9.15. JSON Functions and Operators

Table 9-40 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 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-41. 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.

Table 9-42 shows the functions that are available for creating json values. (Currently, there are no equivalent functions for jsonb, but you can cast the result of one of these functions to jsonb.)

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-43 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
---+---
 1 | 2

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

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

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

 a |    b    | d
---+---------+---
 1 | [1,2,3] |

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

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: While the examples for the functions json_populate_record, json_populate_recordset, json_to_record and json_to_recordset 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.

JSON keys are matched to identical column names in the target row type. JSON type coercion for these functions is "best effort" and may not result in desired values for some types. JSON fields that do not appear in the target row type will be omitted from the output, and target columns that do not match any JSON field will simply be NULL.

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.

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.