JSON functions

jsonb_array_elements

Expands the top-level JSON array into a set of JSON values.

Syntax

jsonb_array_elements ( jsonb ) → setof jsonb

Example

SELECT * FROM jsonb_array_elements('[1,true, [2,false]]'::jsonb);
------RESULT
1
true
[2, false]

jsonb_array_elements_text

Expands the top-level JSON array into a set of text (varchar) values.

Syntax

jsonb_array_elements_text ( jsonb ) → setof varchar

Example

SELECT * FROM jsonb_array_elements_text('["foo", "bar"]'::jsonb)
------RESULT
foo
bar

jsonb_each

Expands the top-level JSON object into a set of key/value pairs.

Syntax

jsonb_each ( jsonb ) → setof record ( key varchar, value jsonb )

Example

SELECT * FROM jsonb_each('{"a":"foo", "b":"bar"}'::jsonb);
------RESULT
a "foo"
b "bar"

jsonb_each_text

Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type varchar.

Syntax

jsonb_each_text ( jsonb ) → setof record ( key varchar, value varchar )

Example

SELECT jsonb_each_text('{"a":"foo", "b":"bar"}'::jsonb);
------RESULT
(a,foo)
(b,bar)

jsonb_object_keys

Returns the set of keys in the top-level JSON object.

Syntax

jsonb_object_keys ( jsonb ) → setof varchar

Example

SELECT * FROM jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}'::jsonb);
------RESULT
f1
f2