PostgreSQL中如何合并含对象数组的JSON字段

现有两个JSON对象数组：

[{"k": 1,"v":{"a1": null}}, {"k": 2, "v":{"b1":"B1"}}, {"k": 3, "v":{"c1":"C1"}}]

[{"k": 1,"v":{"a1": "A1"}}, {"k": 2, "v":{"b1":"B1", "b2": "B2"}}] 

期望合并后得到结果：

[{"k": 1,"v":{"a1": "A1"}}, {"k": 2, "v":{"b1":"B1", "b2": "B2"}}, {"k": 3, "v":{"c1":"C1"}}]

规则是按k字段匹配，合并对应的v对象，后出现的非空值覆盖前者。

尝试了以下SQL语句：

WITH merged_objects AS (
  SELECT k,
         jsonb_agg(v) as v
  FROM (
    SELECT elem -> 'k' as k,
           jsonb_each(elem -> 'v') as v
    FROM (
      SELECT jsonb_array_elements('[
        {"k": 1, "v": {"a1": null}},
        {"k": 2, "v": {"b1": "B1"}},
        {"k": 3, "v": {"c1": "C1"}}
      ]'::jsonb) as elem

      UNION ALL

      SELECT jsonb_array_elements('[
        {"k": 1, "v": {"a1": "A1"}},
        {"k": 2, "v": {"b1": "B1", "b2": "B2"}}
      ]'::jsonb) as elem
    ) subquery
  ) subquery
  GROUP BY k
)
SELECT jsonb_agg(jsonb_build_object('k', k, 'v', v)) as merged_array
FROM merged_objects;

但得到的结果不符合预期，v字段未正确合并，结果如下：

[{"k": 2, "v": [{"key": "b1", "value": "B1"}, {"key": "b1", "value": "B1"}, {"key": "b2", "value": "B2"}]}, {"k": 1, "v": [{"key": "a1", "value": null}, {"key": "a1", "value": "A1"}]}, {"k": 3, "v": [{"key": "c1", "value": "C1"}]}]

原SQL的问题在于：

1. 使用jsonb_each将v对象拆分为键值对行后，用jsonb_agg又把这些键值对拼成了数组，而非重新合并为JSON对象；
2. 未处理“后出现的值覆盖前者”的逻辑，重复键被多次保留，没有实现覆盖效果。

我们需要先将所有JSON元素按k分组，对每组内的v对象按顺序合并（后出现的v覆盖前面对象的重复键），最后重新组装成目标数组。

方法一：利用QUALIFY和jsonb_object_agg保留最新值 #

SELECT jsonb_agg(
         jsonb_build_object('k', k::int, 'v', merged_v)
         ORDER BY k::int
       ) AS merged_array
FROM (
  SELECT
    elem ->> 'k' AS k,
    jsonb_object_agg((kv).key, (kv).value) AS merged_v
  FROM (
    -- 合并两个数组，标记顺序确保第二个数组元素优先级更高
    SELECT elem, 1 AS seq FROM jsonb_array_elements('[
      {"k": 1, "v": {"a1": null}},
      {"k": 2, "v": {"b1": "B1"}},
      {"k": 3, "v": {"c1": "C1"}}
    ]'::jsonb) AS elem
    UNION ALL
    SELECT elem, 2 AS seq FROM jsonb_array_elements('[
      {"k": 1, "v": {"a1": "A1"}},
      {"k": 2, "v": {"b1": "B1", "b2": "B2"}}
    ]'::jsonb) AS elem
  ) AS combined,
       jsonb_each(elem -> 'v') AS kv
  -- 按k和键分组，保留seq最大的（即后出现的）值
  QUALIFY row_number() OVER (PARTITION BY elem ->> 'k', (kv).key ORDER BY seq DESC) = 1
  GROUP BY elem ->> 'k'
) AS grouped;

方法二：分步聚合合并 #

WITH all_elements AS (
  -- 合并两个数组的所有元素，标记来源顺序
  SELECT 
    elem ->> 'k' AS k,
    elem -> 'v' AS v,
    source
  FROM (
    SELECT elem, 1 AS source
    FROM jsonb_array_elements('[
      {"k": 1, "v": {"a1": null}},
      {"k": 2, "v": {"b1": "B1"}},
      {"k": 3, "v": {"c1": "C1"}}
    ]'::jsonb) AS elem
    
    UNION ALL
    
    SELECT elem, 2 AS source
    FROM jsonb_array_elements('[
      {"k": 1, "v": {"a1": "A1"}},
      {"k": 2, "v": {"b1": "B1", "b2": "B2"}}
    ]'::jsonb) AS elem
  ) AS combined
),
key_value_pairs AS (
  -- 拆分v对象的键值对，保留每个键的最新版本
  SELECT
    k,
    (kv).key,
    (kv).value
  FROM all_elements,
       jsonb_each(v) AS kv
  QUALIFY row_number() OVER (PARTITION BY k, (kv).key ORDER BY source DESC) = 1
),
merged_v AS (
  -- 按k分组，将键值对重新合并为v对象
  SELECT
    k,
    jsonb_object_agg(key, value) AS v
  FROM key_value_pairs
  GROUP BY k
)
-- 组装最终的JSON数组
SELECT jsonb_agg(jsonb_build_object('k', k::int, 'v', v) ORDER BY k::int) AS merged_array
FROM merged_v;

结果验证 #

以上方法都会输出预期结果：

[{"k":1,"v":{"a1":"A1"}},{"k":2,"v":{"b1":"B1","b2":"B2"}},{"k":3,"v":{"c1":"C1"}}]

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