jsquery

JsQuery – json query language with GIN indexing support

Introduction

JsQuery – is a language to query jsonb data type, introduced in PostgreSQL release 9.4.

It's primary goal is to provide an additional functionality to jsonb (currently missing in PostgreSQL), such as a simple and effective way to search in nested objects and arrays, more comparison operators with indexes support. We hope, that jsquery will be eventually a part of PostgreSQL.

Jsquery is released as jsquery data type (similar to tsquery) and @@ match operator for jsonb.

Authors

• Teodor Sigaev , Postgres Professional, Moscow, Russia
• Alexander Korotkov , Postgres Professional, Moscow, Russia
• Oleg Bartunov , Postgres Professional, Moscow, Russia

Availability

JsQuery is realized as an extension and not available in default PostgreSQL installation. It is available from github under the same license as PostgreSQL and supports PostgreSQL 9.4+.

Regards

Development was sponsored by Wargaming.net.

Installation

JsQuery is PostgreSQL extension which requires PostgreSQL 9.4 or higher. Before build and install you should ensure following:

• PostgreSQL version is 9.4 or higher.
• You have development package of PostgreSQL installed or you built PostgreSQL from source.
• You have flex and bison installed on your system. JsQuery was tested on flex 2.5.37-2.5.39, bison 2.7.12.
• Your PATH variable is configured so that pg_config command available, or set PG_CONFIG variable.

Typical installation procedure may look like this:

$ git clone https://github.com/postgrespro/jsquery.git
$ cd jsquery
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck
$ psql DB -c "CREATE EXTENSION jsquery;"

JSON query language

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 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 a 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 an array;
• #N – N-th index of an array;
• % – any key of an object;
• * – any sequence of array indexes and object keys;
• @# – length of array or object, may only be used as the last component of a path;
• $ – the whole JSON document as single value, may only be 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 may not contain spaces, start with a number or match a jsquery keyword.

The supported binary operators are:

• Equality operator: =;
• Numeric comparison operators: >, >=, `` (contains), = 10 AND $ = 0 AND $ = 0 AND $ = 0 AND $ = 0 AND $ 20 – an element less than 10 exists, and another element greater than 20 exists.
• #($ 20) – an element which is both less than 10 and greater than 20 exists (impossible).
• `#($ >= 10 AND $ = 10 AND # c)

1. Is (x is type)
2. Any (x = *)

The optimizer avoids 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 the 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 the lack of statistics, decisions made by optimizer can be inaccurate. That's why JsQuery supports hints. The comments /*-- index */ or /*-- noindex */ placed in the conditions force the optimizer to use or not use an 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           +

Contribution

Please note that JsQuery is still under development. While it's stable and tested, it may contain some bugs. Don't hesitate to create issues at github with your bug reports.

If there's some functionality you'd like to see added to JsQuery and you feel like you can implement it, then you're welcome to make pull requests.