Remove Multiple items from an embedded array

SQL++
,
1

Hello,

I am attempting to remove multiple items from an array embedded in a document.

 {
   "id": "fooId",
   "myArray": ["a","b","c"]
 }

This works if I need to remove a single item"

UPDATE myBucket SET myArray = ARRAY_REMOVE(myArray,"a") where meta(myBucket).id="fooId" RETURNING ARRAY_COUNT(myArray)

However, I’d like to remove multiple items doing something like this ARRAY_REMOVE(myArray,"a","b"). I’ve also tried ARRAY_REMOVE(myArray,["a","b"]) but neither work. The docs says that ARRAY_REMOVE:

returns a new array with all occurrences of the specified value(s) removed. It requires a minimum of two arguments…

This language implies that multiple items can be removed with a single command, though I am unable to get it to work.

Thanks,

K

2

ARRAY_REMOVE(myArray, “a”,“b”)

UPDATE myBucket SET myArray = ARRAY_REMOVE(myArray,"a","b") WHERE meta(myBucket).id="fooId" RETURNING ARRAY_COUNT(myArray);

3

Hello,

That was the first thing I tried before posting here. I get this as an error message:

[
  {
    "code": 3000,
    "msg": "Wrong number of arguments to function ARRAY_REMOVE. - at )",
    "query_from_user": "UPDATE `myBucket` SET myArray = ARRAY_REMOVE(myArray,\"a\",\"b\") where meta(myBucket).id=\"fooId\" RETURNING ARRAY_COUNT(myArray)"
  }
]

We are using Version: 4.5.0-2601 Community Edition (build-2601).

Thanks.

4

The change is in 4.6.0 as part of https://issues.couchbase.com/browse/MB-19782.

Pre 4.6.0 you can try the following recursive operation.

UPDATE myBucket 
SET myArray = ARRAY_REMOVE(ARRAY_REMOVE(myArray,"a"),"b") 
WHERE meta(myBucket).id="fooId" RETURNING ARRAY_COUNT(myArray);

OR

UPDATE myBucket 
SET myArray = ARRAY v FOR v IN myArray WHEN v  NOT IN ["a","b"] END 
WHERE meta(myBucket).id="fooId" RETURNING ARRAY_COUNT(myArray);
5

That worked. Thank you for the quick response.

6

One more question. Is there a limit to the number of items I can put in the NOT IN array?

7

There is no LIMIT on number of elements.