0

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?

2

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}
  • 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 – Piotr Dabkowski Jan 16 '18 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. – a_horse_with_no_name Jan 16 '18 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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