N1QL update with CAS

java
n1ql
query

#1

Hello,

We have documents in couchbase (4.5.1-2844) with the following structure:

{
  "foo":"bar",
  "subscribers": [
    "string1",
    "string2",
    "string_to_remove"
  ]
}

We are currently using this N1QL to remove elements from the subscribers array:

UPDATE my bucket
USE KEYS ${"someId"}
SET subscribers = ARRAY v FOR v IN subscribers WHEN v NOT IN ["string_to_remove"] END
RETURNING ARRAY_COUNT(subscribers)

However, we are periodically seeing this error:

{"msg":"DML Error, possible causes include CAS mismatch or concurrent modificationFailed to perform update - cause: MCResponse status=KEY_EEXISTS, opcode=SET, opaque=0, msg: Data exists for key","code":12009}

I’ve looked at trying to use the sub-document api to perform the update but it requires me to either use an index or use dictionary kv pair in the list. The latter will require me to change my document structure and the former doesn’t seem reliable.
The other alternative that I’ve looked in is to pull back the entire document, modify the array locally and perform an update using the CAS value. However, I’d rather not pull back a potentially large json object and parse it just to do a simple modification.

Is there a way to handle this using N1QL? From what I’ve seen on this post it looks like it is not possible but that was from 2016.

Thanks,

-K


#2

If you are using latest CB you should be able to use subdoc API (get subscribers and modify and update subscribers). Not in 4.5.1

with N1QL only option will be retry again on error until succeeded. FYI: N1QL also gets whole document parse and updates.


#3

Hi @vsr1,

How would this work using the subdocument api? It may be worth us upgrading.

Also, I am using the retryBuilder to retry the operation but. I don’t see a way to pass in the CAS value. Should I just increase the number of times it retries?

b.query(query).retryWhen(RetryBuilder.anyOf(classOf[QueryExecutionException]).max(3).delay(Delay.exponential(TimeUnit.SECONDS), Schedulers.io).build()).toScala

#4

What subdoc api allows you get directly specific fields in documents and update them instead of whole document.

I am not expert in Clientside APIs cc @ingenthr.


#5

Hi @k_reid. Subdoc doesn’t give you ‘remove item from array if it matches this predicate’ semantics. You could change subscribers so it’s an object rather than an array, and then do a subdoc operation to remove “subscribers.string_to_remove”. But you’ve intimated that you’d prefer not to do that.

So, instead of fetching the whole document, why not do a subdoc get to fetch just the subscribers, and then a subdoc update to replace just subscribers? I haven’t tested this code, but hopefully it’s a good enough hint to get you started:

bucket.async()
  // Get just the subscribers field from "id"
  .lookupIn("id")
  .get("subscribers")
  .execute()
  .flatMap(doc => {
    // Remove the desired subscriber, and do a subdoc mutation to update subscribers
    val subscribers = doc.content(0).asInstanceOf[JsonArray]
    val newSubscribers = subscribers.toList.stream().filter(v => v != "string_to_remove").toArray
    bucket.async()
      .mutateIn("id")
      .replace("subscribers", newSubscribers)
      .withCas(doc.cas())
      .execute()
  })
  // Retry the whole thing on CAS fail, with exponential backoff, up to 20 times
  .retryWhen(RetryBuilder
    .anyOf(classOf[CASMismatchException])
      .delay(Delay.exponential(TimeUnit.MILLISECONDS, 500))
      .max(20)
      .build())