Query to merge documents within the same bucket

SQL++
1

Hi All,

Please help me with the N1QL query to achieve merged document.

I have below two documents in the same bucket, where value of cpm_id is the document key

[
{
“cdb_id”: “1001”,
“cpm_id”: “profile_1001”, //KEY value
“phone”:“1234”,
“email":"abc@fd.com”
},
{
“cdb_id”: “1001”,
“gala_id”:“9100”,
“cpm_id”: “profile_9100”, //KEY value
“phone”:“2006”,
“email":"xyz@fd.com”
}
}

I want to update phone and email from 1st document to 2nd document, where cdb_id value is common and field gala_id does not contain in 1st document.

Below is the expected output for above example: -

[
{
“cdb_id”: “1001”,
“cpm_id”: “profile_1001”, //KEY value
“phone”:“1234”,
“email":"abc@fd.com”
},
{
“cdb_id”: “1001”,
“gala_id”:“9100”,
“cpm_id”: “profile_9100”, //KEY value
“phone”:“1234”,
“email":"abc@fd.com”
}
}

Thank you,
Regards,
Jayant

2

Requires 6.5 
MERGE default AS t USING  (SELECT d.cdb_id, d.phone FROM default AS d WHERE d.gala_id IS MISSNG) AS s
ON s.cbd_id = t.cbd_id AND t.gala_id  IS NOT MISSING 
WHEN MATCHED  THEN t.phone = s.phone;
3

Thank you so much. This works. As we have huge number of records (Around 10 million), guess putting a limit will have a control over the query execution.

4 
CREATE INDEX ix1  ON default(META().id, cbd_id, phone) WHERE  gala_id IS MISSNG;
CREATE INDEX ix2  ON default( cbd_id, phone) WHERE  gala_id IS NOT MISSNG;

MERGE default AS t USING  (SELECT d.cdb_id, d.phone, META(d).id FROM default AS d WHERE d.gala_id IS MISSNG AND META(d).id > "") AS s
ON s.cbd_id = t.cbd_id AND t.gala_id  IS NOT MISSING AND t.phone != s.phone
WHEN MATCHED  THEN t.phone = s.phone  LIMIT 10000 RETURNING s.id;

Change condition META(d).id > “” to previous merge returned id