Search PostgreSQL documentation

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_utilsExtensionsExtensionsJSON type helper functions, operators, and a hash operator class for PostgreSQL 9.2+. json operator plpgsql pg_json_utils Functions and aggregatesFunctions and aggregates · Extensions json_validator_extExtensionsExtensionsValidate JSONB values against JSON Schema using Rust and pgrx json json-schema pgrx validation json_validator_ext FeaturesFeatures · Extensions 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 Containment and ExistenceManual ChaptersPG188.14.3. jsonb Containment and Existence Testing containment is an important capability of jsonb. There is no parallel set of facilities for the json type. Containment tests whether one jsonb…jsonb Containment and Existence jsonb IndexingManual ChaptersPG188.14.4. jsonb Indexing GIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of jsonb documents (datums). Two GIN “operator classes” are p…jsonb Indexing jsonb SubscriptingManual ChaptersPG188.14.5. jsonb Subscripting The jsonb data type supports array-style subscripting expressions to extract and modify elements. Nested values can be indicated by chaining subscripting expressio…jsonb Subscripting 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_deep_sumExtensionsExtensionsRecursively sum numeric fields in JSONB objects aggregation jsonb jsonb_deep_sum Functions and aggregatesFunctions and aggregates · Extensions jsonb_deleteExtensionsExtensionshstore-style delete function and operator for jsonb jsonb operator jsonb_delete Functions and aggregatesFunctions and aggregates · Extensions jsonb_delete_arrayExtensionsExtensionsDelete jsonb keys or string elements listed in a text array array jsonb operator jsonb_delete_array Functions and aggregatesFunctions and aggregates · Extensions 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_explorerExtensionsExtensionsInspect jsonb trees and paths introspection jsonb jsonb_explorer Functions and aggregatesFunctions and aggregates · Extensions jsonb_extendExtensionsExtensionsMerge two or more jsonb values jsonb merge jsonb-extend Functions and aggregatesFunctions and aggregates · Extensions jsonb_extraExtensionsExtensionsExtra JSONB functions jsonb utility jsonb_extra Functions and aggregatesFunctions and aggregates · Extensions 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 jsonb_extract_path_textFunctionsReferencejsonb_extract_path_text Json Functions And Operators 10 Returns JSON value pointed to by path_elems as text (equivalent to #>> operator). jsonb_extract_path_text ( from_json jsonb, VARIADIC …Functions · Json Functions And Operators jsonb_insertFunctionsReferencejsonb_insert Json Functions And Operators 10 Returns target with new_value inserted. If target section designated by path is in a JSONB array, new_value will be inserted before target or aft…Functions · Json Functions And Operators jsonb_objectFunctionsReferencejsonb_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 jsonb_object_aggFunctionsReferencejsonb_object_agg Aggregate Functions 10 aggregates name/value pairs as a JSON object; values can be null, but not names jsonb_object_agg ( name, value ) → jsonb aggregates name/value pairs a…Functions · Aggregate Functions jsonb_object_agg_strictFunctionsReferencejsonb_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_json…Functions · Aggregate Functions jsonb_object_agg_uniqueFunctionsReferencejsonb_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_json…Functions · Aggregate Functions jsonb_object_agg_unique_strictFunctionsReferencejsonb_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 …Functions · Aggregate Functions jsonb_object_keysFunctionsReferencejsonb_object_keys Json Functions And Operators 10 Returns set of keys in the outermost JSON object. jsonb_object_keys ( jsonb ) → setof text Returns set of keys in the outermost JSON object.…Functions · Json Functions And Operators jsonb_opxExtensionsExtensionshstore-style functions and operators for jsonb jsonb operators jsonb_opx Functions and aggregatesFunctions and aggregates · Extensions jsonb_path_existsFunctionsReferencejsonb_path_exists Json Functions And Operators 12 Checks whether JSON path returns any item for the specified JSON value. jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, sil…Functions · Json Functions And Operators jsonb_path_exists_tzFunctionsReferencejsonb_path_exists_tz Json Functions And Operators 13 These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of da…Functions · Json Functions And Operators jsonb_path_matchFunctionsReferencejsonb_path_match Json Functions And Operators 12 Returns the result of JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the…Functions · Json Functions And Operators jsonb_path_match_tzFunctionsReferencejsonb_path_match_tz Json Functions And Operators 13 These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of dat…Functions · Json Functions And Operators jsonb_path_queryFunctionsReferencejsonb_path_query Json Functions And Operators 12 Gets all JSON items returned by JSON path for the specified JSON value. jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silen…Functions · Json Functions And Operators jsonb_path_query_arrayFunctionsReferencejsonb_path_query_array Json Functions And Operators 12 Gets all JSON items returned by JSON path for the specified JSON value and wraps result into an array. jsonb_path_query_array ( target …Functions · Json Functions And Operators jsonb_path_query_array_tzFunctionsReferencejsonb_path_query_array_tz Json Functions And Operators 13 These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons …Functions · Json Functions And Operators jsonb_path_query_firstFunctionsReferencejsonb_path_query_first Json Functions And Operators 12 Gets the first JSON item returned by JSON path for the specified JSON value. Returns NULL on no results. jsonb_path_query_first ( targe…Functions · Json Functions And Operators