By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl(id int, js jsonb);
INSERT INTO tbl VALUES
(1, jsonb '{"user_info": [{"name":"Team member 1","email":"abc@xyz.com","employee_id":"1"}
, {"name":"Team member 2","email":"def@xyz.com","employee_id":"2"}]}')
, (2, jsonb '{"user_info": [{"name":"Team member 1","email":"foo@xyz.com","employee_id":"1"}
, {"name":"Team member 2","email":"bar@xyz.com","employee_id":"2"}]}')
;
2 rows affected
-- plain containment, Postgres 9.4+
SELECT *
FROM tbl
WHERE js @> '{"user_info":[{"email":"abc@xyz.com"}]}';
| id | js |
|---|---|
| 1 | {"user_info": [{"name": "Team member 1", "email": "abc@xyz.com", "employee_id": "1"}, {"name": "Team member 2", "email": "def@xyz.com", "employee_id": "2"}]} |
-- SQL/JSON path expression, Postgres 12+
SELECT *
FROM tbl
WHERE js @? '$.user_info[*].email ? (@ == "abc@xyz.com")';
| id | js |
|---|---|
| 1 | {"user_info": [{"name": "Team member 1", "email": "abc@xyz.com", "employee_id": "1"}, {"name": "Team member 2", "email": "def@xyz.com", "employee_id": "2"}]} |
-- Either can use an index efficiently
CREATE INDEX tbl_js_path_ops_idx ON tbl USING gin (js jsonb_path_ops);
SET enable_seqscan = off; -- only for demonstration with minimal table!
EXPLAIN
SELECT * FROM tbl WHERE js @> '{"user_info":[{"email":"abc@xyz.com"}]}';
EXPLAIN
SELECT * FROM tbl WHERE js @? '$.user_info[*].email ? (@ == "abc@xyz.com")';
| QUERY PLAN |
|---|
| Bitmap Heap Scan on tbl (cost=8.00..12.01 rows=1 width=36) |
| Recheck Cond: (js @> '{"user_info": [{"email": "abc@xyz.com"}]}'::jsonb) |
| -> Bitmap Index Scan on tbl_js_path_ops_idx (cost=0.00..8.00 rows=1 width=0) |
| Index Cond: (js @> '{"user_info": [{"email": "abc@xyz.com"}]}'::jsonb) |
| QUERY PLAN |
|---|
| Bitmap Heap Scan on tbl (cost=8.00..12.01 rows=1 width=36) |
| Recheck Cond: (js @? '$."user_info"[*]."email"?(@ == "abc@xyz.com")'::jsonpath) |
| -> Bitmap Index Scan on tbl_js_path_ops_idx (cost=0.00..8.00 rows=1 width=0) |
| Index Cond: (js @? '$."user_info"[*]."email"?(@ == "abc@xyz.com")'::jsonpath) |