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?.
drop table if exists my_table;
create table my_table(myuid text, totalpetsjson json);
insert into my_table values
('john', '{"dogs":3,"cats":5,"fish":144}'),
('andy', '{"dogs":2,"lizards":4,"cats":3}');
DROP TABLE
CREATE TABLE
INSERT 0 2
select json_object_agg(key, sum)
from (
select key, sum(value::int)
from my_table
cross join json_each_text(totalpetsjson)
where myuid in ('john','andy')
group by key
) s
| json_object_agg |
|---|
| { "fish" : 144, "lizards" : 4, "dogs" : 5, "cats" : 8 } |
SELECT 1