jsquery

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

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

-- 最适合已知完整路径时的范围和精确搜索
CREATE INDEX ON jsonb_table USING gin (data jsonb_path_value_ops);

-- 最适合精确值搜索；支持路径中使用 % 和 *
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)
);