如何合并

[{"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"}}]

型
我尝试了以下一个：

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"}]}]

型