sql - PostgreSQL JSON 对象总和

Question

我有一列包含这样的 json 数据：

{
    "40593":[{"id":1,"amount":1}],
    "40594":[{"id":1,"amount":1},{"id":2,"amount":1}]
}

我想根据给定 id 的 amount 属性计算总和。对于上面 id 1 的示例，我想得到 2。

标签： sqlarraysjsonpostgresqlsum

Answer 1

您可以枚举对象的键，取消嵌套数组，然后聚合：

select
    (y.obj ->> 'id')::int id,
    sum((y.obj ->> 'amount')::int) total_amount
from mytable t
cross join lateral jsonb_object_keys(t.mycol) x(attr)
cross join lateral jsonb_array_elements(t.mycol -> x.attr) as y(obj)
group by (y.obj ->> 'id')::int
order by id

DB Fiddle 上的演示：

编号 | 总金额
-: | ------------:
 1 | 2
 2 | 1