PostgreSQL - Adding up json values

Question

This is my json column in pgSQL and I need to add up all of the "sum" amounts using pgSQL.

So the query should return "50" using the data below.

'{
    "1": {
        "sum": 5,
    },
    "2": {
        "sum": 10,
    },
    "2728": {
        "sum": 30,
    },
    "2729": {
        "sum": 5,
    }
}'

I did find something like this (Get aggregate sum of json array in Postgres NOSQL json data) that kinda works if I had my values in array, but I dont, so this gives me an error.

WITH x AS( SELECT
'{
    "1": {
        "sum": 5,
    },
    "2": {
        "sum": 10,
    },
    "2728": {
        "sum": 30,
    },
    "2729": {
        "sum": 1410,
    }
}'::json as y),
sums AS(
SELECT json_array_elements(y->'2729') as j FROM x)
SELECT sum((j->>'sum')::int) FROM sums;

D-Shih · Accepted Answer · 2022-06-06 12:25:34Z

1

Because your JSON value is nested objects, We can try to use jsonb_each_text function to get all nested object which is contain sum field before SUM by that.

WITH x AS( SELECT
'{
    "1": {
        "sum": 5
    },
    "2": {
        "sum": 10
    },
    "2728": {
        "sum": 30
    },
    "2729": {
        "sum": 5
    }
}'::jsonB as y)
SELECT SUM((v.value::jsonb->'sum')::INT)
FROM x
CROSS JOIN LATERAL jsonb_each_text(x.y) v

sqlfiddle

answered Jun 6, 2022 at 12:25

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - Adding up json values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related