query to sum value array object on postgres-CodePudding

I have table slow with column default and type column is text and the value on the bellow:

{"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}], "non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 100, "value_2": 0.1}]}

and how to sum for each of default -> value_1 ?

I have tried this it's result null

select sum(cast(additional ::json-> 'default' ::text->> 'value_1' as integer)) as sum_default from "slow" where id = 'id'

CodePudding user response：

Because your JSON value is an object containing an array, We can try to use json_array_elements function to get array by key is default

select SUM((v.val->>'value_1')::INT)
from "slow" t1
cross join lateral json_array_elements(t1.additional::json-> 'default') as v(val)
where id = 'id'

sqlfiddle