Skip to main content

JSON functions and operators

JSON functions​

jsonb_array_elements​

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

jsonb_array_elements ( jsonb ) → setof jsonb
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.

jsonb_array_elements_text ( jsonb ) → setof varchar
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.

jsonb_array_length ( jsonb ) → integer
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.

jsonb_each ( jsonb ) → setof record ( key varchar, value jsonb )
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.

jsonb_each_text ( jsonb ) → setof record ( key varchar, value varchar )
SELECT * FROM jsonb_each_text('{"a":"foo", "b":"bar"}'::jsonb);
------RESULT
 key | value 
-----+-------
 a   | foo
 b   | bar

jsonb_extract_path​

Extracts JSON sub-object at the specified path.

This function is equivalent to the #> operator in functionality.

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

  • from_json is the input JSON value from which to extract the sub-object.

  • path_elems is the path elements that specify the location of the desired sub-object in the JSON structure. Multiple path elements can be provided as separate arguments.

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

jsonb_extract_path_text​

Extracts JSON sub-object at the specified path as text.

This function is equivalent to the #>> operator in functionality.

jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text
SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"string"}}', 'f4', 'f6');
------RESULT
string

jsonb_object_keys​

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

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

jsonb_strip_nulls​

Removes all object fields that have null values from a given JSONB value, recursively. Empty objects or null values that are not object fields are left untouched.

jsonb_strip_nulls ( jsonb ) → jsonb
-- Handling non-null values
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}}');
------RESULT
{"a": 1, "c": {"e": 2}}

-- Empty object preservation
SELECT jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
------RESULT
{"a": {}, "d": {}}

jsonb_typeof​

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

jsonb_typeof ( jsonb ) → varchar
SELECT jsonb_typeof ('-123.4');
------RESULT
number

jsonb_pretty​

This function takes a jsonb value and returns a text representing the formatted, indented JSON value.

jsonb_pretty ( jsonb JSONB ) → TEXT
SELECT jsonb_pretty('[{"f1":1,"f2":null}, 2]');
------RESULT
[
    {
        "f1": 1,
        "f2": null
    },
    2
]

jsonb_object​

This function takes an array of text elements and returns a jsonb object where adjacent pairs of values are taken as the key and value of an object property.

jsonb_object ( text_array TEXT[] ) → JSONB
jsonb_object('{a, 1, b, def, c, 3.5}' :: text[]) → {"a": "1", "b": "def", "c": "3.5"}
jsonb_object(array['a', null]) → {"a": null}

JSON operators​

jsonb -> integer → jsonb​

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

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

jsonb -> varchar → jsonb​

Extracts JSON object field with the given key.

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

jsonb ->> integer → varchar​

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

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

jsonb ->> varchar → varchar​

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

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

jsonb - text → jsonb​

Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.

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

jsonb - text[] → jsonb​

Deletes all matching keys or array elements from a JSON object.

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

jsonb - integer → jsonb​

Deletes the array element with the specified index (negative integers counting from the end). Throws an error if JSON object is not an array.

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

jsonb #- text[] → jsonb​

Deletes the field or array element at the specified path, where path elements can be either field keys or array indexes.

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

(jsonb || jsonb) -> jsonb​

Concatenates jsonb data.

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]

jsonb @> jsonb -> boolean​

This operator checks if the left jsonb value contains the right jsonb value. For a detailed description and examples about containment and existence, see jsonb Containment and Existence in PostgreSQL's documentation.

'[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb → t

'{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb → t

'{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb → f

'{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb → t

jsonb <@ jsonb -> boolean​

This operator checks if the left jsonb value is contained within the right jsonb value. For a detailed description and examples about containment and existence, see jsonb Containment and Existence in PostgreSQL's documentation.

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

jsonb ? text -> boolean​

This operator checks if a string exists as a top-level array element or object key within a jsonb value.

'["foo", "bar", "baz"]'::jsonb ? 'bar' → t

'{"foo": "bar"}'::jsonb ? 'foo' → t

'{"foo": "bar"}'::jsonb ? 'bar' → f

'{"foo": {"bar": "baz"}}'::jsonb ? 'bar' → f

'"foo"'::jsonb ? 'foo' → t

jsonb ?| text[] -> boolean​

This operator checks if any string in an array exists as a top-level array element or object key within a jsonb value.

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

'["a", "b", "c"]'::jsonb ?| array['b', 'd'] → t

'"b"'::jsonb ?| array['b', 'd'] → t

json ?& text[] -> boolean​

This operator checks if all strings in an array exist as top-level array elements or object keys within a jsonb value.

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

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

'["a", "b", "c"]'::jsonb ?& array['a', 'd'] → f

jsonb #> text[] -> jsonb​

This operator extracts a nested value from a JSONB object using a text array of keys or indices.

'{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,1}'::text[] → "bar"

'{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,null}'::text[] → NULL

jsonb #>> text[] -> text​

This operator extracts a nested value as text from a JSONB object using a text array of keys or indices.

'{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,1}'::text[] → bar

'{"a": {"b": ["foo",null]}}'::jsonb #>> '{a,b,1}'::text[] → NULL

'{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,null}'::text[] → NULL

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.

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.

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

 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

Help us make this doc better!