Postgresql - Sum JSON rows on matching keys

Question

I have two columns in my database, one of uid (myuid) and another of key value pairs representing total pets per user (totalpetsjson). I would like to query on uid and then sum the resulting JSON rows where keys match.

This query

SELECT totalpetsjson FROM mytable WHERE "myuid" IN ('john','andy') 

Results in two rows

{'dogs':3,'cats':5,'fish':144}
{'dogs':2,'lizards':4,'cats':3'}

What I want the result to be. How could I query and combine the above two rows to look like below?

{'dogs':5,'cats':8,'fish':144,'lizards':4}

Answer 1

Use the function json_each_text() which gives pairs (key, value), cast values to integer and sum them in groups by keys. Finally, aggregate the results to json:

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, "cats" : 8, "dogs" : 5 }
(1 row) 

Test it in db<>fiddle.