Join between two collections in MongoDB

$lookup command used to perform join between two collections. This is a new feature of MongoDB version 3.2

$lookup performs the left outer join between two collections in the same database. For each input document, $lookup add new array fields whose element matches the joined collection.

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


$lookup takes a document with the following fields
from: Its takes collection name in the same database to perform joined.
localField: It takes the document in $lookup to perform equally match with foreign field from the documents which are the field of collection where we are performing aggregation.
foreighFiled: It takes the field of “from” the collection.
as: This is new array field contains the matching documents from the “from” collection.
Example: Suppose we have a collection of the employee.

Collection EMP


{_id:1,Name:"Manish",dep:"HR"}
{_id:2,Name:"Raj",dep:"Salesforce"}
{_id:3,Name:"Dilip",dep:"Database"}
{_id:4,Name:"Vipul",dep:"UI"}
{_id:5,Name:"Ashish",dep:"Syste Admin"}
{_id:7,Name:"Pankaj"}


Collection DEP

                                    
{_id:10,depName:"HR",Manager:"Mar1"}
{_id:11,depName:"Salesforce",Manager:"Mar2"}
{_id:12,depName:"Database",Manager:"Mar3"}
{_id:13,depName:"UI",Manager:"Mar4"}
{_id:14,depName:"Syste Admin",Manager:"Mar5"}


We have two collections EMP and DEP and we want to know about employees and their managers run the following query.


db.EMP.aggregate([
    {
      $lookup:
        {
          from: "DEP",
          localField: "dep",
          foreignField: "depName",
          as: "EMp_Manager"
        }
   }
])


Result: 


{ "_id" : 1, "Name" : "Manish", "dep" : "HR", "EMp_Manager" : [ { "_id" : 10, "depName" : "HR", "Manager" : "Mar1" } ] } 
{ "_id" : 2, "Name" : "Raj", "dep" : "Salesforce", "EMp_Manager" : [ { "_id" : 11, "depName" : "Salesforce", "Manager" : "Mar2" } ] }
{ "_id" : 3, "Name" : "Dilip", "dep" : "Database", "EMp_Manager" : [ { "_id" : 12, "depName" : "Database", "Manager" : "Mar3" } ] }
{ "_id" : 4, "Name" : "Vipul", "dep" : "UI", "EMp_Manager" : [ { "_id" : 13, "depName" : "UI", "Manager" : "Mar4" } ] }
{ "_id" : 5, "Name" : "Ashish", "dep" : "Syste Admin", "EMp_Manager" : [ { "_id" : 14, "depName" : "Syste Admin", "Manager" : "Mar5" } ] }
{ "_id" : 7, "Name" : "Pankaj", "EMp_Manager" : [] }


No comments:

Post a Comment

PostgreSQL: How to change the password of the user

This article will teach you to change the password of the user in PostgreSQL. To change the password, we use the ALTER ROLE, its uses are ...