Remove null value from array


#1

I have a document that is similar to this:

{
“dateModified”: “2018-09-10 12:20:29”,
“dealershipID”: “15212”,
“department”: “Sales”,
“devices”: [
null,
“bf0ba93b-cbc9-49a3-98da-be3de4d62d97”,
“af0ba93b-cbc9-49a3-98da-be3de4d62d97”
],
“downloaded”: “2018-09-10 23:30:26”,
“employeeID”: “”,
“entityKey”: “6401”
}

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.


#2
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;

#3

Awesome! Thank you.

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.


#4

Create index did not work as posted. Another developer looked over my shoulder and said the ARRAY was missing.

CREATE INDEX ix1 ON customers(DISTINCT ARRAY v FOR v IN devices END, dealershipID, entityKey)