PostgreSQL

x>

int

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

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

{"c":"baz"}

x>

text

Get JSON object field by key

'{"a": {"b":"foo"}}'::jsonx>'a'

{"b":"foo"}

x>>

int

Get JSON array element as text

'[1,2,3]'::jsonx>>2

3

x>>

text

Get JSON object field as text

'{"a":1,"b":2}'::jsonx>>'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

@>

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 topxlevel key within the JSON value?

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

?|

text[]

Do any of these array strings exist as topxlevel keys?

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

?&

text[]

Do all of these array strings exist as topxlevel keys?

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

||

jsonb

Concatenate two jsonb values into a new jsonb value

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

x

text

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

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

x

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 x '{a,c}'::text[]

x

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 x 1

#x

text[]

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

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

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 dimensionx1 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 levelx1 elements if `pretty_bool` is true.

row_to_json(row(1,'foo'))

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

Builds a possiblyxheterogeneouslyxtyped JSON array out of a variadic argument list.

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

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

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}

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.

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

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

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

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

int

Returns the number of elements in the outermost JSON array.

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

5

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

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

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.

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

Returns JSON value pointed to by `path_elems (equivalent to `#> operator).

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')

{"f5":99,"f6":"foo"}

text

Returns JSON value pointed to by `path_elems as `text (equivalent to #>> operator).

json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')

foo

setof text

Returns set of keys in the outermost JSON object.

json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')

anyelement

Expands the object in `from_json to a row whose columns match the record type defined by base` (see note below).

select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')

setof anyelement

Expands the outermost array of objects in `from_json to a set of rows whose columns match the record type defined by base` (see note below).

select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')

Expands a JSON array to a set of JSON values.

select * from json_array_elements('[1,true, [2,false]]')

setof text

Expands a JSON array to a set of text values.

select * from json_array_elements_text('["foo", "bar"]')

text

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

json_typeof('x123.4')

number

record

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

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)

setof record

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.

select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);

Returns `from_json` with all object fields that have null values omitted. Other null values are untouched.

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

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

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

jsonb

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 oriented operators, negative integers that appear in path` count from the end of JSON arrays.

jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])

jsonb

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 oriented operators, negative integers that appear in path` count from the end of JSON arrays.

jsonb_pretty(from_json jsonb)

text

Returns `from_json` as indented JSON text.

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