jsquery

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

pig install jsquery;		# install via package name, for the active PG version

pig install jsquery -v 18;   # install for PG 18
pig install jsquery -v 17;   # install for PG 17
pig install jsquery -v 16;   # install for PG 16
pig install jsquery -v 15;   # install for PG 15
pig install jsquery -v 14;   # install for PG 14

CREATE EXTENSION jsquery;

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';

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

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

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

-- 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);

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

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