couchbase - Couchbase 双嵌套数组中每个项目的子查询

Question

有下一个对象

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

现在我可以通过这样的数组与 NEST 进行连接。

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

现在我想嵌套 c bs 中每个项目的文档
添加此 NEST 不起作用

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

我正在寻找这个结果:

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

Answer 1

我能够通过迭代 bs 中的每个元素来解决它在 subquery .由于子查询位于查询的 SELECT 部分，因此它必须使用 USE KEYS 而不是 ON META().id =。最后，我将子查询结果添加到每个项目。

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

这意味着对于 bs 中的每个元素它查询innerArray的每个元素。然后将结果加到 bs的元素上.