N1QL (CB6.6) ANSI merge

Question

I am trying to fetch customer id from ":ao.mos:pro:%" and update corresponding doc ":pd:pro:%", we have thousands of ":ao.mos:pro:%" docs and corresponding ":pd:pro:%" for each mo:pro doc.

I am using below query and it works but it updates only one doc it can find indexes.

MERGE INTO `bucket1` AS d
USING `bucket1` AS p
ON d.icc = p.icc AND META(d).id LIKE ':pd:pro:%' AND META(p).id LIKE ':ao.mos:pro:%'
WHEN MATCHED THEN
UPDATE SET d.customerId = p.customerId;

Any suggestions on how to get this working for all matching docs in the bucket.

It will update all matching documents (NOTE: It will not mutate same document again ). Check your condition d.icc = p.icc. Also https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/ — vsr, Aug 03 '21 at 17:24
I verified it multiple times, only one mutation count and apparently, its always the same doc that gets updated..Only when I change Like statement with use keys than I can force the change to different id — Bimal Mirchandani, Aug 03 '21 at 19:02

vsr · Answer 1 · 2021-08-04T03:58:56.397

The following works.

 CREATE INDEX ix11 ON `default` (icc, customerId) WHERE META().id LIKE ":ao.mos:pro:%";
CREATE INDEX ix12 ON `default` (icc, customerId) WHERE META().id LIKE ":pd:pro:%";
INSERT INTO default VALUES(":ao.mos:pro:1", {"icc":1, "customerId":100});
INSERT INTO default VALUES(":ao.mos:pro:2", {"icc":2, "customerId":101});
INSERT INTO default VALUES(":pd:pro:1", {"icc":1});
INSERT INTO default VALUES(":pd:pro:2", {"icc":1});
INSERT INTO default VALUES(":pd:pro:3", {"icc":2});

MERGE INTO `default` AS m
USING (SELECT p1.icc, p1.customerId
       FROM `default` AS p1
       WHERE META(p1).id LIKE ':ao.mos:pro:%' AND p1.icc IS NOT NULL) AS p
ON m.icc = p.icc AND META(m).id LIKE ':pd:pro:%'
WHEN MATCHED THEN
UPDATE SET m.customerId = p.customerId;

MERGE INTO `default` AS m
USING default AS p
ON m.icc = p.icc AND META(m).id LIKE ':pd:pro:%'  AND META(p).id LIKE ':ao.mos:pro:%'
WHEN MATCHED THEN
UPDATE SET m.customerId = p.customerId;

Are you having following issue:

First source row matches 2 target rows. Second source matches same target row (by changing icc value to 1 of document key ":ao.mos:pro:2") then returns error (5320 Multiple UPDATE/DELETE of the same document (document key 'xxx') in a MERGE statement) (as with partial updated rows, You need transactions don't want update anything) as you can't update same row again. To resolve that you need to change your ON clause.

score 0 · Answer 2 · answered Aug 04 '21 at 16:15

If it is a one off operation you can use Eventing Service and create a function with a bucket alias of "src_bkt" to map to bucket1 in r+w mode and the deployment feed boundary set to "From Everything" can easily do 100's of millions of items quickly without indexes.

function OnUpdate(doc,meta) {
    // Filter out all non-interesting items
    if (!meta.id.startsWith(":pd:pro:")) return;

    // No need to do anything already updated.
    if (doc.customerId)return;

    var myint = meta.id.substring(8);
    var otherkey = ":ao.mos:pro:" + myint;
    var otherdoc = src_bkt[otherkey];
    if (otherdoc) {
        if (doc.icc === otherdoc.icc ) {
            // update the field as we have a match and it is missing.
            doc.customerId = otherdoc.customerId;
            src_bkt[meta.id] = doc;
        }
    }
}

So now you just insert some test documents using the QWB

INSERT INTO bucket1 VALUES(":ao.mos:pro:1", {"icc":1, "customerId":100});
INSERT INTO bucket1 VALUES(":ao.mos:pro:2", {"icc":2, "customerId":101});
INSERT INTO bucket1 VALUES(":pd:pro:1", {"icc":1});
INSERT INTO bucket1 VALUES(":pd:pro:2", {"icc":1});
INSERT INTO bucket1 VALUES(":pd:pro:3", {"icc":2});

Then inspect the documents you should have

:ao.mos:pro:1{"customerId":100,"icc":1}
:ao.mos:pro:2{"customerId":101,"icc":2}
:pd:pro:1{"icc":1}
:pd:pro:2{"icc":1}
:pd:pro:3{"icc":2}

Deploy the Eventing function and look at your documents again.

:ao.mos:pro:1{"customerId":100,"icc":1}
:ao.mos:pro:2{"customerId":101,"icc":2}
:pd:pro:1{"icc":1,"customerId":100}
:pd:pro:2{"icc":1}
:pd:pro:3{"icc":2}

As expected only one doc with matching keys and matching icc properties updated.

For more performance (doing millions) you can up the workers in the functions settings to 2x the physical cores.

N1QL (CB6.6) ANSI merge

2 Answers2