How to sum a value in a JSONB array in Postgresql?

Question

Given the following data in the jsonb column p06 in the table ryzom_characters:

        -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    p06 | {
  "id": 675010,
  "cname": "Bob",
  "rpjobs": [
    {
      "progress": 25
    },
    {
      "progress": 13
    },
    {
      "progress": 30
    }
  ]
}

I am attempting to sum the value of progress. I have attempted the following:

    SELECT 
c.cname AS cname,
jsonb_array_elements(c.p06->'rpjobs')::jsonb->'progress' AS value 
FROM ryzom_characters c
Where cid = 675010
ORDER BY value DESC 
LIMIT 50;

Which correctly lists the values:

 cname  | value
--------+-------
 Savisi | 30
 Savisi | 25
 Savisi | 13
(3 rows)

But now I would like to sum these values, which could be null.

How do I correctly sum an object field within an array?

Here is the table structure:

                     Table "public.ryzom_characters"
    Column     |          Type          | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
 cid           | bigint                 |           |          |
 cname         | character varying(255) |           | not null |
 p06           | jsonb                  |           |          |
 x01           | jsonb                  |           |          |

The data structure is not clear. Please, edit the question and add the table definition (\d ryzom_characters in psql). — klin
– klin, Commented Aug 11, 2018 at 9:56

klin · Accepted Answer · 2018-08-11 11:04:06Z

4

Use the function jsonb_array_elements() in a lateral join in the from clause:

select cname, sum(coalesce(value, '0')::int) as value
from (
    select 
        p06->>'cname' as cname, 
        value->>'progress' as value
    from ryzom_characters
    cross join jsonb_array_elements(p06->'rpjobs')
    where cid = 675010
    ) s
group by cname
order by value desc 
limit 50;

You can use left join instead of cross join to protect the query against inconsistent data:

    left join jsonb_array_elements(p06->'rpjobs')
    on jsonb_typeof(p06->'rpjobs') = 'array'
    where p06->'rpjobs' <> 'null'

edited Aug 11, 2018 at 11:04

answered Aug 11, 2018 at 10:42

klin

123k15 gold badges240 silver badges262 bronze badges

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

8 Comments

crmepham Over a year ago

@kiln Is it possible to not restrict by cid with that query? Also cname is another column on the 'ryzom_characters' table, not a element in the p06 jsonb sorry will correct description.

klin Over a year ago

Of course, you can skip the condition. Then you might want (or not) to add the column to select list and use it in group by clause in the outer query, depending on your expectations.

crmepham Over a year ago

@kiln I am getting error: cannot extract elements from a scalar. When I removed the where clause?

klin Over a year ago

Well, your data is not consistent, in some rows 'rpjobs' is not an array.

crmepham Over a year ago

@kiln it could be null in some rows. Is it possible to protect again this?

|

Patrick · Accepted Answer · 2018-08-11 11:03:07Z

0

The function jsonb_array_elements() is a set-returning function. You should therefore use it as a row source (in the FROM clause). After the call you have a table where every row contains an array element. From there on it is relatively easy.

SELECT cname, 
       sum(coalesce(r.prog->>'progress'::int, 0)) AS value  
FROM ryzom_characters c,
     jsonb_array_elements(c.p06->'rpjobs') r (prog)
WHERE c.cid = 675010
GROUP BY cname 
ORDER BY value DESC 
LIMIT 50;

edited Aug 11, 2018 at 11:03

answered Aug 11, 2018 at 10:36

Patrick

33k7 gold badges73 silver badges102 bronze badges

4 Comments

crmepham Over a year ago

This gives the error: ERROR: COALESCE types jsonb and integer cannot be matched?

Patrick Over a year ago

That was a type conversion error. See updated answer.

crmepham Over a year ago

I get the error: ERROR: invalid input syntax for integer: "progress"?

Patrick Over a year ago

That would indicate that progress has some data which is not an integer.

Collectives™ on Stack Overflow

How to sum a value in a JSONB array in Postgresql?

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related