Couchbase - update child in self referencing document (N1QL)

Question

I have a self referencing document in my couchbase 6.0.0 instance that can be an arbitrary number of levels deep.

{
  "branchEndDateTime": "2020-09-22 10:00 am",
  "branchEndX": 0,
  "branchId": "id-652c12fe-000e-4b42-a7e6-e4817d123456",
  "branchName": "Root",
  "branchStartDateTime": "1975-09-22 10:00 am",
  "branchStartX": 0,
  "children": [
    {
      "branchEndDateTime": "1984-09-22 10:00 am",
      "branchEndX": 100,
      "branchId": "id-15c1737f-1ab5-417e-b74c-14f3ee3f3461",
      "branchName": "Test Child Level 1",
      "branchStartDateTime": "1980-09-22 10:00 am",
      "branchStartX": 0,
      "children": [
        {
          "branchEndDateTime": "1984-09-22 10:00 am",
          "branchEndX": 100,
          "branchId": "id-15c1737f-1ab5-417e-b74c-14f3ee3f3467",
          "branchName": "Test Child Level 2",
          "branchStartDateTime": "1980-09-22 10:00 am",
          "branchStartX": 0,
          "children": [
            {
              "branchEndDateTime": "1984-09-22 10:00 am",
              "branchEndX": 100,
              "branchId": "id-15c1737f-1ab5-417e-b74c-14f3ee3fxxxx",
              "branchName": "Test Child Level 3",
              "branchStartDateTime": "1980-09-22 10:00 am",
              "branchStartX": 0,
              "children": [],
              "type": "Branch"
            }
          ],
          "type": "Branch"
        }
      ],
      "type": "Branch"
    }
  ],
  "type": "Branch"
}

Each parent has a child fragment, and every fragment has has an id branchId

"branchId": "id-15c1737f-1ab5-417e-b74c-14f3ee3f3461",

Given I know the branch Id, is there any way to update a single property on a child fragment?

example: I want to change the branchName "Test Child Level 2" to "Custardy underpants competition" given I know the branchId is "id-15c1737f-1ab5-417e-b74c-14f3ee3f3467"

Is there an elegant solution to this problem?

I have tried the following, but it's not working out

UPDATE BucketName AS l
SET o.branchName ='Custardy underpants competition' FOR o IN l.children END
WHERE l.branchId =  "id-15c1737f-1ab5-417e-b74c-14f3ee3f3467"

Many thanks!

References https://www.youtube.com/watch?v=RA68D8hOuSw

vsr vsr · Accepted Answer · 2020-03-10T05:19:54

The matching branchId can be in the root of document or in the children. Update root of the document you must use d.branchName i.e first SET in the following SQL. Second SET will take care of all the children how deep it is irrespective of structure (WITHIN).

Also you need WHERE clause WITHIN if there is no branchId never mutate document.

UPDATE default d
SET d.branchName = CASE WHEN d.branchId = "id-15c1737f-1ab5-417e-b74c-14f3ee3f3467" TEHN "Custardy underpants competition" ELSE d.branchName END,
b.branchName = "Custardy underpants competition" FOR b WITHIN d WHEN b.branchId = "id-15c1737f-1ab5-417e-b74c-14f3ee3f3467" AND b.type = "Branch" END
WHERE ANY b WITHIN d SATISFIES b.type = "Branch" AND b.branchId = "id-15c1737f-1ab5-417e-b74c-14f3ee3f3467" AND b.type = "Branch" END;

Couchbase - update child in self referencing document (N1QL)

1 Answers