No matter what query I run I have not been able to remove the null value. I can remove any item in the array that has a value but have not found a way to remove only the null value.
Also when I ran the following query:
UPDATE customers
SET devices = ARRAY v FOR v IN devices WHEN v != ‘af0ba93b-cbc9-49a3-98da-be3de4d62d97’ END
WHERE dealershipID = ‘15212’
AND entityKey = ‘6401’
AND ‘af0ba93b-cbc9-49a3-98da-be3de4d62d97’ WITHIN customers.devices
It removed the array element with the value and the null value, which is good. But I also need a way to remove any null value that may have been pushed into the array.
Removes NULL
SELECT ARRAY v FOR v IN [1,2,3,null,4,null,5] WHEN v IS NOT NULL END;
SELECT ARRAY v FOR v IN [1,2,3,null,4,null,5] WHEN v IS VALUED END;
Remove NULL AND 3
SELECT ARRAY v FOR v IN [1,2,3,null,4,null,5] WHEN v IS NOT NULL AND v !=3 END;
UPDATE customers
SET devices = ARRAY v FOR v IN devices WHEN v IS VALUED END
WHERE dealershipID = "15212"
AND entityKey = "6401"
AND "af0ba93b-cbc9-49a3-98da-be3de4d62d97" IN devices;
UPDATE customers
SET devices = ARRAY v FOR v IN devices WHEN v IS VALUED END
WHERE dealershipID = "15212"
AND entityKey = "6401"
AND ANY v IN devices SATISFIES v = "af0ba93b-cbc9-49a3-98da-be3de4d62d97" END;
The following query and index removes ALL null values from the devices.
CREATE INDEX ix1 ON customer(DISTINCT ARRAY v FOR v IN devices END, dealershipID, entityKey);
UPDATE customers
SET devices = ARRAY v FOR v IN devices WHEN v IS VALUED END
WHERE dealershipID = "15212"
AND entityKey = "6401"
AND ANY v IN devices SATISFIES v IS NULL END;
Looking at your examples will help fix an issue I hadn’t run into yet, but can see where my query would have given me some unexpected results in the future.