I have a table with a JSON column in Postgres, I am grouping the rows and I am looking for some aggregate function that could "sum up" values from jsons.

sum aggregator sums the integers, I am looking for equivalent aggregator for JSONs, for example these 2 JSONs:

 {a: 1, b: 4}
 {b: 7, c: 3}

should be aggregated to

{a: 1, b: 11, c: 3}

Note that the values in conflicting keys are summed. JSONs are always 'str->int'

Is it possible to that in Postgres? Alternatively is it possible to write a custom aggregator to do that in C?

1 Answer 1


You need to first normalize the JSON data into a result that can be aggregated:

with data (doc) as (
    ('{"a": 1, "b": 4}'::jsonb), 
    ('{"b": 7, "c": 3}'::jsonb)
select k, v::int as val
from data, jsonb_each_text(doc) as t(k,v);


k | v
a | 1
b | 4
b | 7
c | 3

This can now be aggregated per key:

select k, sum(v::int) s
from data, jsonb_each_text(doc) as t(k,v)
group by k;


k | s
c |  3
a |  1
b | 11

This can now be converted back and aggregated into a JSON:

select jsonb_object_agg(k,s) as doc
from (
  select k, sum(v::int) s
  from data, jsonb_each_text(doc) as t(k,v)
  group by k
) t


{"a": 1, "b": 11, "c": 3}
  • Thanks! This definitely works but do you know whether writing a custom aggregating function for this use case would be possible? Custom function would definitely be way faster Commented Jan 16, 2018 at 12:36
  • @PiotrDabkowski: yes, you can write a custom function for that. But as the custome function would essentially do the same thing, I doubt it would that much faster.
    – user1822
    Commented Jan 16, 2018 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.