Search PostgreSQL documentation

json_accessorsExtensionsExtensionsJSON accessor functions function json json_accessors Functions and aggregatesFunctions and aggregates · Extensions json_aggFunctionsReferencejson_agg Aggregate Functions 10 aggregates values, including nulls, as a JSON array json_agg ( expression ) → json aggregates values, including nulls, as a JSON array 11 12 13 Collects all t…Functions · Aggregate Functions json_agg_strictFunctionsReferencejson_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. json_agg_strict ( anyelement…Functions · Aggregate Functions json_arrayFunctionsReferencejson_array Json Functions And Operators 16 Constructs a JSON array from either a series of value_expression parameters or from the results of query_expression, which must be a SELECT query r…Functions · Json Functions And Operators 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_buildExtensionsExtensionsC functions and aggregate helpers for building complex PostgreSQL JSON objects and arrays. array build json object json_build Functions and aggregatesFunctions and aggregates · Extensions 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_enhancements_no_hstoreExtensionsExtensionsBackport of pg 9.3 JSON enhancements to PostgreSQL 9.2 aggregate array enhancements hstore json json path keys path record values json_enhancements FeaturesFeatures · Extensions json_enhancements_with_hstoreExtensionsExtensionsBackport of pg 9.3 JSON enhancements to PostgreSQL 9.2 aggregate array enhancements hstore json json path keys path record values json_enhancements FeaturesFeatures · Extensions 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_fdwExtensionsExtensionsForeign data wrapper for local and remote JSON document access. fdw foreign data wrapper json json_fdw json_fdw Foreign data wrappersForeign data wrappers · Extensions json_modelExtensionsExtensionsJSON Model PL/pgSQL Runtime - JSON value validation data type data validation json json model json_model FeaturesFeatures · Extensions 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_queryExtensionsExtensionsGenerate PostgreSQL WHERE clause predicates from JSONB filter objects. json query-builder sql pg_json_query Functions and aggregatesFunctions and aggregates · Extensions 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_schema_validateExtensionsExtensionsValidate JSON and JSONB data against JSON Schema in PostgreSQL json json-schema validation json_schema_validate FeaturesFeatures · Extensions 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_TABLEManual ChaptersPG189.16.4. JSON_TABLE JSON_TABLE is an SQL/JSON function which queries JSON data and presents the results as a relational view, which can be accessed as a regular SQL table. You can use JSON_TA…JSON_TABLE 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