Recursive look within a tree structure

SQL++
1

This is similar to this question but my tree structure is not within a single document. Each children of a node tree is referred by its ID instead of being explicitly described in its parent.

Here is an example:

{
	"_id": "ROOT",
	"name": "root",
	"options": [
		"NODE_1",
		"NODE_2",
		"NODE_3",
		"NODE_4"
	]
}

{
	"_id": "NODE_1",
	"name": "node 1",
	"options": [
		"LEAF_1_1",
		"LEAF_1_2"
	]
}

{
	"_id": "LEAF_1_1",
	"name": "leaf 1.1"
}

And the structure might have multiple level of recusrivity. Only the leaves doesn’t have any options.
Any idea on how I could get every children of ROOT (plus the root id)? So the result should be this list of ids:

  • ROOT
  • NODE_1
  • NODE_2
  • NODE_3
  • NODE_4
  • LEAF_1_1
  • LEAF_1_2
  • LEAF_2_1

The result can be in any order. I would like a kind a FOR LOOP or WHILE but this isn’t possible.

Thanks for any suggestion!

2

N1QL Doesn’t support CONNECT BY.
You can check out this post and see if helps in what you are looking for N1QL - array join