How can I merge records inside two JSON arrays?

Question

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

How can this be done with SQL in Postgres 9.4?

Answer 1

For any single jsonb element this use of the concat || operator works well for me with strip_nulls and another trick to cast the result back to jsonb (not an array).

select jsonb_array_elements(jsonb_strip_nulls(jsonb_agg(
    '{
        "a" : "unchanged value",
        "b" : "old value",
        "d" : "delete me"
    }'::jsonb
    || -- The concat operator works as merge on jsonb, the right operand takes precedence
    -- NOTE: it only works one JSON level deep
    '{
        "b" : "NEW value",
        "c" : "NEW field",
        "d" : null
    }'::jsonb
)));

This gives the result

 {"a": "unchanged value", "b": "NEW value", "c": "NEW field"}

which is properly typed jsonb

Answer 2

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

Postgres 9.5

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

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

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.

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

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;

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.

Related:

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