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!!

Pin it

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2