Search PostgreSQL documentation

json_array_elementsFunctionsReferencejson_array_elements Json Functions And Operators 10 Expands a JSON array to a set of JSON values. json_array_elements ( json ) → setof json setof jsonb Expands a JSON array to a set of JSON …Functions · Json Functions And Operators json_array_elements_textFunctionsReferencejson_array_elements_text Json Functions And Operators 10 Expands a JSON array to a set of text values. json_array_elements_text ( json ) → setof text Expands a JSON array to a set of text va…Functions · Json Functions And Operators json_array_lengthFunctionsReferencejson_array_length Json Functions And Operators 10 Returns the number of elements in the outermost JSON array. json_array_length ( json ) → int Returns the number of elements in the outermost…Functions · Json Functions And Operators json_arrayaggFunctionsReferencejson_arrayagg Aggregate Functions 16 Behaves in the same way as json_array but as an aggregate function so it only takes one value_expression parameter. If ABSENT ON NULL is specified, any N…Functions · Aggregate Functions json_build_arrayFunctionsReferencejson_build_array Json Functions And Operators 10 Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. json_build_array ( VARIADIC "any" ) Builds a possibly-het…Functions · Json Functions And Operators json_build_objectFunctionsReferencejson_build_object Json Functions And Operators 10 Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. json_build_o…Functions · Json Functions And Operators json_eachFunctionsReferencejson_each Json Functions And Operators 10 Expands the outermost JSON object into a set of key/value pairs. json_each ( json ) → setof key text, value json setof key text, value jsonb Expands…Functions · Json Functions And Operators json_each_textFunctionsReferencejson_each_text Json Functions And Operators 10 Expands the outermost JSON object into a set of key/value pairs. The returned values will be of type text. json_each_text ( json ) → setof key …Functions · Json Functions And Operators JSON_EXISTSFunctionsReferenceJSON_EXISTS Json Functions And Operators 17 JSON_EXISTS ( context_item, path_expression [PASSING { value AS varname } [, ...]] [{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR]) → boolean 18 19…Functions · Json Functions And Operators json_extract_pathFunctionsReferencejson_extract_path Json Functions And Operators 10 Returns JSON value pointed to by path_elems (equivalent to #> operator). json_extract_path ( from_json json, VARIADIC path_elems text[] ) → …Functions · Json Functions And Operators json_extract_path_textFunctionsReferencejson_extract_path_text Json Functions And Operators 10 Returns JSON value pointed to by path_elems as text (equivalent to #>> operator). json_extract_path_text ( from_json json, VARIADIC pat…Functions · Json Functions And Operators json_objectFunctionsReferencejson_object Json Functions And Operators 10 Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are …Functions · Json Functions And Operators json_object_aggFunctionsReferencejson_object_agg Aggregate Functions 10 aggregates name/value pairs as a JSON object; values can be null, but not names json_object_agg ( name, value ) → json aggregates name/value pairs as a…Functions · Aggregate Functions json_object_agg_strictFunctionsReferencejson_object_agg_strict Aggregate Functions 16 Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb…Functions · Aggregate Functions json_object_agg_uniqueFunctionsReferencejson_object_agg_unique Aggregate Functions 16 Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb…Functions · Aggregate Functions json_object_agg_unique_strictFunctionsReferencejson_object_agg_unique_strict Aggregate Functions 16 Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or t…Functions · Aggregate Functions json_object_keysFunctionsReferencejson_object_keys Json Functions And Operators 10 Returns set of keys in the outermost JSON object. json_object_keys ( json ) → setof text Returns set of keys in the outermost JSON object. 11…Functions · Json Functions And Operators json_objectaggFunctionsReferencejson_objectagg Aggregate Functions 16 Behaves like json_object, but as an aggregate function, so it only takes one key_expression and one value_expression parameter. json_objectagg ( [ { key…Functions · Aggregate Functions json_populate_recordFunctionsReferencejson_populate_record Json Functions And Operators 10 Expands the object in from_json to a row whose columns match the record type defined by base (see note below). json_populate_record ( bas…Functions · Json Functions And Operators json_populate_recordsetFunctionsReferencejson_populate_recordset Json Functions And Operators 10 Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base (see note bel…Functions · Json Functions And Operators JSON_QUERYFunctionsReferenceJSON_QUERY Json Functions And Operators 17 JSON_QUERY ( context_item, path_expression [PASSING { value AS varname } [, ...]] [RETURNING data_type [FORMAT JSON [ENCODING UTF8] ] ] [ { WITHOUT…Functions · Json Functions And Operators json_scalarFunctionsReferencejson_scalar Json Functions And Operators 17 Converts a given SQL scalar value into a JSON scalar value. If the input is NULL, an SQL null is returned. If the input is number or a boolean val…Functions · Json Functions And Operators json_serializeFunctionsReferencejson_serialize Json Functions And Operators 17 Converts an SQL/JSON expression into a character or binary string. The expression can be of any JSON type, any character string type, or bytea …Functions · Json Functions And Operators json_strip_nullsFunctionsReferencejson_strip_nulls Json Functions And Operators 10 Returns from_json with all object fields that have null values omitted. Other null values are untouched. json_strip_nulls ( from_json json ) …Functions · Json Functions And Operators JSON_TABLEFunctionsReferenceJSON_TABLE Json Functions And Operators 17 Each syntax element is described below in more detail. JSON_TABLE ( context_item, path_expression [ AS json_path_name] [ PASSING { value AS varname…Functions · Json Functions And Operators json_to_recordFunctionsReferencejson_to_record Json Functions And Operators 10 Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the s…Functions · Json Functions And Operators json_to_recordsetFunctionsReferencejson_to_recordset Json Functions And Operators 10 Builds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must ex…Functions · Json Functions And Operators json_to_tsvectorFunctionsReferencejson_to_tsvector Text Search Functions And Operators 13 Selects each item in the JSON document that is requested by the filter and converts each one to a tsvector, normalizing words accordin…Functions · Text Search Functions And Operators json_typeofFunctionsReferencejson_typeof Json Functions And Operators 10 Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null. json_typeof ( …Functions · Json Functions And Operators JSON_VALUEFunctionsReferenceJSON_VALUE Json Functions And Operators 17 JSON_VALUE ( context_item, path_expression [PASSING { value AS varname } [, ...]] [RETURNING data_type] [ { ERROR | NULL | DEFAULT expression } ON …Functions · Json Functions And Operators jsonb_aggFunctionsReferencejsonb_agg Aggregate Functions 10 aggregates values, including nulls, as a JSON array jsonb_agg ( expression ) → jsonb aggregates values, including nulls, as a JSON array 11 12 13 Collects al…Functions · Aggregate Functions jsonb_agg_strictFunctionsReferencejsonb_agg_strict Aggregate Functions 16 Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb. jsonb_agg_strict ( anyeleme…Functions · Aggregate Functions jsonb_array_elementsFunctionsReferencejsonb_array_elements Json Functions And Operators 10 Expands a JSON array to a set of JSON values. jsonb_array_elements ( jsonb ) → setof json setof jsonb Expands a JSON array to a set of JS…Functions · Json Functions And Operators jsonb_array_elements_textFunctionsReferencejsonb_array_elements_text Json Functions And Operators 10 Expands a JSON array to a set of text values. jsonb_array_elements_text ( jsonb ) → setof text Expands a JSON array to a set of text…Functions · Json Functions And Operators jsonb_array_lengthFunctionsReferencejsonb_array_length Json Functions And Operators 10 Returns the number of elements in the outermost JSON array. jsonb_array_length ( jsonb ) → int Returns the number of elements in the outerm…Functions · Json Functions And Operators jsonb_build_arrayFunctionsReferencejsonb_build_array Json Functions And Operators 10 Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. jsonb_build_array ( VARIADIC "any" ) Builds a possibly-h…Functions · Json Functions And Operators jsonb_build_objectFunctionsReferencejsonb_build_object Json Functions And Operators 10 Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. jsonb_build…Functions · Json Functions And Operators jsonb_eachFunctionsReferencejsonb_each Json Functions And Operators 10 Expands the outermost JSON object into a set of key/value pairs. jsonb_each ( jsonb ) → setof key text, value json setof key text, value jsonb Expa…Functions · Json Functions And Operators jsonb_each_textFunctionsReferencejsonb_each_text Json Functions And Operators 10 Expands the outermost JSON object into a set of key/value pairs. The returned values will be of type text. jsonb_each_text ( jsonb ) → setof k…Functions · Json Functions And Operators jsonb_extract_pathFunctionsReferencejsonb_extract_path Json Functions And Operators 10 Returns JSON value pointed to by path_elems (equivalent to #> operator). jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] )…Functions · Json Functions And Operators