NEST on double nested array

SQL++
1

Having the following objects

"a" : {
  "id" : "1",
  "arr" : [
    {"id" : "b1"}, {"id" : "b2"}
  ]
}

"b1" : {
  "id" : "b1",
  "innerArr" : [{"id" : "c1"},{"id" : "c2"}]
}

"b2" : {
  "id" : "b2",
  "innerArr" : [{"id" : "c3"}]
}

"c1" : {
  "name" : "c1"
}...

Right now I’m able to make a join with NEST over an array like this.

SELECT *
FROM bucket AS a
NEST bucket AS bs
  ON META(a).id IN a.arr[*].id


{
  "id" : "1",
  "arr" : [
    {"id" : "b1"}, {"id" : "b2"}
  ],
  "bs" : [
    {
      "id" : "b1",
      "innerArr" : [{"id" : "c1"},{"id" : "c2"}]
    },
    {
      "id" : "b2",
      "innerArr" : [{"id" : "c3"}]
    }
  ]
}
```

Now I want to NEST c documents for each item in bs
Adding these NEST doesn’t work

NEST bucket AS cs
  ON META(c).id IN bs[*].innerArr[*].id

NEST bucket AS cs
  ON META(c).id IN ARRAY item.innerArr[*].id FOR item IN bs END

NEST bucket AS cs
  ON META(c).id IN ARRAY item.id FOR item IN bs[*].innerArr END

I’m looking for this result:

{
  "id" : "1",
  "arr" : [
    {"id" : "b1"}, {"id" : "b2"}
  ],
  "bs" : [
    {
      "id" : "b1",
      "innerArr" : [{"id" : "c1"},{"id" : "c2"}],
      "cs" : [{"name" : "c1"},{"name" : "c2"}]
    },
    {
      "id" : "b2",
      "innerArr" : [{"id" : "c3"}],
      "cs" : [{"name" : "c3"}]
    }
  ]
}
2

NEST bucket AS bs .

bs starts with document (Scan, Fetch, ON). At the end of NEST bs becomes ARRAY for Filter, Group, projections, etc. Same applies chained JOIN, NEST. Example 17: https://blog.couchbase.com/ansi-join-support-n1ql/

In those situations use JOIN+GROUP on LEFT document + ARRAG_AGG on right document. Or use ARRAY …FOR… syntax.

The desired results can be achieved by following query. This LEFT outer nest

SELECT a.*,
       (SELECT b.*,
               (SELECT c.*
                 FROM  bucket AS c USE KEYS b.innerArr[*].id) AS cs
        FROM bucket AS b USE KEYS a.arr[*].id) AS bs
FROM bucket AS a
WHERE ..........;
3

Thanks, your solution works.
I also solved it like this:

SELECT a*.
  ,ARRAY OBJECT_ADD(item, "cs", (SELECT c.* FROM bucket AS c USE KEYS item.innerArr[*].id)) FOR item IN bs END 
FROM bucket AS a,
NEST bucket AS bs
  ON META(a).id IN a.arr[*].id

Your solution is more legible, but mine is faster. I guess it has to do with some index.

Now I have a new problem that has to do with this query. Having the same objects plus D:

"a" : {
  "id" : "1",
  "arr" : [
    {"id" : "b1"}, {"id" : "b2"}
  ]
}

"b1" : {
  "id" : "b1",
  "innerArr" : [{"id" : "c1"},{"id" : "c2"}]
}

"b2" : {
  "id" : "b2",
  "innerArr" : [{"id" : "c3"}]
}

"c1" : {
  "name" : "c1"
}...

"d1" : {
  "name" : "d1",
  "cs" :["c3","c4"] 
}

And I need this new join:

{
  "id" : "1",
  "arr" : [
    {"id" : "b1"}, {"id" : "b2"}
  ],
  "bs" : [
    {
      "id" : "b1",
      "innerArr" : [{"id" : "c1"},{"id" : "c2"}],
      "cs" : [{"name" : "c1"},{"name" : "c2"}]
    },
    {
      "id" : "b2",
      "innerArr" : [{"id" : "c3"}],
      "cs" : [
         {
            "name" : "c3", 
            "d":{"name":"d1"}
         }
       ]
    }
  ]
}

Since the inner join must USE KEYS I can’t do something like this

SELECT META(d).id 
  FROM entity_catalog AS d 
  WHERE ANY item IN d.cs SATISFIES item = META(c).id END

Can’t reference de result of an outer query in the subquery.