How to map array values in one document to another and display in result

Question

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?

vsr vsr · Accepted Answer · 2021-05-28T02:59:14

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;

How to map array values in one document to another and display in result

1 Answers