Trying to merge from one bucket to another, where the document id has a prefix

SQL++
1

Couchbase 4.5.
I have two buckets, order and history. I am trying to create a new field in order that matches something in history. However history references an order via a uuid while the actual document has order::uuid. When i try to perform my MERGE i get error:5030, invalid merge key of <nil>
for reference paidDate doesn’t exist in the order document yet. So I might need to change this into an insert. I am just trying to get it to run

MERGE INTO order as o USING history AS h ON KEY 'order::' || history.orderId
WHEN MATCHED THEN UPDATE SET o.paidDate = h.date

Is it possible for me to be able to run a query like this or am i looking into using something different.

2

The source aliased to h, So u need to use alias name every where. Assume orderId is always string.

MERGE INTO order as o USING history AS h ON KEY 'order::' || h.orderId
WHEN MATCHED THEN UPDATE SET o.paidDate = h.date
3

Thanks for the tip, still getting the same error so it might be the way we are setting up some of these things. our history bucket as a history::uuid piece as well wondering if that is interfering for some reason.

4

It looks like h.orderId is not a string or MISSING in some documents.

MERGE INTO order as o USING 
 (SELECT 'order::' || h.orderId  AS id, h.date FROM history AS h WHERE   IS_STRING(h.orderId)   ) AS h 
ON KEY  h.id 
WHEN MATCHED THEN UPDATE SET o.paidDate = h.date
5

Thank you, i was able to get it to work with that revised query!