CODEWITHSUNDEEP
jsonpostgresql
Juan Gabriel
Juan Gabriel

Reputation: 3

Sum of json array

I have json type field, something like this

data

{"age": 44, "name": "Jun"}

{"age": 19, "name": "Pablo", "attempts": [11, 33, 20]}

{"age": 33, "name": "Maria", "attempts": [77, 10]}

Here some json data have "attempts" array, some not. When json have this array, I need get sum of array elements in different field, need result like

data , sum_of_array

{"age": 44, "name": "Jun"} , (nothing here)

{"age": 19, "name": "Pablo", "attempts": [11, 33, 20]} , 64

{"age": 33, "name": "Maria", "attempts": [77, 10]} , 87

Upvotes: 0

Views: 1058

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246588

SELECT attempts.id,
       sum(vals.v::integer) sum_attempts
FROM attempts
   LEFT JOIN LATERAL jsonb_array_elements_text(val->'attempts') vals(v)
      ON TRUE
GROUP BY attempts.id;

Use json_array_elements_text if you are using json instead of jsonb.

Upvotes: 4

Oto Shavadze
Oto Shavadze

Reputation: 42773

This works if you have unique id identity column in your table

SELECT your_table.*, tt.sum FROM your_table
LEFT JOIN (
    select id, SUM(arrvals) as sum FROM (
        select id, json_array_elements_text(CAST(your_json_column->>'attempts' AS json))::NUMERIC as arrvals  from your_table
    )t 
     group by id
) tt
ON your_table.id = tt.id

Upvotes: 0

Related Questions