PostgreSQL – Adding up json values

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;

Answer

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