Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (id int, data jsonb);
INSERT INTO tbl VALUES
  (1, '{"foo": 20, "bar": "hello", "baz": null}')       -- bar matches
, (2, '{"foo": "hello", "bar": "bye", "baz": "urg"}')  -- foo matches
, (3, '{"foo": 20, "bar": "bye", "baz": null}')         -- no matching value
, (4, '{"x": 20, "y": "bye", "z": null}')               -- no matching key
, (5, '["x", "y", "z", null]')                          -- sth else entirely
;

-- Basic GIN index
-- DROP INDEX tbl_data_idx;
-- CREATE INDEX tbl_data_idx ON tbl USING gin (data);

-- Optimized GIN index
-- DROP INDEX tbl_data_pathops_idx;
CREATE INDEX tbl_data_pathops_idx ON tbl USING gin (data jsonb_path_ops);

CREATE TABLE

INSERT 0 5

CREATE INDEX

SELECT *
FROM   tbl t
WHERE  data @? '$.keyvalue() ? (@."key" like_regex "foo|bar") ? (@."value" == "hello")';

id
data

1
{"bar": "hello", "baz": null, "foo": 20}

2
{"bar": "bye", "baz": "urg", "foo": "hello"}

SELECT 2

-- force index for tiny table if possible
SET enable_seqscan = off;

EXPLAIN
SELECT *
FROM   tbl t
WHERE  data @? '$.keyvalue() ? (@."key" like_regex "foo|bar") ? (@."value" == "hello")';

SET

QUERY PLAN

Bitmap Heap Scan on tbl t  (cost=56.00..60.01 rows=1 width=36)

  Recheck Cond: (data @? '$.keyvalue()?(@."key" like_regex "foo|bar")?(@."value" == "hello")'::jsonpath)

  ->  Bitmap Index Scan on tbl_data_pathops_idx  (cost=0.00..56.00 rows=1 width=0)

        Index Cond: (data @? '$.keyvalue()?(@."key" like_regex "foo|bar")?(@."value" == "hello")'::jsonpath)

EXPLAIN

id	data
1	{"bar": "hello", "baz": null, "foo": 20}
2	{"bar": "bye", "baz": "urg", "foo": "hello"}