2

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.

  • Are these numbers inside the jsonb floats -- do they have a fractional part? Or, are they just integers? – Evan Carroll Aug 9 '18 at 16:58
  • More a theoretical question. Most likely will be Integer so can cast to Integer. – TheStranger Aug 10 '18 at 11:52
2

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;

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.