jsquery

Overview

Version

Install

You can install jsquery directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install jsquery;          # Install for current active PG version

pig ext install -y jsquery -v 18  # PG 18
pig ext install -y jsquery -v 17  # PG 17
pig ext install -y jsquery -v 16  # PG 16
pig ext install -y jsquery -v 15  # PG 15
pig ext install -y jsquery -v 14  # PG 14

dnf install -y jsquery_18       # PG 18
dnf install -y jsquery_17       # PG 17
dnf install -y jsquery_16       # PG 16
dnf install -y jsquery_15       # PG 15
dnf install -y jsquery_14       # PG 14

apt install -y postgresql-18-jsquery   # PG 18
apt install -y postgresql-17-jsquery   # PG 17
apt install -y postgresql-16-jsquery   # PG 16
apt install -y postgresql-15-jsquery   # PG 15
apt install -y postgresql-14-jsquery   # PG 14

Create Extension:

CREATE EXTENSION jsquery;

Usage

jsquery: data type for jsonb inspection

JsQuery provides a query language for JSONB data, similar to what tsquery does for full-text search. It offers a concise way to search nested objects and arrays with index support via GIN.

Operators

Query Syntax

Expressions follow the pattern path operator value:

Binary operators:

• = (equality), >, >=, <, <= (comparison)
• IN (list membership)
• && (overlap), @> (contains), <@ (contained in)

Unary operators:

• = * (existence check)
• IS ARRAY, IS NUMERIC, IS OBJECT, IS STRING, IS BOOLEAN (type checking)

Path Expressions

“Every” semantics (all elements must match):

• #: – all array elements
• %: – all object keys
• *: – all nested paths

Examples

Simple value matching:

SELECT * FROM jsonb_table WHERE data @@ 'name = "Alice"';
SELECT * FROM jsonb_table WHERE data @@ 'age > 21';
SELECT * FROM jsonb_table WHERE data @@ 'tags.#: IS STRING';

Logical combinations:

SELECT * FROM jsonb_table WHERE data @@ 'a = 1 AND (b = 2 OR c = 3)';

Array element matching (find array elements where both conditions hold):

SELECT * FROM jsonb_table WHERE data @@ '#(a = 1 AND b = 2)';

Object key range matching:

SELECT * FROM jsonb_table WHERE data @@ '%($ >= 10 AND $ <= 20)';

GIN Indexing

Two operator classes for different query patterns:

-- Best for range and exact searches when full path is known
CREATE INDEX ON jsonb_table USING gin (data jsonb_path_value_ops);

-- Best for exact value searches; supports % and * in paths
CREATE INDEX ON jsonb_table USING gin (data jsonb_value_path_ops);

Optimizer hints for index usage:

SELECT * FROM jsonb_table WHERE data @@ 'x = 1 /*-- index */ AND y = 2';
SELECT * FROM jsonb_table WHERE data @@ 'x = 1 /*-- noindex */ AND y = 2';

Schema Validation via CHECK Constraints

CREATE TABLE documents (
    id serial PRIMARY KEY,
    data jsonb CHECK (data @@ 'name IS STRING AND similar_ids.#: IS NUMERIC'::jsquery)
);