Merge JSON arrays by key in PostgreSQL

UPDATE:

While testing more cases, I’ve come up with a solution which better covers my needs. 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

Unmarshal JSON and XML into the same Go structure slice with proper data type

I’m consuming a REST service which gives various lists in both JSON and XML formats, something similar to these ones:

[  
   {  
      "id":803267,
      "name":"Paris, Ile-de-France, France",
      "region":"Ile-de-France",
      "country":"France",
      "is_day":1,
      "localtime":"2018-05-12 12:53"
   },
   {  
      "id":760995,
      "name":"Batignolles, Ile-de-France, France",
      "region":"Ile-de-France",
      "country":"France",
      "is_day":0,
      "localtime":"2018-05-12"
   }
]
<?xml version="1.0" encoding="UTF-8"?>
<root>
   <geo>
      <id>803267</id>
      <name>Paris, Ile-de-France, France</name>
      <region>Ile-de-France</region>
      <country>France</country>
      <is_day>1</is_day>
     <localtime>2018-05-12 12:53</localtime>
   </geo>
   <geo>
      <id>760995</id>
      <name>Batignolles, Ile-de-France, France</name>
      <region>Ile-de-France</region>
      <country>France</country>
      <is_day>0</is_day>
      <localtime>2018-05-12</localtime>
   </geo>
</root>

And I wanted to get them into a slice of this type of structures:

type Locations []Location

type Location struct {
   ID        int      `json:"id" xml:"id"`
   Name      string   `json:"name" xml:"name"`
   Region    string   `json:"region" xml:"region"`
   Country   string   `json:"country" xml:"country"`
   IsDay     int      `json:"is_day" xml:"is_day"`
   LocalTime string   `json:"localtime" xml:"localtime"`
}

It’s straight forward for JSON: Continue reading Unmarshal JSON and XML into the same Go structure slice with proper data type