Querying Highly Nested JSON

server
query
n1ql

#1

I am writing a Reddit like comment store using Couchbase. For each comment, I am storing its parentId and a list of its childrenIds . Each top-level comment on a web page will have its parentId as null .

I want to retrieve a comment block efficiently. By a comment block, I mean a top-level comment along with all its children comments. So the first step in this can be to write a map function that emits the ids of all top-level comments.

How do I go about fetching the entire tree once I have the root. A very naive approach would be to find the children, and query them recursively. But this defeats the purpose of not using a relational database for this project (since I am dealing with highly nested data and relational databases are terrible at storing them).

Can someone guide me on this?


#2

Couchbase doesn’t support CONNECT BY. You can use technique described here N1QL - array join


#3

You can also get array of all ids or objects like below.

INSERT INTO default VALUES ("k01",
{ "id": "520707438", "pageURIHash": "3988665684",  "parentId": null, "content": "Here is a parent comment" , "children": [ { "id": "1-1", "pageURIHash": "3988665684", "children": [{ "id": "1-1-1"}], "parentId": null, "content": "Here is a parent comment" }, { "id": "1-2", "pageURIHash": "3988665684", "children": [{ "id": "1-2-1"}], "parentId": null, "content": "Here is a parent comment" } ]});

SELECT ARRAY p.id FOR p WITHIN d;
"results": [
{
    "ids": [
        "1-1",
        "1-1-1",
        "1-2",
        "1-2-1"
    ]
}
]