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

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

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来完成这项工作？

假设数据类型jsonb和您想要合并每个共享相同"id"值的JSON数组的记录.

Postgres 9.5

使用新的连接运算符||使jsonb值更简单:

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);

在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结果).测试哪种情况更快.

有关:

如何将json数组转换为postgres数组？

在查询中合并连接JSON(B)列