JSON Functions and Operators (original) (raw)

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]')

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.

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

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

json_extract_path(from_json json, VARIADIC path_elems text[])

jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])

json

jsonb

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

json_extract_path_text(from_json json, VARIADIC path_elems text[])

jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])

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

json_object_keys(json)

jsonb_object_keys(jsonb)

setof text

Returns set of keys in the outermost JSON object.

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

json_object_keys

f1 f2

json_populate_record(base anyelement, from_json json)

jsonb_populate_record(base anyelement, from_json jsonb)

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

json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

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}]')

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

json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

Expands a JSON array to a set of JSON values.

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

value

1 true [2,false]

json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof text

Expands a JSON array to a set of text values.

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

value

foo bar

json_typeof(json)

jsonb_typeof(jsonb)

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('-123.4')

number

json_to_record(json)

jsonb_to_record(jsonb)

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":"bar"}') as x(a int, b text, d text)

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

json_to_recordset(json)

jsonb_to_recordset(jsonb)

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

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