如何在Postgresql中的JSONB数组中求和? [英] How to sum a value in a JSONB array in Postgresql?

问题描述

给出表ryzom_characters中jsonb列p06中的以下数据:

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
    }
  ]
}

我正在尝试对progress的值求和.我尝试了以下操作:

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;

正确列出值的地方:

 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?

这是表结构:

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

推荐答案

在from子句的横向联接中使用函数jsonb_array_elements():

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'

这篇关于如何在Postgresql中的JSONB数组中求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！