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?.
begin;
create temp table teams(data jsonb);
insert into teams values ('{ "id" : 1, "name": "foobar", "members": {
"coach": {"id": 1, "name": "A dude" },
"players": [
{ "id": 2,"name": "B dude" }, { "id" : 3, "name": "C dude" }, { "id" : 4,"name": "D dude" }, { "id" : 6,"name": "F dude" },
{ "id" : 7, "name": "G dude" }]}}'::jsonb);
insert into teams values('
{"id": 2,"name": "bazbar",
"members": {
"coach": {"id": 11,"name": "A dude"},
"players": [
{ "id": 3, "name": "C dude" },{"id": 5, "name": "E dude"},
{"id": 6, "name": "F dude"},{"id": 7, "name": "G dude"},
{"id": 8, "name": "H dude"}
] }}');
commit;
1 rows affected
1 rows affected
with a as(
select data['id'] as teamid,
(jsonb_array_elements( data['members']['players']))['id'] as playerid
from teams), b as( select teamid, array_agg(playerid) as playerids from a group by 1)
select b.* from b where b.playerids @> '{3,4,7}';
| teamid | playerids |
|---|---|
| 1 | {2,3,4,6,7} |