MERGE statement in N1QL

SQL++
1

I am trying to use the MERGE statement in N1QL to be able to insert a document if it does not already exist or update its content if it does exist…

I am trying to replace doing this in my application code by doing a bucket get, followed by bucket insert or bucket replace. And when calling bucket replace I check the CAS and retry if I had an issue with CAS as my updated document content depends on its content at time of reading (some kind of counter inside). I am hoping by moving this in N1QL I can garantie atomicity of the action so do not have to deal with CAS mismatch error (is this really the case?)

My MERGE statement looks like that:
MERGE INTO myBucket USING (SELECT key FROM myBucket WHERE key = $key) AS d ON KEY $key
WHEN MATCHED THEN
UPDATE SET
d.counter = d.counter + 1,
d.value = $value
WHEN NOT MATCHED THEN
INSERT { “key”: $key, “value”: $value, “counter”: 0 }";

Both parseSuccess and finalSuccess are true, but when run the first time I do not have any document inserted in the bucket. I guess my statement is incorrect?

Could someone help me correct the statement… Or maybe there is a way to do the same using UPSERT statement instead?

As an aside, I also need to unset the TTL of the document when updating it. Is there a way to set the expiry? Can we do META(myBucket).expiry = 0 in the UPDATE section?

Many thanks.

1 Like
2

A few points:

  • Not possible to set TTL via N1QL
  • You should first validate the MERGE statement using cbq shell, and then move the statement to your application

MERGE INTO myBucket
USING (SELECT NULL) AS d ON KEY $key
WHEN MATCHED THEN UPDATE SET …
WHEN NOT MATCHED THEN INSERT …
;

1 Like
3

I managed to do the update part but not the insert for now… But I did not try using (SELECT NULL).

But as I need to (re)set the TTL in effect I think it is better that I stick to doing this MERGE directly using the get/insert or replace of the java driver.

Quick question related to CAS mismatch… Using the merge in N1QL, is it an atomic operation or a CAS mismatch or document not found or document exist exception can still happen… In effect the query handler does the same as I am doing in java driver and therefore we need to retry the full query again?

4

The query engine does the same thing as your application would do – read, then CAS.

5

Many thanks.

Using the SELECT NULL I managed to get the query to work for insert / update.