Use array distinct by a specific property

SQL++
1

So I have this documents on my db:

[
	{
		"id": "c5b69fb1853e361417816eae057d03ff",
		"name": "Product 00",
		"categories": [
			{
				"name": "cat 00",
				"id": "cat00"
			},
			{
				"name": "cat 01",
				"id": "cat01"
			},
			{
				"name": "cat 02",
				"id": "cat02"
			}
		]
	},
	{
		"id": "c5b69fb1853e361417816eae057d03xx",
		"name": "Product 01",
		"categories": [
			{
				"name": "cat 00",
				"id": "cat00"
			},
			{
				"name": "cat 03",
				"id": "cat03"
			},
			{
				"name": "cat 02",
				"id": "cat02"
			}
		]
	},
	{
		"id": "c5b69fb1853e361417816eae057d03zz",
		"name": "Product 02",
		"categories": [
			{
				"name": "cat 00",
				"id": "cat00"
			},
			{
				"name": "cat 03",
				"id": "cat03"
			},
			{
				"name": "cat 02",
				"id": "cat02"
			},
			{
				"name": "cat 04",
				"id": "cat04"
			}
		]
	},
]

The desired result is to add a new category from an array if it does not have it yet, the result would be:

[
	{
		"id": "c5b69fb1853e361417816eae057d03ff",
		"name": "Product 00",
		"categories": [
			{
				"name": "cat 00",
				"id": "cat00"
			},
			{
				"name": "cat 01",
				"id": "cat01"
			},
			{
				"name": "cat 02",
				"id": "cat02"
			},
			{
				"name": "cat 04",
				"id": "cat04"
			}
		]
	},
	{
		"id": "c5b69fb1853e361417816eae057d03xx",
		"name": "Product 01",
		"categories": [
			{
				"name": "cat 00",
				"id": "cat00"
			},
			{
				"name": "cat 03",
				"id": "cat03"
			},
			{
				"name": "cat 02",
				"id": "cat02"
			},
			{
				"name": "cat 04",
				"id": "cat04"
			}
		]
	},
	{
		"id": "c5b69fb1853e361417816eae057d03zz",
		"name": "Product 02",
		"categories": [
			{
				"name": "cat 00",
				"id": "cat00"
			},
			{
				"name": "cat 03",
				"id": "cat03"
			},
			{
				"name": "cat 02",
				"id": "cat02"
			},
			{
				"name": "cat 04",
				"id": "cat04"
			}
		]
	},
]

my query would be like this:

UPDATE `HUC_Erick` p USE KEYS ['c5b69fb1853e361417816eae057d03ff', 'c5b69fb1853e361417816eae057d03xx', 'c5b69fb1853e361417816eae057d03zz']
SET p.categories = (
  SELECT DISTINCT RAW n
  FROM ARRAY_FLATTEN(ARRAY_APPEND(
  p.categories, [{"name": "cat 04", "id": "cat04"}]), 1) AS n

It seems working fine by I wanna ensure that the distinct operation should be done comparing only my category id, on this example the “cat04”; how can I ensure that the distinct take only this parameter in count; also I gues this will be more performant.

2 
UPDATE `HUC_Erick` p USE KEYS ['c5b69fb1853e361417816eae057d03ff', 'c5b69fb1853e361417816eae057d03xx', 'c5b69fb1853e361417816eae057d03zz']
SET p.categories = ARRAY_APPEND( p.categories , {"name": "cat 04", "id": "cat04"})
WHERE ANY v IN p.categories SATISFIES v.id != "cat04" END;
3

Sorry, I wasn’t to clear, but I need to use:

UPDATE `HUC_Erick` p USE KEYS ['c5b69fb1853e361417816eae057d03ff', 'c5b69fb1853e361417816eae057d03xx', 'c5b69fb1853e361417816eae057d03zz']
SET p.categories = (
  SELECT DISTINCT RAW n
  FROM ARRAY_FLATTEN(ARRAY_APPEND(
  p.categories, [{"name": "cat 04", "id": "cat04"},{"name": "cat 05", "id": "cat05"} ]), 1) AS n

Due there could me more than one category to be apended… that’s the main reason that I need to use arrays distinct, flaten and distinct methods

4 
UPDATE `HUC_Erick` p USE KEYS ['c5b69fb1853e361417816eae057d03ff', 'c5b69fb1853e361417816eae057d03xx', 'c5b69fb1853e361417816eae057d03zz']
SET p.categories = ARRAY_DISTINCT(ARRAY_APPEND(
  p.categories, [{"name": "cat 04", "id": "cat04"},{"name": "cat 05", "id": "cat05"} ]));
5

I see, so there’s no way to only apply the DISTINCT by a certain field right? due the objects to append could have another field that I do not care like timeStamp:

UPDATE `HUC_Erick` p USE KEYS ['c5b69fb1853e361417816eae057d03ff', 'c5b69fb1853e361417816eae057d03xx', 'c5b69fb1853e361417816eae057d03zz']
SET p.categories = ARRAY_DISTINCT(ARRAY_APPEND(
  p.categories, [{"name": "cat 04", "id": "cat04", "timeStamp": "xxx"},{"name": "cat 05", "id": "cat05", "timeStamp": "ZZZ"} ]));
6

Re construct ARRAY based on the document and append

UPDATE `HUC_Erick` p USE KEYS ['c5b69fb1853e361417816eae057d03ff', 'c5b69fb1853e361417816eae057d03xx', 'c5b69fb1853e361417816eae057d03zz']
SET p.categories = ARRAY_CONCAT(p.categories,  ARRAY v FOR v IN [{"name": "cat 04", "id": "cat04"},{"name": "cat 05", "id": "cat05"} ] WHEN v.id NOT IN p.categories[*].id END);