JsQuery is a language to query
jsonb data type. Its primary goal is to provide an
additional functionality for jsonb, such as a simple and
effective way for search in nested objects and arrays, as well as
additional comparison operators with index support.
JsQuery is implemented by means of a
jsquery data type (similar to tsquery) and the
@@ match operator for jsonb.
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.
x = "abc" - value of key
"x" is equal to "abc";
$ @> [4, 5, "zzz"] - the JSON
document is an array containing values 4, 5 and
"zzz";
"abc xyz" >= 10 - value of key
"abc xyz" is greater than or equal to 10;
volume IS NUMERIC - type of key
"volume" is numeric.
$ = true - the whole JSON document is just
a true.
similar_ids.@# > 5 - similar_ids is an
array or object of length greater than 5;
similar_product_ids.# = "0684824396"
- array similar_product_ids contains string
"0684824396".
*.color = "red" - there is object
somewhere which key "color" has value
"red".
foo = * - key "foo" exists in
object.
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:
# - any index of array;
#N - Nth index of array;
% - any key of object;
* - any sequence of array indexes and
object keys;
@# - length of array or object, could be
only used as last component of path;
$ - the whole JSON document as single
value, could be only the whole path.
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:
Equality operator: =;
Numeric comparison operators: >,
>=, <,
<=;
Search in the list of scalar values using
IN operator;
Array comparison operators: &&
(overlap), @> (contains),
<@ (contained in).
Filtering operator: ~~.
Taking jsonb data as the left operand and a
jsquery expression as the right operand, this operator
checks that jsonb data contains any entries that
satisfy the condition provided in a jsquery expression
and returns an array of such entries, if any.
The supported unary operators are:
Check for existence operator: = *;
Check for type operators: IS ARRAY,
IS NUMERIC, IS OBJECT,
IS STRING and
IS BOOLEAN.
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.
a = 1 AND (b = 2 OR c = 3) AND NOT d = 1
x.% = true OR x.# = true
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.
#(a = 1 AND b = 2) - an array contains
an element where the a key is 2
and the b key is 2
%($ >= 10 AND $ <= 20) - an
object contains a key with a value between 10 and 20
Path also could contain the following special placeholders with "every" semantics:
#: - every index of array;
%: - every key of object;
*: - every sequence of array indexes and
object keys.
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.
numbers.#: IS NUMERIC - every element of
"numbers" array is numeric.
*:($ IS OBJECT OR $ IS BOOLEAN) - JSON is a
structure of nested objects with booleans as leaf values.
#:.%:($ >= 0 AND $ <= 1) - each
element of array is object containing only numeric values
between 0 and 1.
documents.#:.% = * - "documents"
is an array of objects containing at least one key.
%.#: ($ IS STRING) - JSON object contains
at least one array of strings.
#.% = true - at least one array element is
an object that contains at least one "true" value.
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.
# < 10 AND # > 20 - there is an element
less than 10 and another element greater than 20.
#($ < 10 AND $ > 20) - there is an element
that is both less than 10 and greater than 20 (impossible).
#($ >= 10 AND $ <= 20) - there is an
element between 10 and 20.
# >= 10 AND # <= 20 - there is an element
greater than or equal to 10 and another element less than or equal
to 20. The query can be satisfied by an array with no elements
between 10 and 20, for instance [0,30].
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.
jsonb_path_value_ops
jsonb_value_path_ops
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:
"a".#."b"."xyz"
"a".#."c".true
"a".#.10
"d"."e".#.7
"d"."e".#.false
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.
jsonb_path_value_ops represents entry as pair of path hash and value. The following pseudocode illustrates it.
(hash(path_item_1.path_item_2. ... .path_item_n); value)
In comparison of entries path hash is the higher part of entry and value is its lower part. This determines the features of this opclass. Since path is hashed and it is higher part of entry we need to know the full path to the value in order to use it for search. However, once path is specified we can use both exact and range searches very efficiently.
jsonb_value_path_ops represents entry as pair of value and bloom filter of path.
(value; bloom(path_item_1) | bloom(path_item_2) | ... | bloom(path_item_n))
In comparison of entries value is the higher part of entry and
bloom filter of path is its lower part. This determines the
features of this opclass. Since value is the higher part of
entry we can perform only exact value search efficiently. Range
value search is possible as well but we would have to filter all
the different paths where matching values occur. Bloom
filter over path items allows index usage for conditions
containing % and * in
their paths.
JsQuery opclasses perform complex query optimization. Thus it's valuable for developer or administrator to see the result of such optimization. Unfortunately, opclasses aren't allowed to do any custom output to the EXPLAIN. That's why JsQuery provides the following functions which allows to see how particular opclass optimizes given query.
gin_debug_query_path_value(jsquery) - for jsonb_path_value_ops
gin_debug_query_value_path(jsquery) - for jsonb_value_path_ops
Result of these functions is a textual representation of query tree which leaves are GIN search entries. The following examples show different results of query optimization by different opclasses.
# 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 +
Unfortunately, jsonb has no statistics yet. That's why JsQuery optimizer has to do imperative decision while selecting conditions to be evaluated using index. This decision is made by assumption that some condition types are less selective than others. Optimizer divides conditions into the following selectivity class (listed by descending of selectivity).
Equality (x = c)
Range (c1 < x < c2)
Inequality (x > c)
Is (x is type)
Any (x = *)
Optimizer evades index evaluation of less selective conditions
when possible. For example, in the
x = 1 AND y > 0 query
x = 1 is assumed to be more selective than
y > 0. That's why index isn't used for
evaluation of y > 0.
# SELECT gin_debug_query_path_value('x = 1 AND y > 0');
gin_debug_query_path_value
----------------------------
x = 1 , entry 0 +
With lack of statistics decisions made by optimizer can be
inaccurate. That's why JsQuery supports hints. Comments
/*-- index */ and
/*-- noindex */ placed in the conditions
force optimizer to use and not to use index correspondingly.
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 +
Teodor Sigaev <teodor@sigaev.ru>, Postgres
Professional, Moscow, Russia
Alexander Korotkov <aekorotkov@gmail.com>,
Postgres Professional, Moscow, Russia
Oleg Bartunov <oleg@sai.msu.su>, Postgres
Professional, Moscow, Russia
Development is sponsored by Wargaming.net.