Update a 4th level nested document

SQL++
,
1

Hi everyone,

I am quite new on couchbase and niql query.

I am wondering how I could update efficiently a fourth level nested document.

For exemple, my document would look like this :

{
  "level1_Id" : "L1_ID_0",
  "level1_Arr": [
    {
      "level2_Id": "L2_ID_0",
      "level2_Arr": [
        {
          "level3_Id": "L3_ID_0",
          "level3_Arr": [
            {
              "level4_Id": "L4_ID_0",
              "level4_Attr": "L4_ATTR"
            },
            {
              "level4_Id": "L4_ID_1",
              "level4_Attr": "L4_ATTR",
              "level4_Attr1": "L4_ATTR1"
            }
          ]
        }
      ]
    }
  ]
}

In this exemple I would like to update level4_Attr to another value when level4_Id is L4_ID_1. I know all level Ids.
At this time, I am able to update with a WITHIN loop

UPDATE default d use keys "TEST::1"
SET p.level4_Attr = “test” FOR p WITHIN d.level1_Arr WHEN p.L4_ID_1 = “planingId” END

but I guess is not efficiently because it loops over all subdocuments.

I found in the documentation we can now use multiple for loop but I do not know how to use this feature and target on each subdocument level2_Arr, level3_Arr, level4_Arr with their Ids.

Does someone give me any help ?

2

Looks good as is. You can index this if needed.

3

Thx for your answer.

Is there any way to do it using all Ids and so improve performance without adding any index ?
I tried to use multiple “for loop” but without success until now.

4

No. The important thing is USE KEYS, which you already have.

5

Maybe I don’t understand well, but in the official documentation in update section :

Starting version 4.5.1, the UPDATE statement has been improved to SET nested array elements. The FOR clause is enhanced to evaluate functions and expressions, and the new syntax supports multiple nested FOR expressions to access and update fields in nested arrays. Additional array levels are supported by chaining the FOR clauses.

Please could you explain me in which case use this feature and how to use it. I don’t know how to chain the FOR clauses.

6

See blogs by @prasad.

7

hi @nixosz,
Check this blog https://dzone.com/articles/couchbase-n1ql-continuing-to-simplify-transition-f
For your example, try (you can add WHEN at any FOR level)

UPDATE default d use keys "TEST::1"
SET k.level4_Attr = "test" 
FOR k IN ARRAY_FLATTEN( ARRAY j.level3_Arr 
                        FOR j IN ARRAY_FLATTEN(ARRAY i.level2_Arr 
                                               FOR i IN d.level1_Arr
                                               END, 1)
                        END , 1)
WHEN k.level4_Id = "L4_ID_0"
END
returning *;
1 Like
8

Hi @prasad ,

Thank you for your answer and the link which will be very helpful.

I wrote the same kind of request but without using Array_flatten feature, so my FOR … IN was FOR … WITHIN.

Thank you to both of you @prasad and @geraldss for your answers.

1 Like