Postgres Pro Standard distribution includes jsquery as a contrib module. Once you complete Postgres Pro Standard installation, create the jsquery extension, as follows:

CREATE EXTENSION jsquery;

JsQuery extension contains jsquery datatype which represents whole JSON query as a single value (like tsquery does for fulltext search). The query is an expression on JSON-document values.

Simple expression is specified as path binary_operator value or path unary_operator. See the following examples.

Path selects set of JSON values to be checked using given operators. In the simplest case, path is just a key name. In general, path is key names and placeholders combined by dot signs. Path can use the following placeholders:

Expression is true when operator is true against at least one value selected by path.

Key names could be given either with or without double quotes. Key names without double quotes shouldn't contain spaces, start with number, or concur with jsquery keyword.

The supported binary operators are:

The supported unary operators are:

Expressions could be complex. Complex expression is a set of expressions combined by logical operators (AND, OR, NOT) and grouped using parentheses.

Examples of complex expressions are given below.

Prefix expressions are expressions given in the form path (subexpression). In this case path selects JSON values to be checked using given subexpression. Check results are aggregated in the same way as in simple expressions.

Path also could contain the following special placeholders with "every" semantics:

Consider the following example.

%.#:($ >= 0 AND $ <= 1)

This example could be read as follows: there is at least one key for which the value is an array of numerics between 0 and 1.

We can rewrite this example in the following form with extra parentheses.

%(#:($ >= 0 AND $ <= 1))

The first placeholder % checks that expression in parentheses is true for at least one value in object. The second placeholder #: checks that the value is an array and all its elements satisfy expressions in parentheses.

We can rewrite this example without #: placeholder as follows.

%(NOT #(NOT ($ >= 0 AND $ <= 1)) AND $ IS ARRAY)

In this example we transform assertion that every element of array satisfies some condition to assertion that there is no one element which doesn't satisfy the same condition.

Some examples of using paths are given below.

Usage of path operators and parentheses needs some explanation. When same path operators are used multiple times they may refer to different values while you can refer to the same value multiple times by using parentheses and $ operator. See the following examples.

Same rules apply when you search inside objects and branchy structures.

Type checking operators and "every" placeholders are useful for document schema validation. JsQuery matching operator @@ is immutable and can be used in CHECK constraint. See the following example.

CREATE TABLE js (
    id serial,
    data jsonb,
    CHECK (data @@ '
        name IS STRING AND
        similar_ids.#: IS NUMERIC AND
        points.#:(x IS NUMERIC AND y IS NUMERIC)'::jsquery));

In this example check constraint validates that in "data" jsonb column: value of "name" key is string, value of "similar_ids" key is array of numerics, value of "points" key is array of objects which contain numeric values in "x" and "y" keys.

See our pgconf.eu presentation for more examples.

JsQuery extension contains two operator classes (opclasses) for GIN which provide different kinds of query optimization.

In each of two GIN opclasses jsonb documents are decomposed into entries. Each entry is associated with a particular value and its path. Difference between opclasses is in the entry representation, comparison, and usage for search optimization.

For example, jsonb document {"a": [{"b": "xyz", "c": true}, 10], "d": {"e": [7, false]}} would be decomposed into the following entries:

Since JsQuery doesn't support search in a particular array index, we consider all array elements to be equivalent. Thus, each array element is marked with the same # sign in the path.

Major problem in the entries representation is its size. In the given example key "a" is presented three times. In the large branchy documents with long keys size of naive entries representation becomes unreasonable. Both opclasses address this issue but in a slightly different way.

(hash(path_item_1.path_item_2. ... .path_item_n); value)

(value; bloom(path_item_1) | bloom(path_item_2) | ... | bloom(path_item_n))

    # SELECT gin_debug_query_path_value('x = 1 AND (*.y = 1 OR y = 2)');
     gin_debug_query_path_value
    ----------------------------
     x = 1 , entry 0           +

    # SELECT gin_debug_query_value_path('x = 1 AND (*.y = 1 OR y = 2)');
     gin_debug_query_value_path
    ----------------------------
     AND                       +
       x = 1 , entry 0         +
       OR                      +
         *.y = 1 , entry 1     +
         y = 2 , entry 2       +

    # SELECT gin_debug_query_path_value('x = 1 AND y > 0');
     gin_debug_query_path_value
    ----------------------------
     x = 1 , entry 0           +

    SELECT gin_debug_query_path_value('x = 1 AND y /*-- index */ > 0');
     gin_debug_query_path_value
    ----------------------------
     AND                       +
       x = 1 , entry 0         +
       y > 0 , entry 1         +

    SELECT gin_debug_query_path_value('x /*-- noindex */ = 1 AND y > 0');
     gin_debug_query_path_value
     ----------------------------
      y > 0 , entry 0           +

Development is sponsored by Wargaming.net.