Can I sum an array of jsonb in Postgresql with dynamic keys in a select statement?

Question

I have a jsonb object in postgres:

[{"a": 1, "b":5}, {"a":2, "c":3}]

I would like to get an aggregate sum per unique key:

{"a":3, "b":5, "c":3}

The keys are unpredictable.

Is it possible to do this in Postgres with a select statement?

404 · Accepted Answer · 2018-11-28 19:55:28Z

2

Query:

SELECT key, SUM(value::INTEGER)
FROM (
  SELECT (JSONB_EACH_TEXT(j)).*
  FROM JSONB_ARRAY_ELEMENTS('[{"a": 1, "b":5}, {"a":2, "c":3}]') j
) j
GROUP BY key
ORDER BY key;

Results:

| key | sum |
| --- | --- |
| a   | 3   |
| b   | 5   |
| c   | 3   |

DB Fiddle

answered Nov 28, 2018 at 19:55

404

8,7622 gold badges34 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

Can I sum an array of jsonb in Postgresql with dynamic keys in a select statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related