How To Sum A Value In A Jsonb Array In Postgresql?

Given the following data in the jsonb column p06 in the table ryzom_characters: -[ RECORD 1 ]-------------------------------------------------------------------------------

Solution 1:

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

select cname, sum(coalesce(value, '0')::int) asvaluefrom (
    select 
        p06->>'cname'as cname, 
        value->>'progress'asvaluefrom ryzom_characters
    crossjoin jsonb_array_elements(p06->'rpjobs')
    where cid =675010
    ) s
groupby cname
orderbyvaluedesc 
limit 50;

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

left joinjsonb_array_elements(p06->'rpjobs')
    onjsonb_typeof(p06->'rpjobs') = 'array'where p06->'rpjobs' <> 'null'

Solution 2:

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)) ASvalueFROM ryzom_characters c,
     jsonb_array_elements(c.p06->'rpjobs') r (prog)
WHERE c.cid =675010GROUPBY cname 
ORDERBYvalueDESC 
LIMIT 50;