如何合并两个JSON数组中的记录？ [英] How can I merge records inside two JSON arrays?

问题描述

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

I have two Postgres SQL queries returning JSON arrays:

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

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

I want the result to be a union of both array unique elements:

[
  {"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做到这一点？

How can this be done with SQL in Postgres 9.4?

推荐答案

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

Assuming data type jsonb and that you want to merge records of each JSON array that share the same 'id' value.

使用新的对于 jsonb 值的连接运算符 || ：

makes it simpler with the new concatenate operator || 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);

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

The FULL [OUTER] JOIN makes sure you don't lose records without match in the other array.

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

The type jsonb has the convenient property to only keep the latest value for each key in the record. Hence, the duplicate 'id' key in the result is merged automatically.

Postgres 9.5手册也建议：

The Postgres 9.5 manual also advises:

注意： ||| 运算符将每个操作数位于
顶层的元素连接起来。它不会递归操作。例如，如果
两个操作数都是具有相同键字段名称的对象，那么结果中的字段
的值将恰好是右侧操作数的值。

Note: The || operator concatenates the elements at the top level of each of its operands. It does not operate recursively. For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.

Postgres 9.4

不太方便。我的想法是提取数组元素，然后提取所有键/值对， UNION 这两个结果，汇总成一个新的 jsonb 每个id值的值，最后汇总为一个数组。

Postgres 9.4

Is a bit less convenient. My idea would be to extract array elements, then extract all key/value pairs, UNION both results, aggregate into a single new jsonb values per id value and finally aggregate into a single array.

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 作为结果）。测试哪个更适合您的情况。

The cast to jsonb removes duplicate keys. Alternatively you could use UNION to fold duplicates (for instance if you want json as result). Test which is faster for your case.

相关：

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


• 合并查询中的JSON（B）列

• How to turn json array into postgres array?
• Merging Concatenating JSON(B) columns in query

这篇关于如何合并两个JSON数组中的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！