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"}]
 }


Leave a Reply

Your email address will not be published. Required fields are marked *