Get JSON array element (indexed from zero, negative integers
count from the end)

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2

{"c":"baz"}

->

text

Get JSON object field by key

'{"a":
{"b":"foo"}}'::json->'a'

{"b":"foo"}

->>

int

Get JSON array element as text

'[1,2,3]'::json->>2

3

->>

text

Get JSON object field as text

'{"a":1,"b":2}'::json->>'b'

2

#>

text[]

Get JSON object at specified path

'{"a": {"b":{"c":
"foo"}}}'::json#>'{a,b}'

{"c": "foo"}

#>>

text[]

Get JSON object at specified path as text

'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

3

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.

Table 9.44. Additional
jsonb Operators

Operator

Right Operand Type

Description

Example

@>

jsonb

Does the left JSON value contain the right JSON path/value
entries at the top level?

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

<@

jsonb

Are the left JSON path/value entries contained at the top level
within the right JSON value?

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

?

text

Does the string exist as
a top-level key within the JSON value?

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

?|

text[]

Do any of these array strings exist as top-level keys?

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

?&

text[]

Do all of these array strings exist as top-level keys?

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

||

jsonb

Concatenate two jsonb values into a
new jsonb value

'["a", "b"]'::jsonb || '["c",
"d"]'::jsonb

-

text

Delete key/value pair or string element from left operand.
Key/value pairs are matched based on their key value.

'{"a": "b"}'::jsonb - 'a'

-

text[]

Delete multiple key/value pairs or string elements from left operand.
Key/value pairs are matched based on their key value.

'{"a": "b", "c": "d"}'::jsonb -
'{a,c}'::text[]

-

integer

Delete the array element with specified index (Negative
integers count from the end). Throws an error if top level
container is not an array.

'["a", "b"]'::jsonb - 1

#-

text[]

Delete the field or element with specified path (for JSON
arrays, negative integers count from the end)

'["a", {"b":1}]'::jsonb #-
'{1,b}'

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

Table 9.45. JSON Creation
Functions

Function

Description

Example

Example Result

to_json(anyelement)

to_jsonb(anyelement)

Returns the value as json or
jsonb. 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
scalar value is produced. For any scalar type other than a number,
a Boolean, or a null value, the text representation will be used,
in such a fashion that it is a valid json
or jsonb value.

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

jsonb_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")

jsonb_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[])

jsonb_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[])

jsonb_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.46
shows the functions that are available for processing json and jsonb values.

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

Returns target with
the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is
true) and the item designated by
path does not exist. As
with the path orientated operators, negative integers that appear
in path count from the
end of JSON arrays.

Returns target with
new_value inserted. If
target section designated
by path is in a JSONB
array, new_value will be
inserted before target or after if insert_after is true (default is
false). If target section designated by
path is in JSONB object,
new_value will be
inserted only if target
does not exist. As with the path orientated operators, negative
integers that appear in path count from the end of JSON
arrays.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}',
'"new_value"')

jsonb_insert('{"a": [0,1,2]}', '{a, 1}',
'"new_value"', true)

{"a": [0, "new_value", 1, 2]}

{"a": [0, 1, "new_value", 2]}

jsonb_pretty(from_json jsonb)

text

Returns from_json as
indented JSON text.

jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')

[
{
"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

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

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.

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.