在PostgreSQL中对JSON数组进行聚合

Question

我看到很多关于使用json_array_elements提取JSON数组元素的参考资料。然而，这似乎只适用于正好有一个数组的情况。如果我在通用查询中使用它，会出现错误：

“ERROR: 无法对标量调用json_array_elements”

例如，给定以下内容：

我想要提取如下内容：

是否可以使用json_array_elements对JSON数组进行聚合操作呢？

- Aserian

---

“这似乎只适用于正好1个数组。如果我在通用查询中使用它”- 我不确定你的意思。您能展示一下导致错误的代码吗？” - Bergi

Answer 1

使用orders->'items'函数来展开数据：

select elem->>'name' as name, (elem->>'price')::numeric as price
from my_table
cross join jsonb_array_elements(orders->'items') as elem;

从扁平化数据中获取你想要的聚合数据很容易：

select name, count(*), sum(price) as total_value_sold
from (
    select elem->>'name' as name, (elem->>'price')::numeric as price
    from my_table
    cross join jsonb_array_elements(orders->'items') as elem
    ) s
group by name;

Db<>fiddle.

- klin

---

当我运行你的fiddle时，它可以正常工作 - 但是当我用WITH (SELECT * FROM my_local_table)替换WITH (VALUES ...)时，它会抛出相同的错误。 - Aserian

你是怎么这样调用表名的？orders->'items'对我来说并没有意义，因为它不是列名。我正在使用你的语法在我的本地表上尝试（没有fiddle中的WITH），但它显示错误：列"orders"不存在。 - Aserian

我的查询中的“orders”是列名。请将其替换为实际的jsonb列名，这是你在问题中忘记包含的。 - klin

好的，抱歉，问题实际上是数据中存在空值数组。你的答案有效，关键是还要添加 WHERE orders->>items IS NOT NULL。 - Aserian