Perform JOIN in mongoDB using ‘$lookup’
MongoDB 3.2 has introduced ‘$lookup’ operator in aggregation framework , with the help of ‘$lookup’ operator we can perform LEFT JOIN.
The ‘$lookup’ stage lets you specify which collection you want to join with the current collection, and which fields that should match.
Syntax:
{ $lookup: { from: <collection to join>, localField: <field from the input documents>, foreignField: <field from the documents of the "from" collection>, as: <output array field> } }
Example :- We have “orders” Collection and “products” Collection :
orders:
[ { "_id":ObjectId("5b25fff518e09cfc0c000029"), "product_id": ObjectId("5b2601e318e09cfc0c00002d"), "status": 1 }, { "_id":ObjectId("5b26000018e09cfc0c00002a"), "product_id": ObjectId("5b2601e318e09cfc0c00002e"), "status": 1 } ]
products:
[ { "_id":ObjectId("5b2601e318e09cfc0c00002e"), "name": "A" }, { "_id":ObjectId("5b2601e318e09cfc0c00002d"), "name": "B" }, { "_id":ObjectId("5b2601e318e09cfc0c00002c"), "name": "C" } ]
Now to retrieve order with product detail we can use below
db.orders.aggregate({ $lookup: { from: "products", localField: "product_id", foreignField : "_id", as: "ordered_product" } })
Result documents:
{ "_id" : ObjectId("5b25fff518e09cfc0c000029"),"product_id": ObjectId("5b2601e318e09cfc0c00002d"),"status": 1, "ordered_product" : [ {"_id":ObjectId("5b2601e318e09cfc0c00002d"),"name": "B"}] }, { "_id" : ObjectId("5b26000018e09cfc0c00002a"),"product_id": ObjectId("5b2601e318e09cfc0c00002e"),"status": 1, "ordered_product" : [ {"_id":ObjectId("5b2601e318e09cfc0c00002e"),"name": "A"}] }