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.

Answer 2

Building on top of the previous anwser by klin, that approach can be expanded if you're not interested in flattening a full table but keeping a grouping:

SELECT x, json_object_agg(key, sum)
FROM (
    SELECT x, key, sum(value::INT) AS sum
    FROM (
        VALUES (1, '{
          "a": 10,
          "b": 11
        }'::JSONB),
        (1, '{
          "b": 12,
          "c": 13
        }'::JSONB),
        (2, '{
          "b": 14,
          "c": 15
        }'::JSONB)
    ) AS mytable(x, y)
             CROSS JOIN jsonb_each_text(Y)
    GROUP BY 1, 2
) x
GROUP BY 1
ORDER BY 1;

Result:

1,"{ ""c"" : 13, ""b"" : 23, ""a"" : 10 }"
2,"{ ""c"" : 15, ""b"" : 14 }"