JsQuery

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.

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. 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+.

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.

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 particular value and it's path. Difference between opclasses is in the entry representation, comparison and usage for search optimization.

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 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 leafs are GIN search entries. 

Documentation: https://postgrespro.com/docs/postgrespro/10/jsquery.


to Extensions Repository: JsQuery