Remove node in deep nested tree

SQL++
1

Hello all,

I’m currently working with deep nested (recursive ?) documents which looks like this

"type": "root",
"id": "root_1",
"array_of_myobj": [
    {
        "type": "myobj",
        "id": "myobj_1_1",
        "array_of_myobj": [
            {
                "type": "myobj",
                "id": "myobj_1_1_1"
            },
            {
                "type": "myobj",
                "id": "myobj_1_1_2",
                "array_of_myobj": [....]
            }
        ]
    },
    {
        "type": "myobj",
        "id": "myobj_1_2"
    },
    {
        "type": "myobj",
        "id": "myobj_1_3",
        "array_of_myobj": [
            {
            "type": "myobj",
            "id": "myobj_1_3_1"
            },
            {
                "type": "myobj",
                "id": "myobj_1_3_2"
            }
        ]
    }
]

}

I’m correctly able to retrieve one specific document using

SELECT (ARRAY array_of_myobj FOR obj WITHIN b.array_of_myobj WHEN obj.id = ‘myobj_1_1_2’ END) FROM default b WHERE b.type = ‘root’ AND b.id = ‘root_1’

And I can update one specific document using

UPDATE default b SET obj.prop1 = ‘new value’ FOR obj WITHIN array_of_myobj WHEN obj.id = ‘myobj_1_1_2’ END WHERE b.type = ‘root’ AND b.id = ‘root_1’

Although now I would like to remove 1 item from a deep array but can’t find out how. I tried to set the element to null but it seems I can’t update an entire element using the following

UPDATE default b SET obj = NULL …

or

UPDATE default b SET obj = {“new object”} …

I tried to use ARRAY_REMOVE but it needs the entire element to look for which I would like to avoid.
There might be something to play for ARRAY_FLATTEN or is there any “deep unnest” ?

Would love to have some feedback on this, can’t get my head around this one. Maybe recursive is not such a good choice ?

Thanks!

1 Like
2

To update or unset you need to root of json,

Update top level 

UPDATE default b
SET b.array_of_myobj = ARRAY v FOR v IN b.array_of_myobj WHEN v.id != "myobj_1_2" END WHERE b.type = "root" AND b.id = "root_1";

Update deep level

UPDATE default b
SET obj.array_of_myobj = ARRAY v FOR v IN obj.array_of_myobj WHEN v.id != "myobj_1_1_2" END FOR obj WITHIN b.array_of_myobj END WHERE b.type = "root" AND b.id = "root_1";
3

Hello @vsr1,

Thanks a lot for your answer, it makes quite sense. Couldn’t find how to nest loops.
I’m trying to run the “deep level” update query but even if couchbase report 1 mutation done there are no difference on my document and “myobject_1_1_2” is still there.

Any index I should have created ?

4

If document qualify mutation or not only depends on the WHERE clause.
i.e If mutation is decided it may update same document as it is if set clause is conditional. To avoid that if possible add condition to WHERE clause. Update an attribute inside a array in a json docuemnt

You can create Index based on WHERE clause.

5

I see but my issue here is that the mutation should happen (So 1 mutation reported is correct) but the nested object I’m trying to remove is still there (The document remains the same).

I just add the WHERE condition AND ANY o WITHIN b.array_of_myobj WHEN o.id = "myobj_1_1_2" and mutation is still 1 so that condition seems correct but the SET part might be missing something

6

obj is variable you need to remove form the array you need to update the array object not unsetting varaible
Example : when obj is point to one of the array position you need to remove the object in that array position. not un setting variable.

7

Sorry I forgot to mention, I’m working with depth of X so not the exact document above but even more levels
E.g

{
  "array_of_myobj": [
    {
      "array_of_myobj": [
        {
          "array_of_myobj": [
            {
              "id": "myobj_1_1_1_1",
              "type": "myobj"
            }
          ],
          "id": "myobj_1_1_1",
          "type": "myobj"
        }
      ],
      "id": "myobj_1_1",
      "type": "myobj"
    }
  ],
  "id": "root_1",
  "type": "root"
}

But I don’t really know the depth I should look into so I would like for find a query to cover all levels

8

if you are removing the one level it should work. It doesn’t matter which depth you are removing. Did you tried it

UPDATE default b
SET b.array_of_myobj = ARRAY v FOR v IN b.array_of_myobj WHEN v.id != "myobj_1_1_2" END,
 obj.array_of_myobj = ARRAY v FOR v IN obj.array_of_myobj WHEN v.id != "myobj_1_1_2" END FOR obj WITHIN b.array_of_myobj END
WHERE b.type = "root" AND b.id = "root_1";
1 Like
9

I might have found a solution, not sure how it sounds but I’ll keep track of a node’s parent like

{
    "array_of_myobj": [
      {
        "array_of_myobj": [
          {
            "array_of_myobj": [
              {
                "id": "myobj_1_1_1_1",
                "type": "myobj",
                "parent_id": "myobj_1_1_1"
              }
            ],
            "id": "myobj_1_1_1",
            "type": "myobj",
            "parent_id": "myobj_1_1"
          }
        ],
        "id": "myobj_1_1",
        "type": "myobj"
      }
    ],
    "id": "root_1",
    "type": "root"
  }

Which I’ll be able to update doing

UPDATE test b
SET obj.array_of_myobj = ARRAY v FOR v IN obj.array_of_myobj WHEN v.id != "myobj_1_1_1_1" END 
FOR obj WITHIN b.array_of_myobj WHEN obj.id = 'myobj_1_1_1' END 
WHERE b.type = "root" AND b.id = "root_1";

Seems to be working well on my project, thanks a lot for your help and time!

1 Like
10

Sure. This should also work

UPDATE default b
SET b.array_of_myobj = ARRAY v FOR v IN b.array_of_myobj WHEN v.id != "myobj_1_1_2" END,
 obj.array_of_myobj = ARRAY v FOR v IN obj.array_of_myobj WHEN v.id != "myobj_1_1_2" END FOR obj WITHIN b.array_of_myobj END
WHERE b.type = "root" AND b.id = "root_1";
1 Like