UPDATE:
While testing more cases, I’ve come up with a solution that covers my needs better. Instead of taking distinct values, I used a FULL JOIN between the two data sets:
SELECT json_agg(data) FROM (
SELECT coalesce(new.id, old.id) AS id, coalesce(new.value, old.value) AS value
FROM jsonb_to_recordset('[{"id": 1, "value": "A"}, {"id": 2, "value": "B"}, {"id": 3, "value": "C"}, {"id": 6, "value": "6"}]') AS old(id int, value text)
FULL JOIN jsonb_to_recordset('[{"id": 1, "value": "NEW A"}, {"id": 2, "value": "NEW B"}, {"id": 5, "value": "5"}]') AS new(id int, value text)
ON old.id = new.id
) data
Given a JSON column which holds an array of objects, each object having “id” and “value” properties, I wanted to update the array with a new one, by merging the values by the “id” property.
If same id is present in an object of both arrays, the new one will be used.
Old:
[
{"id": 2, "value": "B"},
{"id": 1, "value": "A"}
]
New:
[
{"id": 1, "value": "X"},
{"id": 3, "value": "C"},
{"id": 4, "value": "C"}
]
Result:
[
{"id":1, "value":"X"},
{"id":2, "value":"B"},
{"id":3, "value":"C"},
{"id":4, "value":"C"}
]
And I wanted to do this in one database roundtrip, not take the data in back end, merge it, and send it back to the database.
First, I transformed the array objects into records and prioritized the new array by giving its values a position; and I put both sets together:
SELECT 1 AS position, * FROM json_to_recordset('[{"id": 2, "value": "B"}, {"id": 1, "value": "A"}]') AS src(id int, value text) -- old
UNION ALL
SELECT 0 AS position, * FROM json_to_recordset('[{"id": 1, "value": "X"}, {"id": 3, "value": "C"}, {"id": 4, "value": "C"}]') AS dst(id int, value text) -- new
Then I extracted only the id and the value, the position being used only to sort data:
SELECT id, value FROM (
SELECT 1 AS position, * FROM json_to_recordset('[{"id": 2, "value": "B"}, {"id": 1, "value": "A"}]') AS src(id int, value text) -- old
UNION ALL
SELECT 0 AS position, * FROM json_to_recordset('[{"id": 1, "value": "X"}, {"id": 3, "value": "C"}, {"id": 4, "value": "C"}]') AS dst(id int, value text) -- new
) AS ordered ORDER BY position
Next step, I took the distinct rows by id:
SELECT DISTINCT ON (id) id, value FROM (
SELECT id, value FROM (
SELECT 1 AS position, * FROM json_to_recordset('[{"id": 2, "value": "B"}, {"id": 1, "value": "A"}]') AS src(id int, value text) -- old
UNION ALL
SELECT 0 AS position, * FROM json_to_recordset('[{"id": 1, "value": "X"}, {"id": 3, "value": "C"}, {"id": 4, "value": "C"}]') AS dst(id int, value text) -- new
) AS ordered ORDER BY position
) data
And finally, the result needs to be converted to JSON:
SELECT json_agg(uniqueid) FROM (
SELECT DISTINCT ON (id) id, value FROM (
SELECT id, value FROM (
SELECT 1 AS position, * FROM json_to_recordset('[{"id": 2, "value": "B"}, {"id": 1, "value": "A"}]') AS src(id int, value text) -- old
UNION ALL
SELECT 0 AS position, * FROM json_to_recordset('[{"id": 1, "value": "X"}, {"id": 3, "value": "C"}, {"id": 4, "value": "C"}]') AS dst(id int, value text) -- new
) AS ordered ORDER BY position
) data
) uniqueid