PolarDB:JSON types

Containment

The @> operator tests whether one jsonb document is contained within another. The contained object must match the containing object in structure and data, though non-matching array elements or key/value pairs in the containing object are ignored. Array element order does not matter, and duplicate array elements are considered only once.

-- Simple scalar values contain only the identical value
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;  -- true

-- The right-side array is contained within the left-side array
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;  -- true

-- Array element order is not significant
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;  -- true

-- Duplicate array elements don't matter
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;  -- true

-- A single-pair object on the right is contained within the object on the left
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;  -- true

-- A nested array is NOT considered contained at the outer level
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- false

-- But matching the nesting level works
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;  -- true

-- Containment does not cross object nesting boundaries
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- false

-- A top-level key with an empty object is contained
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;  -- true

As a special exception, an array may contain a primitive value (but not the reverse):

-- An array contains a primitive string value
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;  -- true

-- Non-containment: a primitive does not contain an array
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- false

Because containment is nested, queries can skip explicit sub-object selection. For example, to find documents where a tags array contains sub-objects with both "term":"paris" and "term":"food":

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

Existence

The ? operator tests whether a string appears as an object key or array element at the top level of a jsonb value. Existence is not nested.

-- String exists as an array element
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';  -- true

-- String exists as an object key
SELECT '{"foo": "bar"}'::jsonb ? 'foo';  -- true

-- Object values are not checked
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- false

-- Existence matches only at the top level
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar';  -- false

-- A string matches a primitive JSON string
SELECT '"foo"'::jsonb ? 'foo';  -- true

JSON objects are better suited than arrays for containment and existence tests because they are internally optimized for key lookup and do not require a linear search.

Choose a GIN operator class

Use jsonb_ops when queries use ?, ?&, or ?|. Use jsonb_path_ops when queries use only @>, @?, or @@ — it produces a smaller index with better search specificity, especially when queried keys appear frequently in the data.

jsonb_path_ops does not create index entries for JSON structures that contain no values (for example, {"a": {}}). Queries for such structures require a full index scan.

Create a GIN index

Default operator class (`jsonb_ops`):

CREATE INDEX idxgin ON api USING gin (jdoc);

`jsonb_path_ops` operator class:

CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

Expression index on a sub-key:

CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

Use expression indexes when queries consistently target a specific key. A jdoc -> 'tags' expression index is smaller and faster than a full-document GIN index for queries like jdoc -> 'tags' ? 'qui'.

Query examples

Given a jsonb column jdoc in a table api:

-- Find documents where key "company" has value "Magnafone"
-- Uses the GIN index on jdoc
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

-- Find documents where "tags" contains key or array element "qui"
-- Cannot use a plain GIN index on jdoc; requires an expression index on (jdoc -> 'tags')
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

-- jsonpath matching with GIN index support
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

-- Containment as an alternative to the existence query above
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

GIN indexes also support the @? and @@ operators for jsonpath matching. When processing these operators, a GIN index extracts clauses of the form accessors_chain = constant from the jsonpath pattern and searches based on the keys and values mentioned in those clauses. The accessors chain may include .key, [*], and [index] accessors. The jsonb_ops operator class also supports .* and .** accessors, but the jsonb_path_ops operator class does not.

B-tree and hash indexes

jsonb also supports B-tree and hash indexes, useful primarily for equality checks on complete JSON documents.

The B-tree ordering for jsonb values is:

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

Objects with equal numbers of pairs are compared in the order:

key-1, value-1, key-2 ...

Note that shorter keys are stored before longer keys, which can produce unintuitive ordering results:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Arrays with equal numbers of elements are compared element by element:

element-1, element-2 ...

Primitive JSON values are compared using the same rules as the underlying PostgreSQL type. Strings use the default database collation.

Syntax conventions

SQL/JSON path semantics follow SQL, but the syntax uses JavaScript conventions:

• . for member access

• [] for array access

• Arrays are 0-relative (unlike SQL arrays, which start from 1)

Path expressions are written as SQL string literals enclosed in single quotes. Any single quote within the value must be doubled. Embedded string literals within path expressions follow JavaScript/ECMAScript conventions — they use double quotes, and backslash escapes are supported.

Supported escape sequences: \b, \f, \n, \r, \t, \v for ASCII control characters; \u*NNNN* for a Unicode code point. Two additional sequences not in standard JSON are also supported: \x*NN* (two hex digits) and \u{*N...*} (one to six hex digits).

jsonpath variables

jsonpath accessor operators

Path expression elements

A path expression is a sequence of the following elements:

• JSON primitive literals: Unicode text, numeric, true, false, or null

• Path variables

• Accessor operators

• jsonpath operators and methods

• Parentheses for filter expressions or to define evaluation order