Update existing documents of a bucket A by a select - from bucket B document

Hello @vsr1 / Guys

I have 1000+ existing documents in bucket A. I need to update tenantId and clientId for all the existing documents where these two fields are missing.
We need to update for only documents where these 2 fields are MISSING.

I have bucket B with the following data
{
“tenantId”: “scom”,
“clients”: [
{
“name”: “homepage”,
“clientId”: “04e77def-a617-4205-8cda-075a29870fb4”
}
],
}

We need to get data from bucket B and then update bucket A documents.

This below query is only for tenantId and I am not checking missing condition.
UPDATE bucket a
SET a.tenantId = FIRST v.tenantId
FOR v IN (
SELECT tenantId
FROM bucket b
WHERE META().id=“tenant:scom”) END
WHERE a.type=“pagedef” RETURNING *

What would be the best/correct way to do these changes?

The relation between bucket b , bucket a is not clear. Do you want Update only when both missing or one missing. what is condition to update clientId (array sizes might different, different order) You should explore ANSI merge ANSI JOIN Enhancements and ANSI MERGE | The Couchbase Blog

Some thing in the following lines

MERGE INTO bucket AS m  
USING (SELECT  b.tenantId, b.clients, META(b).id  FROM bucket AS b WHERE .....) AS s
ON s.id = META(m).id   
WHEN MATCHED THEN UPDATE   SET m.teantId = s.tenatId, mc.clientId =  (FIRST sc.clientId FOR sc IN s.clients WHEN sc.xxxx = mc.xxxx END)  FOR mc IN m.clients WHEN .... END  WHERE  .................;

Thank you @vsr1 !
Bucket B and bucket A have no relation. Bucket B just has these ids.
Update only when both are MISSING
ClientId will have size as 1. only 1 client id.

Still not clear. If no relation, how do u update 1000+ documents. All update same Id.
can you post the sample document of bucket b with document key value

All old documents in Bucket A will get same tenantId and clientId.
Some documents that already have those 2 fields tenantId & clientId will not be updated. So checking for MISSING might be needed.

Bucket B have this below document with document Id

" tenant:scom"


{
“tenantName”: “SCOM”,
“tenantId”: “scom”,
“clients”: [
{
“name”: “sampage”,
“clientId”: “04e77def-a617-4205-8cda-075a29870fb4”
“clientToken”: “someToken”
}
],
“type”: “tenant”
}

still not clear . The problem is if bucket A has 1000 documents , bucket b has 1000 documents, From which document you pick from bucket b to update bucket a . Is the document keys in bucket A and buket B are same

Bucket B only have 1 document with document.id = “tenant:scom”
{ “tenantName”: “SCOM”, “tenantId”: “scom”, “clients”: [ { “name”: “sampage”, “clientId”: “04e77def-a617-4205-8cda-075a29870fb4” “clientToken”: “someToken” } ], “type”: “tenant” }

Bucket A is having 1000+ documents. Update Bucket A with tenantId and clientId from Bucket B document with id=“tenant.scom”. We need to get clientId and tenantId from Bucket B document and update bucket A with missing fields (tenantId and clientId) on 1000+ documents.
@vsr1 sorry for confusion. Hope this will be clear.

UPDATE bucket a
SET a.tenantId = "newtenantId",
    cl.clientId = "newclientId" FOR cl IN a.clients END
WHERE a.type = "pagedef" AND a.tenantId IS MISSING AND ANY v IN a.clients SATISIFIES v.clientId IS MISSING END RETURNING *;

UPDATE bucket a
SET a.tenantId = (SELECT RAW b.tenantId FROM bucket AS b USE KEYS "tenant:scom")[0],
    cl.clientId = (FIRST v.clientId FOR v IN (SELECT RAW c.clients FROM bucket AS c USE KEYS "tenant:scom")[0] END) FOR cl IN a.clients END
WHERE a.type = "pagedef" AND a.tenantId IS MISSING AND ANY v IN a.clients SATISIFIES v.clientId IS MISSING END RETURNING *;

I recommend using First query as you have one document on bucket b one client array. So get values and directly use query avoid repeated Fetch of document.