JSON functions and operators
JSON functions
jsonb_array_elements
Expands the top-level JSON array into a set of JSON values.
jsonb_array_elements_text
Expands the top-level JSON array into a set of text (varchar) values.
jsonb_array_length
Returns the number of elements in the top-level JSON array.
jsonb_build_array
Builds a JSON array out of a variadic argument list. Each argument is converted as per to_jsonb
.
jsonb_build_object
Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per to_jsonb
.
jsonb_each
Expands the top-level JSON object into a set of key-value pairs.
jsonb_each_text
Expands the top-level JSON object into a set of key-value pairs. The returned values will be of type varchar.
jsonb_extract_path
Extracts JSON sub-object at the specified path.
This function is equivalent to the #> operator in functionality.
from_json
is the input JSON value from which to extract the sub-object.path_elems
is the path elements that specify the location of the desired sub-object in the JSON structure. Multiple path elements can be provided as separate arguments.
jsonb_extract_path_text
Extracts JSON sub-object at the specified path as text.
This function is equivalent to the #>> operator in functionality.
jsonb_object_keys
Returns the set of keys in the top-level JSON object.
jsonb_strip_nulls
Removes all object fields that have null values from a given JSONB value, recursively. Empty objects or null values that are not object fields are left untouched.
jsonb_path_exists
Checks if a JSON path returns any items from a JSON value.
If the vars
argument is provided, it must be a JSON object. Its fields act as named values that are substituted into the path
expression. When the silent
argument is specified and set to true
, the function will suppress errors like the @? and @@ operators.
For information on the SQL/JSON Path syntax, refer to the PostgreSQL documentation.
jsonb_path_match
Evaluates a JSON path predicate on a JSON value and returns the results as a boolean. Only the first item of the result is taken into account.
jsonb_path_query
Extracts items from a JSON value matching a JSON path and returns as a set.
jsonb_path_query_array
Extracts matching JSON path items and returns them wrapped in an array.
jsonb_path_query_first
Extracts the first matching item from a JSON value using a JSON path.
jsonb_populate_record
Expands the top-level JSON object to a row having the struct type of the base argument.
It scans the JSON object for fields matching the output row’s column names, inserting their values into the corresponding output columns. Any fields not matching column names are ignored. Typically the base is NULL
, meaning that any output columns that do not match any object field will be NULL
. Otherwise, values in the base are used for unmatched columns.
The conversion of JSON values to SQL types of the output column applies these rules in sequence:
- A JSON null value is converted to an SQL null in all cases.
- If the output column is of type json or jsonb, the JSON value is just reproduced exactly.
- If the output column is a struct (row) type, and the JSON value is a JSON object, the fields of the object are converted to columns of the output row type by recursive application of these rules.
- Likewise, if the output column is an array type and the JSON value is a JSON array, the elements of the JSON array are converted to elements of the output array by recursive application of these rules.
- Otherwise, if the JSON value is a string, the contents of the string are fed to the input conversion function for the column’s data type.
- Otherwise, the ordinary text representation of the JSON value is fed to the input conversion function for the column’s data type.
NOTE
The jsonb_populate_record
function in RisingWave differs from the function in PostgreSQL. In PostgreSQL, users are required to define a composite type using the CREATE TYPE
statement before using these functions. However, in RisingWave, you should use the inline struct type instead.
jsonb_populate_recordset
Expands the top-level JSON array of objects to a set of rows having the struct type of the base argument. Each element of the JSON array is processed as described above for jsonb_populate_record.
NOTE
The jsonb_populate_recordset
function in RisingWave differs from the function in PostgreSQL. In PostgreSQL, users are required to define a composite type using the CREATE TYPE
statement before using these functions. However, in RisingWave, you should use the inline struct type instead.
jsonb_populate_map
Converts jsonb
data into a map
type by merging key-value pairs from the jsonb
into the specified map
.
jsonb_set
Modifies JSONB data by replacing or inserting new values at a specified path. If the path exists, the function will replace the value with the new one. If the path does not exist and the create_if_missing
parameter is set to true
(which is the default), the function will add the new value.
jsonb_typeof
Returns the type of the top-level JSON value as a text string.
jsonb_pretty
This function takes a jsonb
value and returns a text representing the formatted, indented JSON value.
jsonb_object
This function takes an array of text elements and returns a jsonb
object where adjacent pairs of values are taken as the key and value of an object property.
to_jsonb
Converts any SQL value to JSONB data type. It recursively handles arrays and composites, transforming them into arrays and objects in the resulting JSON representation. If a direct cast from the SQL data type to JSON is available, it is used for the conversion; otherwise, scalar values are produced as JSON scalars, with text representations appropriately escaped to ensure valid JSON string values.
JSON operators
jsonb -> integer → jsonb
Extracts the n’th element of a JSON array (array elements are indexed from zero, but negative integers count from the end).
jsonb -> varchar → jsonb
Extracts JSON object field with the given key.
jsonb ->> integer → varchar
Extracts the n’th element of a JSON array, as text.
jsonb ->> varchar → varchar
Extracts JSON object field with the given key, as text.
jsonb - text → jsonb
Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.
jsonb - text[] → jsonb
Deletes all matching keys or array elements from a JSON object.
jsonb - integer → jsonb
Deletes the array element with the specified index (negative integers counting from the end). Throws an error if JSON object is not an array.
jsonb #- text[] → jsonb
Deletes the field or array element at the specified path, where path elements can be either field keys or array indexes.
(jsonb || jsonb) → jsonb
Concatenates jsonb data.
jsonb @> jsonb → boolean
This operator checks if the left jsonb
value contains the right jsonb
value. For a detailed description and examples about containment and existence, see jsonb Containment and Existence in PostgreSQL’s documentation.
jsonb <@ jsonb → boolean
This operator checks if the left jsonb
value is contained within the right jsonb
value. For a detailed description and examples about containment and existence, see jsonb Containment and Existence in PostgreSQL’s documentation.
jsonb ? text → boolean
This operator checks if a string exists as a top-level array element or object key within a jsonb
value.
jsonb ?| text[] → boolean
This operator checks if any string in an array exists as a top-level array element or object key within a jsonb
value.
json ?& text[] → boolean
This operator checks if all strings in an array exist as top-level array elements or object keys within a jsonb
value.
jsonb #> text[] → jsonb
This operator extracts a nested value from a JSONB object using a text array of keys or indices.
jsonb #>> text[] → text
This operator extracts a nested value as text from a JSONB object using a text array of keys or indices.
jsonb @? varchar → boolean
Determine whether the specified JSON path returns any item for the given JSON value.
jsonb @@ varchar → boolean
Returns the result of a JSON path predicate check on the specified JSON value, considering only the first item of the result. If the result is not a Boolean, it returns NULL.
IS JSON
predicate
This predicate tests whether an expression can be parsed as JSON, optionally of a specified type. It evaluates the JSON structure and returns a boolean result indicating whether the value matches the specified JSON type.
If SCALAR, ARRAY, or OBJECT is specified, the test is whether or not the JSON is of that particular type.
Was this page helpful?