couchbase - 在 Couchbase Nest 右侧数组中包含文档 ID

Question

我有一个嵌套操作，它将一组 id 与一些文档连接起来。

SELECT left.*,right FROM bucket AS left 
  LEFT NEST bucket AS right 
  ON META(right).id IN left.array

结果

[
  {
    array : ["rightId1","rightId2"],
    right : [ { < rightFields1 > }, { < rightFields2 > } ]
  }
]

我需要返回的文档（< rightFields >）在其字段之一上有 id，我该怎么做？

标签： couchbasen1ql

Answer 1

META(item).id 仅在 item 是存储桶别名的情况下有效，否则返回 MISSING。

在这种情况下，前 2 个是正确的方法，因为您有右侧的文档键。

SELECT left.*, 
       (SELECT META(r).id, r.* 
        FROM bucket AS r USE KEYS left.array) AS right
FROM bucket AS left;

或者

SELECT left.*, right
FROM bucket AS left
LET right = (SELECT META(r).id, r.* 
             FROM bucket AS r USE KEYS left.array);

如果您确实需要处理正确的文档并将它们用作数组而不是 NEST（不处理），您应该使用 JOIN、GROUP BY 和 ARRAY_AGG()

SELECT left.*, 
       ARRAY_AGG(OBJECT_ADD(right,"id",META(right).id)) AS right
FROM bucket AS left
LEFT JOIN bucket AS right ON KEYS left.array
GROUP BY left;