Sum of json array

Question

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

"PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit" — Juan Gabriel
– Juan Gabriel, Commented Apr 7, 2017 at 11:44

Laurenz Albe · Accepted Answer · 2017-04-07 12:13:31Z

4

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.

edited Apr 7, 2017 at 12:13

answered Apr 7, 2017 at 11:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

8 Comments

pozs Over a year ago

This is a nasty trick. Is it documented that ->> 0 works on jsonb literals too?

Laurenz Albe Over a year ago

Don't know, I guess it is an implementation artifact. But you can just as well write vals.v::text::integer instead.

pozs Over a year ago

yes, ofc (or the jsonb_array_elements_text() function). It's just surprised me.

Oto Shavadze Over a year ago

Nice solution, but you have typo error, just if OP uses json type, then shuold be json_array_elements_text

Laurenz Albe Over a year ago

@pozs This is going off-topic, but if you look at src/backend/utils/adt/jsonb.c you'll find that a root scalar is stored in jsonb as an array with one element.

|

Oto Shavadze · Accepted Answer · 2017-04-07 11:50:07Z

0

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

answered Apr 7, 2017 at 11:50

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

1 Comment

pozs Over a year ago

You could use LATERAL to simplify your JOIN (this will cause 2 table scans)

Collectives™ on Stack Overflow

Sum of json array

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related