Question 213042 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to sum json objects based on the keys?

Given a column with json objects, how to sum them based on the keys using sql?

{"A": 10, "B": -5}
{"A": 20}
{"A": -15, "B": -5}
{"A": -10, "C": 77}

Result:

{"A": 5, "B": -10, "C": 77}
Tags:

2

Answers


  1. 0

    Use jsonb_each_text() and the necessary casts for this:

    with indata (j) as (
  values 
  ('{"A": 10, "B": -5}'::jsonb), ('{"A": 20}'),
  ('{"A": -15, "B": -5}'), ('{"A": -10, "C": 77}')
)
select k, sum(v::int)
  from indata
       cross join lateral jsonb_each_text(j) as e(k,v)
 group by k;

    Working fiddle

    Login or Signup to reply.
  2. 0

    You can first perform the summation via aggregation and then combine the results into a single JSON object with jsonb_object_agg:

    select jsonb_object_agg(t.key, t.s) from (select v.key, sum(v.value::int) s 
    from tbl t cross join jsonb_each(t.js) v group by v.key) t

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search