Aggregate JSON column

Question

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?

Answer 1

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

with data (doc) as (
  values 
    ('{"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);

returns

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;

returns:

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

returns

doc                      
-------------------------
{"a": 1, "b": 11, "c": 3}