query to sum value array object on postgres

Question

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'

D-Shih · Accepted Answer · 2022-06-10 01:36:01Z

2

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

answered Jun 10, 2022 at 1:36

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

4 Comments

wuku Over a year ago

the column type addtional is 'text' and I tried the query resulting ``` ERROR: cannot call json_array_elements on a non-array ```

wuku Over a year ago

now it's work modified on "slow" t1 -> "slow" json_array_elements(t1.additional::json) -> json_array_elements(additional::json) I don't know why ? maybe u can tell me why?

D-Shih Over a year ago

It is just an alias name for that, that might be worked alias name dbfiddle.uk/…

wuku Over a year ago

can I ask again ?, it's how to set ::json -> 'default' to 'non_default" base on other column. Example I have column -> name and value -> 'john' and other column -> name_adds and the value -> 'john'. Both of column name and name_adds can be diffrent value. If name and name_adds is same I want to (additional::json->'default') and if it not same I want to (additional::json->'non_default'). How I can do that ????

Collectives™ on Stack Overflow

query to sum value array object on postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related