如何将一个文档中的数组值映射到另一个文档并显示在结果中

我是 Couchbase 的新手，已经尝试了几个小时了。已查看文档但无法找到这个特定场景， 我的存储桶中有文档：

{
"type":"order",
"order_id":"1",
"products":[{"product_id":"1","qty":10},{"product_id":"2","qty":20}]
},
{
"type":"product",
"id":"1",
"name":"Product one"
},
{
"type":"product",
"id":"2",
"name":"Product two"
}

“order”类型文档中的product_ids映射到“product”类型文档中的id字段。 我只需要增强订单文档并得到如下结果：

{
"type":"product",
"id":"2",
"name":"Product two"
}

我努力了：

select orderview.order_id,ARRAY{"product_id":l.product_id,"qty":l.qty,"name":m.name} for l in orderview.products end as product_view 
from `Orders` orderview inner join `Orders` m on orderview.product_id=m.id 
where orderview.type="order" and m.type="product";

这不会返回任何结果。

我应该使用什么 N1QL？

查看https://blog.couchbase.com/ansi-join-support-n1ql/ https://blog.couchbase.com/ansi-join-support-n1ql/

CREATE INDEX ix1 ON Orders(order_id) WHERE type = "order";
CREATE INDEX ix2 ON Orders(id, name) WHERE type = "product";

SELECT o.*, ARRAY_AGG(OBJECT_ADD(op,"name",p.name)) AS products
FROM Orders AS o
LEFT UNNEST o.products AS op
LEFT JOIN Orders AS p ON p.type = "product" AND op.product_id = p.id
WHERE o.type = "order" AND o.order_id IS NOT NULL
GROUP BY o;