如何合并两个 JSON 数组中的记录？

我有两个返回 JSON 数组的 Postgres SQL 查询：

q1:

[
  {"id": 1, "a": "text1a", "b": "text1b"},
  {"id": 2, "a": "text2a", "b": "text2b"},
  {"id": 2, "a": "text3a", "b": "text3b"},
  ...
 ]

q2:

[
  {"id": 1, "percent": 12.50}, 
  {"id": 2, "percent": 75.00}, 
  {"id": 3, "percent": 12.50}
  ...
]

我希望结果是两个数组唯一元素的并集：

[
  {"id": 1, "a": "text1a", "b": "text1b", "percent": 12.50},
  {"id": 2, "a": "text2a", "b": "text2b", "percent": 75.00},
  {"id": 3, "a": "text3a", "b": "text3b", "percent": 12.50},
  ...
]

在 Postgres 9.4 中如何使用 SQL 来完成此操作？

Assuming数据类型jsonb并且您想要合并共享相同“id”值的每个 JSON 数组的记录。

Postgres9.5

新的让事情变得更简单连接运算符|| for jsonb values:

SELECT json_agg(elem1 || elem2) AS result
FROM  (
   SELECT elem1->>'id' AS id, elem1
   FROM  (
      SELECT '[
        {"id":1, "percent":12.50}, 
        {"id":2, "percent":75.00}, 
        {"id":3, "percent":12.50}
       ]'::jsonb AS js
      ) t, jsonb_array_elements(t.js) elem1
   ) t1
FULL JOIN (
   SELECT elem2->>'id' AS id, elem2
   FROM  (
      SELECT '[
        {"id": 1, "a": "text1a", "b": "text1b", "percent":12.50},
        {"id": 2, "a": "text2a", "b": "text2b", "percent":75.00},
        {"id": 3, "a": "text3a", "b": "text3b", "percent":12.50}]'::jsonb AS js
      ) t, jsonb_array_elements(t.js) elem2
   ) t2 USING (id);

The FULL [OUTER] JOIN确保您不会丢失其他数组中没有匹配的记录。

方式jsonb具有方便的属性，仅保留记录中每个键的最新值。因此，结果中重复的“id”键会自动合并。

Postgres 9.5 手册还建议：

注：||运算符连接顶层的元素 它的每个操作数。它不递归操作。例如，如果 两个操作数都是具有公共键字段名称的对象，其值 结果中的字段将只是右侧操作数的值。

Postgres 9.4

就是有点不太方便。我的想法是提取数组元素，然后提取所有键/值对，UNION两个结果聚合成一个新的jsonb每个 id 值的值并最终聚合到一个数组中。

SELECT json_agg(j) -- ::jsonb
FROM  (
   SELECT json_object_agg(key, value)::jsonb AS j
   FROM  (
      SELECT elem->>'id' AS id, x.*
      FROM  (
         SELECT '[
           {"id":1, "percent":12.50}, 
           {"id":2, "percent":75.00}, 
           {"id":3, "percent":12.50}]'::jsonb AS js
         ) t, jsonb_array_elements(t.js) elem, jsonb_each(elem) x
      UNION ALL  -- or UNION, see below
      SELECT elem->>'id' AS id, x.*
      FROM  (
         SELECT '[
           {"id": 1, "a": "text1a", "b": "text1b", "percent":12.50},
           {"id": 2, "a": "text2a", "b": "text2b", "percent":75.00},
           {"id": 3, "a": "text3a", "b": "text3b", "percent":12.50}]'::jsonb AS js
         ) t, jsonb_array_elements(t.js) elem, jsonb_each(elem) x
      ) t
   GROUP  BY id
   ) t;

演员阵容jsonb删除重复的键。或者你可以使用UNION折叠重复项（例如，如果您想要json结果）。测试哪个对于您的情况更快。

Related:

• 如何将json数组转为postgres数组？
• 在查询中合并串联 JSON(B) 列