Nested complex query across linked documents

SQL++
1

Hi All, I am new to couchbase and this is my first post to this forum. I have multiple nested(linked) documents inside couchbase using _id . And I need to traverse these linked documents starting from top-level document and fetch certain values from each of them as I iterate, till I reach the last document with a specific value.
Here is my TOP level document when I do a query

select * from AbucketName where _id = ‘6ebb5612-43e2-47ec-a7cd-95f4cb1ce38e’

Once . I get this document I need to keep querying the document that is there under its object “ress” which can have a dynamic any number of items inside it. I need to now fetch vale of eg. ress.81c300f1-8dd3-4ef2-afe9-1c78ff813fcd.Id and and get the value “81c300f1-8dd3-4ef2-afe9-1c78ff813fcd” once I have that I again select * from AbucketName where _id = ‘81c300f1-8dd3-4ef2-afe9-1c78ff813fcd’ and fetch “abClass” value stored in side the document . I need to keep going recursively till I find a document with "label " = “Last Label”.

{
“_id”: “6ebb5612-43e2-47ec-a7cd-95f4cb1ce38e”,
“_status”: “COMP”,
“_ver”: “316be7c1-94a6-408d-ad29-5530d4170c00”,
“abClass”: “USER”,
“label”: “A Top label”,
“ress”: {
“81c300f1-8dd3-4ef2-afe9-1c78ff813fcd”: {
“label”: “A label”,
“_id”: “81c300f1-8dd3-4ef2-afe9-1c78ff813fcd”,
“_ver”: “a1f7cc3f-fcd6-47af-a598-649636fdfaf7”
},
“d24ca6f3-209b-452b-bbd6-077acd2a9a13”: {
“label”: “B label”,
“_id”: “d24ca6f3-209b-452b-bbd6-077acd2a9a13”,
“_ver”: “54c3b554-80c3-4361-8ed6-2c897ad2a437”
}
}
}

2

N1QL doesn’t have any CTE with recursion or connect by. You need to achieve this from the outside.

The following query gives array of all ids in ress object

SELECT d.abClass, d.label, OBJECT_NAMES(d.ress) AS lids
FROM AbucketName AS d WHERE d._id = “6ebb5612-43e2-47ec-a7cd-95f4cb1ce38e”;

May be you can do some thing similar like this

ids = ["6ebb5612-43e2-47ec-a7cd-95f4cb1ce38e"]
FOR id IN ids
   SELECT d.abClass, d.label, OBJECT_NAMES(d.ress) AS lids FROM AbucketName AS d WHERE d._id = $id;
   if label == "Last Label"
        break
   ids = lids
1 Like