MERGE INTO with USE KEYS

what is wrong with this query?

MERGE INTO bucket AS t USE KEYS ‘47d59a73-f75f-465d-b849-58bb8cac6df1’
WHEN MATCHED THEN UPDATE
SET t.“newImages”.“images” = 1
WHEN NOT MATCHED THEN INSERT
{
“newImages.images”:1
}

You can use:

MERGE INTO `bucket`                                                                                                                  
USING "47d59a73-f75f-465d-b849-58bb8cac6df1" id
ON KEY id
WHEN MATCHED THEN
  UPDATE SET `bucket`.`newImages.images` = 1
WHEN NOT MATCHED THEN
  INSERT {"newImages.images":1}
;

(Noting that the field name is of course “newImages.images” rather than there being a field “newImages” with a contained field called “images”. (Of course change the quoting if this is what you’re actually after.))

Presumably this example is only for demonstration as if the document is to have only the one field and/or all values will be the same regardless of if it was inserted or updated, then an UPSERT statement would be preferable and simpler.

As per the syntax and as shown in the examples you must provide the target keyspace (not target document), followed by the keyword USING followed by the source and predicates. Since you’re looking for the key to always be produced by the source, you should use a constant expression as shown in my example. (i.e. if your source was say USING bucket USE KEYS "47d59a73-f75f-465d-b849-58bb8cac6df1" then it would only provide input for the MERGE operation if the key already existed in ‘bucket’ - this might be the correct action if the source bucket and the target bucket aren’t the same but here they are.)

HTH.

As an aside, you could also write it as:

MERGE INTO `bucket` USING "" t                                                                                                       
ON KEY "47d59a73-f75f-465d-b849-58bb8cac6df1"
WHEN MATCHED THEN
  UPDATE SET `bucket`.`newImages.images` = 1
WHEN NOT MATCHED THEN
  INSERT {"newImages.images":1}
;

to achieve the same thing - a single item always produced by the source with the single key in question being used to match against the target. Of course for dealing with multiple keys in a single statement the first form is needed since the source can readily be an array of keys:

MERGE INTO default
USING ["47d59a73-f75f-465d-b849-58bb8cac6df1","another-id"] id                                                                                                                  
ON KEY id
WHEN MATCHED THEN
  UPDATE SET default.`newImages.images` = 1
WHEN NOT MATCHED THEN
  INSERT {"newImages.images":1}
;
2 Likes

thanks for all the details, they are very useful.

I was particularly blocked on USING "47d59a73-f75f-465d-b849-58bb8cac6df1" id ON KEY id

I could not find any example on the Couchbase documentation

Example 5 here:

is the basis for this. All I did was eliminate the example processing on the key value, and simplify the constant where only a single key value is needed.

I run into another problem

This is the query

MERGE INTO `websockets_meta` AS websocket USING "27599dfe-6d36-4556-948b-a2ec4ed79c85" id  ON KEY id 
            WHEN MATCHED THEN UPDATE
                SET websocket.newImages.images = ARRAY_APPEND(websocket.newImages.images,[{"imageSrc":"url"}])
            WHEN NOT MATCHED THEN INSERT 
            ( KEY "27599dfe-6d36-4556-948b-a2ec4ed79c85",
              VALUE {
               "newImages.images":[{"imageSrc":"url2"}] 
      
              },  OPTIONS {"expiration": 120}
            ) ;

If I used it like this I am getting an error: MERGE with ON KEY clause cannot have document key specification in INSERT action

if I delete the KEY (and VALUE ) on the insert statement I cannot use the OPTIONS and getting this error: syntax error - line 8, column 19, near ' },', at: OPTIONS (reserved word)

is there a workaround?

To use OPTIONS you must use an ANSI MERGE not a LOOKUP MERGE, in the note here: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/merge.html#lookup-merge-insert

The ANSI MERGE doesn’t use the ON KEY clause, rather just the ON clause as per example 1.

So try:

MERGE INTO `websockets_meta` AS websocket 
USING "27599dfe-6d36-4556-948b-a2ec4ed79c85" id  ON meta(websocket).id = id 
WHEN MATCHED THEN UPDATE
    SET websocket.`newImages.images` = ARRAY_APPEND(websocket.`newImages.images`,{"imageSrc":"url"})
WHEN NOT MATCHED THEN INSERT 
(
  KEY id
  ,VALUE {"newImages.images":[{"imageSrc":"url2"}]}
  ,OPTIONS {"expiration": 120}
)
;
  • Here I’ve taken your definition from the insert making the field “newImages.images” (as opposed to “newImages”.“images”) and assumed you wanted to append simply another value rather than a nested array.

If you really want the document to have a field “newImages” and that to have a field “images” then:

MERGE INTO `websockets_meta` AS websocket 
USING "27599dfe-6d36-4556-948b-a2ec4ed79c85" id  ON meta(websocket).id = id 
WHEN MATCHED THEN UPDATE
    SET websocket.newImages.images = ARRAY_APPEND(websocket.newImages.images,{"imageSrc":"url"})
WHEN NOT MATCHED THEN INSERT 
( 
   KEY id
  ,VALUE
   {
       "newImages":
       {
           "images": [{"imageSrc":"url2"}]
       }
   }
  ,OPTIONS {"expiration": 120}
) ;

HTH.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.