PostgreSQL中聚合值为字符串数组的jsonb映射并去重的方法

||运算符之所以不符合需求，是因为它对重复键采用覆盖替换逻辑，而非数组合并。要实现同键数组合并并去重，需要拆解jsonb结构、去重后再重组，具体步骤如下：

解决方案SQL #

假设你的表名为your_table，jsonb列名为jsonb_col，执行以下SQL即可得到目标结果：

WITH your_table AS (
  -- 模拟你的输入数据，实际使用时替换为真实表
  SELECT '{"a": ["1", "2"]}'::jsonb AS jsonb_col
  UNION ALL
  SELECT '{"a": ["2", "3"], "b": ["5"]}'::jsonb AS jsonb_col
)
SELECT 
  jsonb_object_agg(key, unique_elements) AS aggregated_jsonb
FROM (
  -- 1. 拆解每个jsonb对象的键和数组元素
  -- 2. 按键分组，聚合去重后的元素为数组
  SELECT 
    key,
    ARRAY_AGG(DISTINCT element ORDER BY element) AS unique_elements
  FROM your_table, jsonb_each(jsonb_col)
  CROSS JOIN jsonb_array_elements_text(value) AS element
  GROUP BY key
) AS grouped_keys;

逻辑说明 #

1. 拆解结构：

   • jsonb_each(jsonb_col)将每个jsonb对象拆分为key（键名）和value（对应jsonb数组）。
   • jsonb_array_elements_text(value)将数组拆分为单个字符串元素，得到key与单个元素的一一对应关系。

2. 去重聚合：

   • 按key分组，用ARRAY_AGG(DISTINCT element)聚合去重后的元素，ORDER BY可选，用于保持数组元素有序。

3. 重组jsonb：

   • jsonb_object_agg(key, unique_elements)将分组后的键和去重数组重新组合为目标jsonb对象。

优化提示 #

• 如果不需要数组元素有序，可去掉ORDER BY element，提升执行效率。
• 若表数据量较大，可考虑给jsonb_col建立GIN索引，加快拆解过程。

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