sql - 如何使用其他列类型在 postgres 中聚合 Jsonb 列

Question

我在 postgres 表中有以下数据，

其中数据是一个 jsonb 列。我想得到的结果是

[
 {field_type: "Design", briefings_count: 1, meetings_count: 13}, 
 {field_type: "Engineering", briefings_count: 1, meetings_count: 13}, 
 {field_type: "Data Science", briefings_count: 0, meetings_count: 3}
]

Answer 1

解释

使用jsonb_each_text从名为 data 的 jsonb 列中提取数据的函数。然后使用 GROUP BY 聚合行，为每个不同的 field_type 获取一行。对于每个聚合，我们还需要包括 session 和简报计数，这是通过使用案例语句选择最大值来完成的，以便您可以为不同的计数创建两个单独的列。最重要的是，如果缺少某些信息，应用 coalesce 函数返回 0 而不是 NULL - 在您的示例中，它将是数据科学的简报.

在更高级别的语句中，现在我们将结果作为包含字段的表，我们需要构建一个 jsonb 对象并将它们全部聚合到一行。为此，我们使用 jsonb_build_object 向其传递包含以下内容的对:字段名称 + 值。这给我们带来了 3 行数据，每行都有一个单独的 jsonb 列和数据。由于我们只需要输出中的一行(聚合的 json)，因此我们需要在其上应用 jsonb_agg。这为我们带来了您正在寻找的结果。

代码

检查 LIVE DEMO 看看它是如何工作的。

select
  jsonb_agg(
    jsonb_build_object('field_type', field_type,
                     'briefings_count', briefings_count,
                     'meetings_count', meetings_count
                     )
    ) as agg_data
from (
  select
      j.k as field_type
    , coalesce(max(case when t.count_type = 'briefings_count' then j.v::int end),0) as briefings_count
    , coalesce(max(case when t.count_type = 'meetings_count'  then j.v::int end),0) as meetings_count
  from tbl t, 
       jsonb_each_text(data) j(k,v)
  group by j.k
  ) t