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;
CodePudding user response:
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