Home > APIs > YSQL > Data types > JSON >

JSON functions and operators

On this page
    Convert a SQL value to a JSON value
    Convert a JSON value to another JSON value
    Convert a JSON value to a SQL value
    Get a property of a JSON value

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	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.