9.15. JSON Functions and Operators

Table 9.43 shows the operators that are available for use with the two JSON data types (see Section 8.14).

Note

There are parallel variants of these operators for both the json and jsonb types. The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text. The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such element exists. The field/element/path extraction operators that accept integer JSON array subscripts all support negative subscripting from the end of arrays.

The standard comparison operators shown in Table 9.1 are available for jsonb, but not for json. They follow the ordering rules for B-tree operations outlined at Section 8.14.4.

Some further operators also exist only for jsonb, as shown in Table 9.44. Many of these operators can be indexed by jsonb operator classes. For a full description of jsonb containment and existence semantics, see Section 8.14.3. Section 8.14.4 describes how these operators can be used to effectively index jsonb data.

Note

The || operator concatenates the elements at the top level of each of its operands. It does not operate recursively. For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.

Table 9.45 shows the functions that are available for creating json and jsonb values. (There are no equivalent functions for jsonb, of the row_to_json and array_to_json functions. However, the to_jsonb function supplies much the same functionality as these functions would.)

Note

array_to_json and row_to_json have the same behavior as to_json except for offering a pretty-printing option. The behavior described for to_json likewise applies to each individual value converted by the other JSON creation functions.

Note

The hstore extension has a cast from hstore to json, so that hstore values converted via the JSON creation functions will be represented as JSON objects, not as primitive string values.

Table 9.46 shows the functions that are available for processing json and jsonb values.

 key | value
-----+-------
 a   | "foo"
 b   | "bar"

 key | value
-----+-------
 a   | foo
 b   | bar

 json_object_keys
------------------
 f1
 f2

 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

 a | b
---+---
 1 | 2
 3 | 4

   value
-----------
 1
 true
 [2,false]

   value
-----------
 foo
 bar

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

 a |  b
---+-----
 1 | foo
 2 |

[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

Note

Many of these functions and operators will convert Unicode escapes in JSON strings to the appropriate single character. This is a non-issue if the input is type jsonb, because the conversion was already done; but for json input, this may result in throwing an error, as noted in Section 8.14.

Note

The functions json[b]_populate_record, json[b]_populate_recordset, json[b]_to_record and json[b]_to_recordset operate on a JSON object, or array of objects, and extract the values associated with keys whose names match column names of the output row type. Object fields that do not correspond to any output column name are ignored, and output columns that do not match any object field will be filled with nulls. To convert a JSON value to the SQL type of an output column, the following rules are applied in sequence:

• A JSON null value is converted to a 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 composite (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 literal, 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.

While the examples for these functions use constants, the typical use would be to reference a table in the FROM clause and use one of its json or jsonb columns as an argument to the function. Extracted key values can then be referenced in other parts of the query, like WHERE clauses and target lists. Extracting multiple values in this way can improve performance over extracting them separately with per-key operators.

Note

All the items of the path parameter of jsonb_set as well as jsonb_insert except the last item must be present in the target. If create_missing is false, all items of the path parameter of jsonb_set must be present. If these conditions are not met the target is returned unchanged.

If the last path item is an object key, it will be created if it is absent and given the new value. If the last path item is an array index, if it is positive the item to set is found by counting from the left, and if negative by counting from the right - -1 designates the rightmost element, and so on. If the item is out of the range -array_length .. array_length -1, and create_missing is true, the new value is added at the beginning of the array if the item is negative, and at the end of the array if it is positive.

Note

The json_typeof function's null return value should not be confused with a SQL NULL. While calling json_typeof('null'::json) will return null, calling json_typeof(NULL::json) will return a SQL NULL.

Note

If the argument to json_strip_nulls contains duplicate field names in any object, the result could be semantically somewhat different, depending on the order in which they occur. This is not an issue for jsonb_strip_nulls since jsonb values never have duplicate object field names.

See also Section 9.20 for the aggregate function json_agg which aggregates record values as JSON, and the aggregate function json_object_agg which aggregates pairs of values into a JSON object, and their jsonb equivalents, jsonb_agg and jsonb_object_agg.