JSON functions and operators

JSON functions and operators

Notes: For an alphabetical listing of the JSON functions and operators, see the listing in the navigation bar.

There are two trivial typecast operators for converting between a text value that conforms to RFC 7159 and a jsonb or json value, the ordinarily overloaded = operator, 12 dedicated JSON operators, and 23 dedicated JSON functions.

Most of the operators are overloaded so that they can be used on both json and jsonb values. When such an operator reads a subvalue as a genuine JSON value, then the result has the same data type as the input. When such an operator reads a subvalue as a SQL text value that represents the JSON value, then the result is the same for a json input as for a jsonb input.

Some of the functions have just a jsonb variant and a couple have just a json variant. Function names reflect this by starting with jsonb_ or ending with _jsonb—and, correspondingly, for the json variants. The reason that this naming convention is used, rather than ordinary overloading, is that YSQL can distinguish between same-named functions when the specification of their formal parameters differ but not when their return types differ. Some of the JSON functions for a specific purpose differ only by returning a json value or a jsonb value. This is why a single consistent naming convention, a b variant and a plain variant, is used throughout.

When an operator or function has both a JSON value input and a JSON value output, the jsonb variant takes a jsonb input and produces a jsonb output; and, correspondingly, the json variant takes a json input and produces a json output. You can use the ysqlsh \df metacommand to show the signature (that is, the data types of the formal parameters and the return value) of any of the JSON functions; but you cannot do this for the operators.

Check the full account of each to find its variant status. When an operator or function has both a jsonb and json variant, then only the jsonb variant is described. The functionality of the json variant can be trivially understood from the account of the jsonb functionality.

To avoid clutter in the tables, only the jsonb variants of the function names are mentioned except where only a json variant exists.

Convert a SQL value to a JSON value

Function or operator jsonb json Description
::jsonb yes yes ::jsonb typecasts a SQL text value that conforms to RFC 7159 to a jsonb value. Use the appropriate one of ::jsonb, ::json, or ::text to typecast between any pair out of text, json, and jsonb, in the direction that you need.
to_jsonb() yes yes Convert a single SQL value of any primitive or compound data type, that allows a JSON representation, to a sematically equivaent jsonb, or json, value.
row_to_json() yes Create a JSON object from a SQL record. It has no practical advantage over to_jsonb().
array_to_json() yes Create a JSON array from a SQL array value. It has no practical advantage over to_jsonb().
jsonb_build_array() yes yes Create a JSON array from a variadic list of values of arbitrary SQL data type.
jsonb_build_object() yes yes Create a JSON object from a variadic list that specifies keys with values of arbitrary SQL data type.
jsonb_object() yes yes Create a JSON object from SQL array values that specifiy keys with their values of SQL data type text.
jsonb_agg() yes yes This is an aggregate function. (Aggregate functions compute a single result from a SETOF input SQL values.) It creates a JSON array whose values are the JSON representations of the aggregated SQL values.
jsonb_object_agg() yes yes This is an aggregate function. (Aggregate functions compute a single result from a SETOF input values.) It creates a JSON object whose values are the JSON representations of the aggregated SQL values. It is most useful when these to-be-aggregated values are "row" type values with two fields. The first represesnts the key and the second represents the value of the intended JSON object's key-value pair.

Convert a JSON value to another JSON value

Function or operator jsonb json Description
-> yes yes Read the value specified by a one-step path returning it as a json or jsonb value.
#> yes yes Read the value specified by a multi-step path returning it as a json or jsonb value.
|| yes Concatenate two jsonb values. The rule for deriving the output value depends upon the JSON data types of the operands.
- yes Remove key-value pair(s) from an object or a single value from an array.
#- yes Remove a single key-value pair from an object or a single value from an array at the specified path.
jsonb_extract_path() yes yes Provide the identical functionality to the #> operator. The path is presented as a variadic list of steps that must all be text values. Its invocation more verbose than that of the #> operator and there is no reason to prefer the function form to the operator form.
jsonb_strip_nulls() yes yes Find all key-value pairs at any depth in the hierarchy of the supplied JSON compound value (such a pair can occur only as an element of an object) and return a JSON value where each pair whose value is null has been removed.
jsonb_set() and jsonb_insert() yes Use jsonb_set() to change a JSON value, i.e. the value of an existing key-value pair in a JSON object or the value at an existing index in a JSON array. Use jsonb_insert() to insert a value, either as the value for a key that doesn't yet exist in a JSON object or beyond the end or before the start of the index range for a JSON array.

Convert a JSON value to a SQL value

Function or operator jsonb json Description
::text yes yes Typecast a jsonb value to a SQL text value that conforms to RFC 7159. Whitesace is conventioanally defined for a jsonb operand. Whitespace, in general, is unpredicatable for a json operand.
->> yes yes Like -> except that the targeted value is returned as a SQL text value: either the ::text typecast of a compound JSON value; or a typecastable text value holding the actual value that a primitive JSON value represents.
#>> yes yes Like ->> except that the to-be-read JSON subvalue is specified by the path to it from the enclosing JSON value.
jsonb_extract_path_text() yes yes Provide the identical functionality to the #>> operator. There is no reason to prefer the function form to the operator form.
jsonb_populate_record() yes yes Convert a JSON object into the equivalent SQL record.
jsonb_populate_recordset() yes yes Convert a homogeneous JSON array of JSON objects into the equivalent set of SQL records.
jsonb_to_record() yes yes Convert a JSON object into the equivalent SQL record. Syntax variant of the functionality that jsonb_populate_record() provides. It has some restrictions and brings no practical advantage over its less restricted equivalent.
jsonb_to_recordset() yes yes Bears the same relationship to jsonb_to_record() as jsonb_populate_recordset() bears to jsonb_populate_record(). Therefore, it brings no practical advantage over its restricted equivalent.
jsonb_array_elements() yes yes Transform the JSON values of JSON array into a SQL table of (i.e. SETOF) jsonb values.
jsonb_array_elements_text() yes yes Transform the JSON values of JSON array into a SQL table of (i.e. SETOF) text values.
jsonb_each() yes yes Create a row set with columns "key" (as a SQL text) and "value" (as a SQL jsonb) from a JSON object.
jsonb_each_text() yes yes Create a row set with columns "key" (as a SQL text) and "value" (as a SQL text) from a JSON object.
jsonb_pretty() yes Format the text representation of the JSON value that the input jsonb actual argument represents, using whitespace, to make it maximally human readable.

Get a property of a JSON value

Function or operator jsonb json Description
= yes Test if two jsonb values are equal.
@> and <@ yes The @> operator tests if the left-hand JSON value contains the right-hand JSON value. The <@ operator tests if the right-hand JSON value contains the left-hand JSON value.
?, ?|, and ?& yes (1) If the left-hand JSON value is an object, test if the right-hand SQL text value(s) exist as key name(s) in the object. (2) If the left-hand JSON value is an array, test if the right-hand SQL text value(s) exist as JSON string value(s) in the array.
jsonb_array_length() yes Return the count of values (primitive or compound) in the array. You can use this to iterate over the elements of a JSON array using the -> operator.
jsonb_typeof() yes Return the data type of the JSON value as a SQL text value.
jsonb_object_keys() yes Transform the list of key names int the supplied JSON object into a set (i.e. table) of text values.