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-41. Additional jsonb Operators

Operator

Right Operand Type

Description

Example

@>

jsonb

Does the left JSON value contain within it the right
value?

'{"a":1, "b":2}'::jsonb @>
'{"b":2}'::jsonb

<@

jsonb

Is the left JSON value contained within the right
value?

'{"b":2}'::jsonb <@ '{"a":1,
"b":2}'::jsonb

?

text

Does the key/element string exist within the
JSON value?

'{"a":1, "b":2}'::jsonb ?
'b'

?|

text[]

Do any of these key/element strings exist?

'{"a":1, "b":2, "c":3}'::jsonb ?|
array['b', 'c']

?&

text[]

Do all of these key/element strings exist?

'["a", "b"]'::jsonb ?&
array['a', 'b']

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

Table 9-42. JSON Creation Functions

Function

Description

Example

Example Result

to_json(anyelement)

Returns the value as JSON. Arrays and composites are
converted (recursively) to arrays and objects; otherwise,
if there is a cast from the type to json, the cast function will be used to
perform the conversion; otherwise, a JSON scalar value is
produced. For any scalar type other than a number, a
Boolean, or a null value, the text representation will be
used, properly quoted and escaped so that it is a valid
JSON string.

to_json('Fred said
"Hi."'::text)

"Fred said \"Hi.\""

array_to_json(anyarray [,
pretty_bool])

Returns the array as a JSON array. A PostgreSQL
multidimensional array becomes a JSON array of arrays.
Line feeds will be added between dimension-1 elements if
pretty_bool is true.

array_to_json('{{1,5},{99,100}}'::int[])

[[1,5],[99,100]]

row_to_json(record [,
pretty_bool])

Returns the row as a JSON object. Line feeds will be
added between level-1 elements if pretty_bool is true.

row_to_json(row(1,'foo'))

{"f1":1,"f2":"foo"}

json_build_array(VARIADIC
"any")

Builds a possibly-heterogeneously-typed JSON array
out of a variadic argument list.

json_build_array(1,2,'3',4,5)

[1, 2, "3", 4, 5]

json_build_object(VARIADIC
"any")

Builds a JSON object out of a variadic argument list.
By convention, the argument list consists of alternating
keys and values.

json_build_object('foo',1,'bar',2)

{"foo": 1, "bar": 2}

json_object(text[])

Builds a JSON object out of a text array. The array
must have either exactly one dimension with an even
number of members, in which case they are taken as
alternating key/value pairs, or two dimensions such that
each inner array has exactly two elements, which are
taken as a key/value pair.

json_object('{a, 1, b, "def", c,
3.5}')

json_object('{{a, 1},{b,
"def"},{c, 3.5}}')

{"a": "1", "b": "def", "c":
"3.5"}

json_object(keys text[], values
text[])

This form of json_object takes keys and values
pairwise from two separate arrays. In all other respects
it is identical to the one-argument form.

json_object('{a, b}',
'{1,2}')

{"a": "1", "b": "2"}

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.

Table 9-43. JSON Processing Functions

Function

Return Type

Description

Example

Example Result

json_array_length(json)

jsonb_array_length(jsonb)

int

Returns the number of elements in the outermost JSON
array.

json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')

5

json_each(json)

jsonb_each(jsonb)

setof key text, value
json

setof key text, value
jsonb

Expands the outermost JSON object into a set of
key/value pairs.

select * from
json_each('{"a":"foo", "b":"bar"}')

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

json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value text

Expands the outermost JSON object into a set of
key/value pairs. The returned values will be of type
text.

Builds an arbitrary set of records from a JSON array
of objects (see note below). As with all functions
returning record, the caller must
explicitly define the structure of the record with an
AS clause.

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: In json_populate_record, json_populate_recordset, json_to_record and json_to_recordset, type coercion from the
JSON is "best effort" and may not
result in desired values for some types. JSON keys are
matched to identical column names in the target row type.
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.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.