JSON functions and operators

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_array_length

Returns the number of elements in the top-level JSON array.

Syntax

jsonb_array_length ( jsonb ) → integer

Example

SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
------RESULT
5

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 * FROM jsonb_each_text('{"a":"foo", "b":"bar"}'::jsonb);
------RESULT
 key | value 
-----+-------
 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

jsonb_typeof

Returns the type of the top-level JSON value as a text string.

Syntax

jsonb_typeof ( jsonb ) → varchar

Example

SELECT jsonb_typeof ('-123.4');
------RESULT
number

JSON operators

jsonb -> integer

Extracts the n'th element of a JSON array (array elements are indexed from zero, but negative integers count from the end).

Syntax

jsonb -> integer → jsonb

Example

SELECT'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2;
------RESULT
 {"c":"baz"}

jsonb -> varchar

Extracts JSON object field with the given key.

Syntax

jsonb -> varchar → jsonb

Example

SELECT '{"a": {"b":"foo"}}'::jsonb -> 'a';
------RESULT
 {"b": "foo"}

jsonb ->> integer

Extracts the n'th element of a JSON array, as text.

Syntax

jsonb ->> integer → varchar

Example

SELECT '[1,2,3]'::jsonb ->> 2;
------RESULT
3

jsonb ->> varchar

Extracts JSON object field with the given key, as text.

Syntax

jsonb ->> varchar → varchar

Example

SELECT '{"a":1,"b":2}'::jsonb ->> 'b';
------RESULT
2

(jsonb || jsonb) -> jsonb

Concatenates jsonb data type.

Syntax

(jsonb || jsonb) → jsonb

Example

SELECT '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;  
SELECT '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb;
SELECT '[1, 2]'::jsonb || '3'::jsonb;
SELECT '{"a": "b"}'::jsonb || '42'::jsonb;
------RESULT
["a", "b", "a", "d"]
{"a": "b", "c": "d"}
[1, 2, 3]
[{"a": "b"}, 42]

IS JSON predicate

This predicate tests whether an expression can be parsed as JSON, optionally of a specified type. It evaluates the JSON structure and returns a boolean result indicating whether the value matches the specified JSON type.

Syntax

expression IS [ NOT ] JSON [ VALUE | ARRAY | OBJECT | SCALAR ] → bool

If SCALAR, ARRAY, or OBJECT is specified, the test is whether or not the JSON is of that particular type.

Example

SELECT js,
  js IS JSON "json?",
  js IS JSON SCALAR "scalar?",
  js IS JSON OBJECT "object?",
  js IS JSON ARRAY "array?"
FROM (VALUES
      ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js);

------RESULT

 js         | json? | scalar? | object? | array?
------------+-------+---------+---------+---------
 123        | t     | t       | f       | f
 "abc"      | t     | t       | f       | f
 {"a": "b"} | t     | f       | t       | f
 [1,2]      | t     | f       | f       | t
 abc        | f     | f       | f       | f