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}

@Nick - My specific question is "How do I sum multiple json rows together where keys match?". Should it be phrased differently? I am relatively new to postgresql and don't know where to start on this — jotamon
– jotamon, Commented Feb 19, 2019 at 20:49
And what have you attempted? You should show your effort and where you need help, otherwise this looks like you are simply asking others to do the work for you. — Nick
– Nick, Commented Feb 19, 2019 at 20:50

klin · Accepted Answer · 2023-04-11 11:45:33Z

7

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.

edited Apr 11, 2023 at 11:45

answered Feb 19, 2019 at 20:50

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juanignaciosl · Accepted Answer · 2020-05-29 16:59:06Z

1

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 }"

edited May 29, 2020 at 16:59

answered May 29, 2020 at 16:53

juanignaciosl

3,5832 gold badges33 silver badges29 bronze badges

Collectives™ on Stack Overflow

Postgresql - Sum JSON rows on matching keys

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related