JSON data types operators

Learn about the operators available for use with the json and jsonb data types.

There are parallel variants of the operators for both the json and jsonb types. The field/element/path extraction operators return the same type as their left input (either json or jsonb), except for those operators specified as returning text, which coerce the value to text. The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example, if no such element exists. The field/element/path extraction operators that accept integer JSON array subscripts all support negative subscripting from the end of arrays.

Note: The #> and #>> operators use JSONB arrays as their right operand.

Operators available for json and jsonb

->

Right operand type: int

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

Examples:

SELECT '["football", "volleyball", "soccer"]'::JSONB -> 0;
  ?COLUMN?  
------------
 "football"
(1 row)

SELECT '["football", "volleyball", "soccer"]'::JSONB -> -1;
 ?COLUMN? 
----------
 "soccer"
(1 row)

->

Right operand type: text

Get JSON object filed by key.

Examples:

SELECT '{"name": "Joe Smith", "age": 28}'::JSONB -> 'name';
  ?COLUMN?   
-------------
 "Joe Smith"
(1 row)

SELECT '{"name": "Joe Smith", "age": 28}'::JSONB -> 'age';
 ?COLUMN? 
----------
 28
(1 row)

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB -> 'sports' -> 0;
  ?COLUMN?  
------------
 "football"
(1 row)

->>

Right operand type: int

Get JSON array element as nvarchar.

Examples:

SELECT '["football", "volleyball", "soccer"]'::JSONB ->> 0;
 ?COLUMN? 
----------
 football
(1 row)

SELECT '["football", "volleyball", "soccer"]'::JSONB ->> -1;
 ?COLUMN? 
----------
 soccer
(1 row)

->>

Right operand type: text

Get JSON object field as nvarchar.

Examples:

SELECT '{"name": "Joe Smith", "age": 28}'::JSONB ->> 'name';
 ?COLUMN?  
-----------
 Joe Smith
(1 row)

SELECT '{"name": "Joe Smith", "age": 28}'::JSONB ->> 'age';
 ?COLUMN? 
----------
 28
(1 row)

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB -> 'sports' ->> 0;
 ?COLUMN? 
----------
 football
(1 row)

#>

Right operand type: JSONB arrays

Function: json_extract_path, jsonb_extract_path

Get JSON object at specified path.

Examples:

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB #> '["sports", 0]';
  ?COLUMN?  
------------
 "football"
(1 row)

#>>

Right operand type: JSONB arrays

Function name: json_extract_path_text, jsonb_extract_path_text

Get JSON object at specified path as nvarchar.

Examples:

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB #>> '["sports", 0]';
 ?COLUMN? 
----------
 football
(1 row)

Some further operators also exist only for jsonb, as described in the next section of this document.

Operators available for jsonb

Note: Some NPS drivers do not support operators with a question mark in them. Instead, you have to use the function. Also, the ?| and ?& operators use JSONB arrays as their right operand type.

?

Right operand type: text

Function name: jsonb_exists

Does the string exist as a top-level key within the JSON value?

Examples:

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ? 'age';
 ?COLUMN? 
----------
 t
(1 row)

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ? 'address';
 ?COLUMN? 
----------
 f
(1 row)

SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]},"一":"二"}'::jsonb ? 'e';
 ?COLUMN? 
----------
 f
(1 row)

SELECT jsonb_exists('{"一":"null", "二":"qq"}', '三');
 JSONB_EXISTS 
--------------
 f
(1 row)

Tip: Question marks (?) are used as parameter place holders in ODBC and OLEDB drivers. You must use jsonb_exists() to check for the existence of a key:

Examples:

SELECT jsonb_exists('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', 'age');
 JSONB_EXISTS 
--------------
 t
(1 row)

SELECT jsonb_exists('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', 'address');
 JSONB_EXISTS 
--------------
 f
(1 row)

?|

Right operand type: JSONB arrays

Function: jsonb_exists_any

Containment and existence

Containment

Testing containment is an important capability of the jsonb data type. The json type doesn't have a parallel set of such facilities. Containment tests if a jsonb document has another document contained within it.

The contained object must match the containing object as to structure and data contents, possibly after discarding some non-matching array elements or object key/value pairs from the containing object. The order of array elements is not significant when carrying out a containment match, and duplicate array elements are effectively considered only once.

Examples:

Simple scalar/primitive values contain only the identical value:

SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
?COLUMN? 
----------
 t
(1 row)

The array on the right side is contained within the one on the left:

SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
?COLUMN? 
----------
 t
(1 row)

Order of array elements is not significant, so this is also true:

SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
?COLUMN? 
----------
 t
(1 row)

Duplicate array elements don't matter either:

SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
?COLUMN? 
----------
 t
(1 row)

The object with a single pair on the right side is contained within the object on the left side:

SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
?COLUMN? 
----------
 t
(1 row)

The array on the right side is not considered contained within the array on the left, even though a similar array is nested within it:

SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
?COLUMN? 
----------
 f
(1 row)

But with a layer of nesting, it is contained:

SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
?COLUMN? 
----------
 t
(1 row)

Similarly, containment is not reported here:

SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;
?COLUMN? 
----------
 f
(1 row)

A top-level key and an empty object is contained:

SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
?COLUMN? 
----------
 t
(1 row)

An array may contain a primitive value, which is an exception to the principle that the structures must match.

Example:

SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
?COLUMN? 
----------
 t
(1 row)

This exception is not reciprocal; non-containment is reported here:

SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;
?COLUMN? 
----------
 f
(1 row)

Existence

jsonb also has an existence operator. It tests if a string (given as a text value) appears as an object key or an array element at the top level of the jsonb value. JSON objects are better suited than arrays for testing containment or existence when there are many keys or elements involved. Unlike arrays, they are internally optimized for searching, and do not need to be searched linearly.

Examples:

String exists as array element:

SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
?COLUMN? 
----------
 t
(1 row)

String exists as object key:

SELECT '{"foo": "bar"}'::jsonb ? 'foo';
?COLUMN? 
----------
 t
(1 row)

Object values are not considered:

SELECT '{"foo": "bar"}'::jsonb ? 'bar';
?COLUMN? 
----------
 f
(1 row)

As with containment, existence must match at the top level:

SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar';
?COLUMN? 
----------
 f
(1 row)

A string is considered to exist if it matches a primitive JSON string:
```
SELECT '"foo"'::jsonb ? 'foo';
?COLUMN? 
----------
 t
(1 row)
```