How to join nested document using lookup in mongodb

In this article we will learn how to join two collection using $lookup in mongodb.

Lets we have two collection users and applications and documents as below

User Documents

{ "_id" : ObjectId("63eb1fcfcbe6f3036c88a716"),"first_name" : "Bartie","last_name" : "Kidde","email" : "bkidde0@sbwire.com","gender" : "Male"}

{"_id" : ObjectId("63eb1fcfcbe6f3036c88a717"),"first_name" : "Armando","last_name" : "Blaxley","email" : "ablaxley1@t-online.de","gender" : "Male"}

Application Document

{
    "_id" : ObjectId("63eb2acfcbe6f3036c88a77a"),
    "first_name" : "Stefano",
    "last_name" : "Scroxton",
    "email" : "sscroxton0@bloglovin.com",
    "gender" : "Male",
    "application_no" : 4297.0,
    "process_user" : [ 
        {
            "role" : "A",
            "userid" : ObjectId("63eb1fcfcbe6f3036c88a716")
        }, 
        {
            "role" : "B",
            "userid" : ObjectId("63eb1fcfcbe6f3036c88a717")
        }
    ]
}

Now if we want user information from user collection inside process_user of application document. We can query using aggregation and lookup in mongoDB.

db.applications.aggregate([
   {
       $lookup:{
           'from':'users',
           'localField':'process_user.userid',
           'foreignField':'_id',
           'as':'pro_user'
           }
   }
])

Above $lookup result will add a new array filed to each input document as below 

{
    "_id" : ObjectId("63eb2acfcbe6f3036c88a77a"),
    "first_name" : "Stefano",
    "last_name" : "Scroxton",
    "email" : "sscroxton0@bloglovin.com",
    "gender" : "Male",
    "application_no" : 4297.0,
    "process_user" : [ 
        {
            "role" : "A",
            "userid" : ObjectId("63eb1fcfcbe6f3036c88a716")
        }, 
        {
            "role" : "B",
            "userid" : ObjectId("63eb1fcfcbe6f3036c88a717")
        }
    ],
    "pro_user" : [ 
        {
            "_id" : ObjectId("63eb1fcfcbe6f3036c88a716"),
            "first_name" : "Bartie",
            "last_name" : "Kidde",
            "email" : "bkidde0@sbwire.com",
            "gender" : "Male"
        }, 
        {
            "_id" : ObjectId("63eb1fcfcbe6f3036c88a717"),
            "first_name" : "Armando",
            "last_name" : "Blaxley",
            "email" : "ablaxley1@t-online.de",
            "gender" : "Male"
        }
    ]
}

Now if we want  user information along with the userid inside of process_user we have to modifiy the aggregation as below

  1. Loop the process_user array using $map

  2. Get the matching object from lookup users ( pro_user) using $filter

  3. Get the first element from above $filter result using $arrayElemAt

  4. Merge current object and returned object from $arrayElemAt using $mergeObjects

  5. Add the result from map to a new field  (proces_users_new) using $set

  6. Unset the field pro_user and process_user using $unset

db.applications.aggregate([
   {
       $lookup:{
           'from':'users',
           'localField':'process_user.userid',
           'foreignField':'_id',
           'as':'pro_user'
           }
   },
   {
       $set:{
           "proces_users_new":{
               $map:{
                   'input':'$process_user',
                   'as':'item',
                   'in':{
                       $mergeObjects:[
                           '$$item',
                           {
                               $arrayElemAt:[
                                        {
                                            $filter:{
                                                'input':'$pro_user',
                                                'as':'info',
                                                'cond':{
                                                    $eq:['$$info._id','$$item.userid']
                                                    }
                                                }
                                       },0
                                   ]
                               }
                           ]
                       }
                   }
               
               }
           }
   },
   {
       $unset:['process_user','pro_user']
   }
])

Final Output of above qyery

{
    "_id" : ObjectId("63eb2acfcbe6f3036c88a77a"),
    "first_name" : "Stefano",
    "last_name" : "Scroxton",
    "email" : "sscroxton0@bloglovin.com",
    "gender" : "Male",
    "application_no" : 4297.0,
    "proces_users_new" : [ 
        {
            "role" : "A",
            "userid" : ObjectId("63eb1fcfcbe6f3036c88a716"),
            "_id" : ObjectId("63eb1fcfcbe6f3036c88a716"),
            "first_name" : "Bartie",
            "last_name" : "Kidde",
            "email" : "bkidde0@sbwire.com",
            "gender" : "Male"
        }, 
        {
            "role" : "B",
            "userid" : ObjectId("63eb1fcfcbe6f3036c88a717"),
            "_id" : ObjectId("63eb1fcfcbe6f3036c88a717"),
            "first_name" : "Armando",
            "last_name" : "Blaxley",
            "email" : "ablaxley1@t-online.de",
            "gender" : "Male"
        }
    ]
}