I am new to Couchbase and have been trying this for several hours. Have looked through the documentation but haven’t been able to locate this specific scenario, My bucket has documents:
{
"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"
}
The product_ids in the “order” type document map to the id field in the “product” type document. I just need to enhance the order document and get the result as below:
{
"type":"product",
"id":"2",
"name":"Product two"
}
I have tried:
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";
This returns no results.
What N1QL should I use?
Checkout 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;