Possible to query for hierarchical data across documents?

SQL++
1

Hi,

Is it possible to do a n1ql query on hierarchical data? For example, consider I have a files and folders, folders can contain more files and folders. I realize I can store all the data as one document, but my question is about storing each file/folder as an individual document. File/folder would have name and id properties, folder would have an array of folder/file ids that it contains.

Is there a query that can get this all at once? I see there are subqueries, but I don’t think I can use that for this case, where the hierarchy depth isn’t known up front. Would I just pick an arbitrary depth and repeat the subquery that many times?

I see another similar question, Query tree like data similar to Oracle’s Connect By Prior db statement, that was prior to the release of n1ql, where it refers to CTEs…but I don’t think that’s quite the same as subqueries, is it?

Thanks for any help,
Todd

2

I don’t think CB supports the WITH clause yet. You’d have to programically traverse the hierarchy from your client. You might want to check out the LET clause in CB to help. In my case, I ended up putting a LEVEL key/ value in the JSON document to make traversing easier. The problem with that is updating it when there’s a large tree hierarchy modification.

3

does N1QL support WITH clause to query tree as Oracle’s connect by statement for now?

4

N1QL doesn’t have WITH clause, you can use LET clause

5

for example

INSERT INTO example (key, value) VALUES ("Org:1", {"dataType":"ORG","name":"Org1-lev1","parentId":"","description":"Level 1"});
INSERT INTO example (key, value) VALUES ("Org:2", {"dataType":"ORG","name":"Org2-lev2","parentId":"Org:1","description":"Level 2"});
INSERT INTO example (key, value) VALUES ("Org:3", {"dataType":"ORG","name":"Org3-lev2","parentId":"Org:1","description":"Level 2"});
INSERT INTO example (key, value) VALUES ("Org:4", {"dataType":"ORG","name":"Org4-lev3","parentId":"Org:2","description":"Level 3"});
INSERT INTO example (key, value) VALUES ("Org:5", {"dataType":"ORG","name":"Org5-lev3","parentId":"Org:2","description":"Level 3"});
INSERT INTO example (key, value) VALUES ("Org:6", {"dataType":"ORG","name":"Org6-lev3","parentId":"Org:3","description":"Level 3"});
INSERT INTO example (key, value) VALUES ("Org:7", {"dataType":"ORG","name":"Org7-lev4","parentId":"Org:5","description":"Level 4"});

I want to get all org document as tree order, such as

{
    "id": "Org:1", 
    "name": "Org1-lev1", 
    "description": "Level 1", 
    "child": [
        {
            "id": "Org:2", 
            "name": "Org2-lev2", 
            "description": "Level 2", 
            "child": [
                {
                    "id": "Org:4", 
                    "name": "Org4-lev3", 
                    "description": "Level 3"
                }, 
                {
                    "id": "Org:5", 
                    "name": "Org5-lev3", 
                    "description": "Level 3", 
                    "child": [
                        {
                            "id": "Org:7", 
                            "name": "Org7-lev4", 
                            "description": "Level 4"
                        }
                    ]
                }
            ]
        }, 
        {
            "id": "Org:3", 
            "name": "Org3-lev2", 
            "description": "Level 2", 
            "child": [
                {
                    "id": "Org:6", 
                    "name": "Org6-lev3", 
                    "description": "Level 3"
                }
            ]
        }
    ]
}

and I don’t know how much level does the org have.

6

Currently N1QL doesn’t support CONNECT BY

Based on your out put it is not possible. Check this post if u can build something similar

7

ok, is there any plan for N1QL to support CONNECT BY statement?

8

Opened improvement MB-29806

2 Likes