如何在PostgreSQL中高效聚合jsonb列数据？

要实现按id分组聚合JSONB列的所有键（数组去重合并、非数组键保留/合并），可以通过拆分键值对→分类处理→重新聚合的流程完成，以下是可行的SQL方案：

完整查询语句 #

WITH exploded AS (
  -- 拆分JSONB的键值对，数组类型拆分为单个元素
  SELECT
    id,
    key,
    CASE
      WHEN jsonb_typeof(value) = 'array' THEN elem
      ELSE value
    END AS val,
    jsonb_typeof(value) AS val_type
  FROM userdata,
       jsonb_each(data) AS kv(key, value),
       LATERAL (
         -- 数组拆分为单个元素，非数组直接保留
         SELECT elem FROM jsonb_array_elements(value) elem
         WHERE jsonb_typeof(value) = 'array'
         UNION ALL
         SELECT value WHERE jsonb_typeof(value) != 'array'
       ) AS vals(elem)
  WHERE data != '{}'::jsonb -- 跳过空JSONB
),
aggregated AS (
  -- 按id和key分类聚合
  SELECT
    id,
    key,
    CASE
      -- 数组类型：去重后重新聚合为有序数组
      WHEN MAX(val_type) = 'array' THEN jsonb_agg(DISTINCT val ORDER BY val)
      -- 对象类型：合并所有对象的键值对（去重保留唯一键）
      WHEN MAX(val_type) = 'object' THEN (
        SELECT jsonb_object_agg(k, v)
        FROM (
          SELECT DISTINCT ON (k) k, v
          FROM exploded, jsonb_each(val) AS obj(k, v)
          WHERE key = CURRENT.key AND id = CURRENT.id
          ORDER BY k
        ) AS merged_obj
      )
      -- 其他类型（数值、字符串等）：保留最后出现的值
      ELSE MAX(val)
    END AS aggregated_val
  FROM exploded
  GROUP BY id, key
)
-- 最终聚合为JSONB对象
SELECT
  id,
  jsonb_object_agg(key, aggregated_val) AS aggregated_data
FROM aggregated
GROUP BY id
ORDER BY id;

代码说明 #

1. exploded CTE：

   • 使用jsonb_each(data)将每行的JSONB拆分为key（键名）和value（键值）。
   • 对数组类型的value，用jsonb_array_elements拆分为单个元素；非数组类型直接保留原值。
   • 过滤掉空JSONB行，避免无效计算。

2. aggregated CTE：

   • 按id和key分组，根据值的类型做不同处理：
     • 数组：用jsonb_agg(DISTINCT val)去重后重新聚合，ORDER BY保证数组元素有序。
     • 对象：将多个同键的对象拆分为键值对，去重后重新合并为单个对象。
     • 基础类型（数值、字符串）：用MAX(val)保留最后出现的值（JSONB支持大小比较，最新插入的值会被保留）。

3. 最终查询：

   • 用jsonb_object_agg将每个id的聚合后键值对重新组合为JSONB对象，得到最终结果。

针对示例数据的输出 #

执行上述SQL后，会得到（修正了原示例中dir的位置笔误，符合常规键聚合逻辑）：

123.abc | {"dis": ["close", "hello", "bye"], "purpose": {"score": 0.1, "text": "hi"}, "dir": 1}
567.bhy | {"dis": ["close"]}

性能优化提示 #

• 如果表数据量较大，给id字段创建普通索引：CREATE INDEX idx_userdata_id ON userdata(id);，加速分组查询。
• 避免对全表进行频繁聚合，可考虑定期将聚合结果存储到单独的表中，减少实时计算开销。

内容的提问来源于stack exchange，提问作者donnie