Updating a Highly Nested Document in Couchbase

SQL++
, ,
1

I have a document that contains multiple levels of hierarchy. Something like this:

{
  "id": "520707438",
  "pageURIHash": "3988665684",
  "children": [],
  "parentId": null,
  "content": "Here is a parent comment"
}

The children array may have other child comments as JSON objects, and each of the child in turn may have other children. Thus, this forms a highly nested structure.

Now, suppose I want to add a child comment to a comment with ID as 123456745 . I am assuming that I know the root-level comment (so that I can use the USE KEYS clause in my N1ql query). How do I extract the children array corresponding to the comment with that particular Id and append a new comment to it? I could use the sub-document API but it requires me to know the path, and in this case, I do not know it.

I did a bit of research and came up with this query:

"UPDATE default d use keys \"" + comment.getRootCommentId()
           + "\" SET (??? How do I get the existing array and append to it) FOR p WITHIN d.children WHEN p.id = \"" + comment.getId() + "\" END";

Thanks a lot!!

2 
UPDATE default d USE KEYS  "KEY1"
SET d.children = CASE WHEN d.id == "commentid"
                      THEN ARRAY_APPEND(IFMISSINGORNULL(d.children,[]), {.....})
                      ELSE d.children
                 END,
    p.children = ARRAY_APPEND(IFMISSINGORNULL(p.children,[]), {.....})
    FOR p WITHIN d.children WHEN p.id = "commentid" END;
3

Gee that looks complex. I’ll update you once I try it.

Edit: Works. Thanks a lot.