The Left Outer Join in MongoDB [$lookup Join]

“How to Join query in MongoDB”?

“How do I perform the SQL Join equivalent in MongoDB”?

The $lookup is introduced in the MongoDB version 3.2 and it’s perform a “Left Outer Join” in the same database collection.

The “$lookup” stage does an equality match between a fields.

The “$lookup” stage adds a new array field whose elements are the matching from the “joined” collection.

The “$lookup” is an alternate way to implement Joins in MongoDB.

Syntax:

{ 
      $lookup:  { 
                 from: “collection- to-join”,  
                 localField: “Input-field-documents”,
                 foreignField: “Input-field- from - documents -of - the- "from"- collection”,
                 as: “Output- field-Array>
     }
}

Perform Joins using $lookup
A collection Users contains

{ "_id" : 1001, "name" : "Anil Singh", "Age" : 32, "Qualification" : "MCA"}
{ "_id" : 1002, "name" : "Reena Singh", "price" : 26, "Qualification" : "MA" }
{ "_id" : 10033  }

Other collection Customers contains:

{ "_id" : 1001, "cust_name" : "Aradhya", description: "Customer 1", "terms" : 3 }
{ "_id" : 1002, "cust_name" : "Sunil", description: "Customer 2", "terms" : 4 }
{ "_id" : 1003, "cust_name" : "Sushil", description: "Customer 3", "terms" : 6 }
{ "_id" : 1004, "cust_name" : null, description: "Customer 4" }
{ "_id" : 1005 }

Join operations perform between the users and customer’s collection on the fields “name” and “cust_name”.

db.users.aggregate([
    {
      $lookup:
        {
          from: "customers",
          localField: "name",
          foreignField: "cust_name",
          as: "users_customers_joins_docs"
        }
   }
]);

The Operation Result looks as.

{ "_id" : 1001,
            "name" : "Anil Sing",
            "Age": 32,
            "Qualification" : "MCA",        
            "users_customers_joins_docs" : [
                  { "_id" : 1002, "cust_name" : "Sunil", description: "Customer 2", "terms" : 4 } ]
}
{ "_id" : 1002,
            "name" : "Reena Singh",
            "Age" : 26,
            "Qualification" : "MA",
            "users_customers_joins_docs" : [
                     { "_id" : 1003, "cust_name" : "Sushil", description: "Customer 3", "terms" : 6 }]
}
{ "_id" : 1003 
   "users_customers_joins_docs" : [
                        { "_id" : 1004, "cust_name" : null, description: "Customer 4" }
                        { "_id" : 1005 } ]
}

Reference Links,

https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-1-of-3-introduction

http://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb

I hope you are enjoying with this post! Please share with you friends. Thank you!!