CODEWITHSUNDEEP
couchbasesql++
Hohenheimsenberg
Hohenheimsenberg

Reputation: 980

Subquery for each item in a Couchbase double nested array

Having the next 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 this NEST doesn't work

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

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"}]
    }
  ]
}

Upvotes: 2

Views: 706

Answers (2)

vsr
vsr

Reputation: 7414

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 ..........;

Upvotes: 1

Hohenheimsenberg
Hohenheimsenberg

Reputation: 980

I was able to solve it iterating every element from bs in a subquery. Since the subquery is in the SELECT part of the query, it must have USE KEYS instead of ON META().id =. Finally I add the subquery result to each item.

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

It means that for each element in bs it queries every element of innerArray. And then adds the result to the element of bs.

Upvotes: 1

Related Questions