HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to sum (aggregate) all the values in a key/value JSONB?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thehowallvaluesumvaluesjsonbaggregatekey

Problem

in Postgres 10 I have the following table:

CREATE TABLE testtable (
    id  int   PRIMARY KEY,
    qty jsonb
);
INSERT INTO testtable (id,qty)
VALUES
  ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "2018-10-23": 30}' ),
  ( 2, '{"2018-08-17": 100, "2018-11-01": 200}' ),
  ( 3, '{"2018-09-03": 1, "2018-09-01": 2, "2018-10-01": 3}' );


Is there a quick way, using SQL to return the sum of each JSONB field so that the results would be:

ID Total
1   60
2   300
3   6


I have seen more complicated possibilities using UNNEST and/or replace (https://stackoverflow.com/questions/26699601/sum-of-values-of-json-array-in-postgresql).

However, I was hoping for something more elegant.

Solution

Despite having searched for hours I think I found my answer 10 minutes after posting!

Including for reference for others:

SELECT id, sum(value::float)
FROM testtable
CROSS JOIN LATERAL jsonb_each_text(qty)
GROUP BY id;

Code Snippets

SELECT id, sum(value::float)
FROM testtable
CROSS JOIN LATERAL jsonb_each_text(qty)
GROUP BY id;

Context

StackExchange Database Administrators Q#214501, answer score: 5

Revisions (0)

No revisions yet.