给定表中jsonb列p06
中的以下数据:
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
p06 | {
"id": 675010,
"cname": "Bob",
"rpjobs": [
{
"progress": 25
},
{
"progress": 13
},
{
"progress": 30
}
]
}
我正试图对
ryzom_characters
的值求和。我尝试了以下操作: 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)
但现在我想对这些值求和,它们可以是空的。
如何正确地对数组中的对象字段求和?
下面是表格结构:
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()
:
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;
可以使用左联接而不是交叉联接来保护查询不受不一致数据的影响:
left join jsonb_array_elements(p06->'rpjobs')
on jsonb_typeof(p06->'rpjobs') = 'array'
where p06->'rpjobs' <> 'null'
关于sql - 如何在Postgresql中的JSONB数组中求和?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51798293/