如何将PostgreSQL JSONB列转换为指定的JSONB格式？

需求回顾 #

现有words JSONB列，结构包含enterprise和private两个数组，每个数组元素格式如下：

{
  "name": "bin",
  "type": 0,
  "props": {"score": 1, "id": "bin"},
  "status": "supported"
}

需要将其转换为新结构：

• 按每个对象的props.id分组
• 同一分组下的所有name值收集到words数组
• 同一分组下status为unsupported的name值收集到exclude数组
• 补充固定字段visible: false、other: []

实现SQL #

SELECT jsonb_object_agg(category, items) AS transformed_words
FROM (
  SELECT
    category,
    jsonb_agg(
      jsonb_build_object(
        'id', item_id,
        'score', item_score,
        'visible', false,
        'words', words_array,
        'other', '[]'::jsonb,
        'exclude', exclude_array
      )
    ) AS items
  FROM (
    SELECT
      key AS category,
      (obj->'props'->>'id') AS item_id,
      (obj->'props'->>'score')::int AS item_score,
      array_agg(obj->>'name') AS words_array,
      array_remove(array_agg(CASE WHEN obj->>'status' = 'unsupported' THEN obj->>'name' END), NULL) AS exclude_array
    FROM your_table,
         jsonb_each(words) AS j(key, arr),
         jsonb_to_recordset(arr) AS obj(name text, props jsonb, status text)
    GROUP BY key, (obj->'props'->>'id'), (obj->'props'->>'score')
  ) AS grouped_items
  GROUP BY category
) AS final_groups;

分步解释 #

• 拆解顶层JSON结构：使用jsonb_each(words)将words列的顶层键值对拆分成行，key对应enterprise/private，arr对应各自的对象数组。
• 展开数组元素：通过jsonb_to_recordset(arr)将每个数组中的JSON对象展开为关系型行，提取需要的name、props、status字段。
• 按规则分组聚合：
  1. 按category（原顶层键）和props.id分组，确保同一ID的对象归为一组
  2. array_agg(obj->>'name')收集所有name到words数组
  3. 用CASE筛选status为unsupported的name，再通过array_remove去掉空值，得到exclude数组
• 重组嵌套JSON结构：
  1. 用jsonb_build_object构建每个分组的目标JSON对象，补充固定字段
  2. jsonb_agg将同一category下的对象聚合为数组
  3. 最后用jsonb_object_agg将category和对应对象数组重组为顶层JSONB对象

空数组处理 #

当原words列中enterprise或private为空数组时，jsonb_to_recordset不会生成对应行，最终结果中该键会被自动忽略。若需保留空数组，可通过左连接原表顶层键或调整逻辑补充空数组。

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