snippetsqlMinor
How to sum (aggregate) all the values in a key/value JSONB?
Viewed 0 times
thehowallvaluesumvaluesjsonbaggregatekey
Problem
in Postgres 10 I have the following table:
Is there a quick way, using SQL to return the sum of each JSONB field so that the results would be:
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.
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 6I 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:
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.