MERGE INSERT does not Insert

query
n1ql

#1

Hi,
I have several documents for each document I have a additional document with tracking informations.

I now try to create the tracking information document if it not exists. Else I will update it and add a tracking status to a property array.

Here a simple test Query which not work. It does not create the new document.
I became a success status but count. 0. The tracking document “track_foreignDocId” does not exists.

I will not write the tracking information in the main document because the other programm logic.

All the documents are in the same Bucket.

`MERGE INTO test o
USING (SELECT meta(t).id AS id FROM test t WHERE t._type="tracking") AS m ON KEY "track_foreignDocId"
WHEN MATCHED THEN
UPDATE SET o.status = "ein Test"
WHEN NOT MATCHED THEN INSERT {"status":"ONE TEST INSERT"}`

How can I create a document if it not exists and else do a update query with one n1ql query?

Couchbase CE 5.1

Thank you very much.


#2

Your ON KEY is constant ON KEY “track_foreignDocId” this makes only once create if not present.
Make ON KEY from expression from m.
When expresession from m present as document key it updates else create new one.

Example:

MERGE INTO orders USING (SELECT "se"||id AS id, (SELECT RAW SUM(orderlines.price) FROM orders.orderlines)[0] AS total FROM orders) o ON KEY o.id WHEN NOT MATCHED THEN INSERT {o.id, o.total} RETURNING *;