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 mytable(data jsonb);
insert into mytable values ('{
"pages": "64",
"stats": {
"1": { "200": "55", "400": "4" },
"2": { "200": "1" },
"3": { "200": "1", "404": "13" }
}
}');
1 rows affected
select
sum(z.cnt::int) no_responses,
sum(z.cnt::int) filter(where z.code::int = 200) no_200_responses
from mytable t
cross join lateral jsonb_each(t.data -> 'stats') as x(kx, obj)
cross join lateral jsonb_each_text(x.obj) as z(code, cnt)
| no_responses | no_200_responses |
|---|---|
| 74 | 57 |