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 docs (id serial, doc jsonb)
CREATE TABLE
insert into docs (doc) values
('{
"header": { "info": "foo"},
"data": [
{"a" : 1, "b" : 123},
{"a": 2, "b": 234},
{"a": 1, "b": 543},
{"a": 1, "b": 123},
{"a": 4, "b": 452}
]
}'),
('{
"header": { "info": "foo"},
"data": [
{"a": 4, "b": 345},
{"a": 5, "b": 234}
]
}'),
('{
"header": { "info": "foo"},
"data": [
{"a": 4, "b": 452},
{"a": 5, "b": 234}
]
}')
INSERT 0 3
select distinct id, doc
from (
select id, doc, jsonb_array_elements(doc->'data') as elem
from docs
) as docelem
where (elem->>'a')::int = 4 and (elem->>'b')::int > 400
| id | doc |
|---|---|
| 1 | {"data": [{"a": 1, "b": 123}, {"a": 2, "b": 234}, {"a": 1, "b": 543}, {"a": 1, "b": 123}, {"a": 4, "b": 452}], "header": {"info": "foo"}} |
| 3 | {"data": [{"a": 4, "b": 452}, {"a": 5, "b": 234}], "header": {"info": "foo"}} |
SELECT 2