How to join multiple collections with $lookup in mongodb

Question

I want to join more than two collections in MongoDB using the aggregate $lookup. Is it possible to join? Give me some examples.

Here I have three collections:

users:

{         "_id" : ObjectId("5684f3c454b1fd6926c324fd"),     "email" : "[email protected]",     "userId" : "AD",     "userName" : "admin" } 

userinfo:

{     "_id" : ObjectId("56d82612b63f1c31cf906003"),     "userId" : "AD",     "phone" : "0000000000" } 

userrole:

{     "_id" : ObjectId("56d82612b63f1c31cf906003"),     "userId" : "AD",     "role" : "admin" } 

Answer 1

The join feature supported by Mongodb 3.2 and later versions. You can use joins by using aggregate query.
You can do it using below example :

db.users.aggregate([      // Join with user_info table     {         $lookup:{             from: "userinfo",       // other table name             localField: "userId",   // name of users table field             foreignField: "userId", // name of userinfo table field             as: "user_info"         // alias for userinfo table         }     },     {   $unwind:"$user_info" },     // $unwind used for getting data in object or for one record only      // Join with user_role table     {         $lookup:{             from: "userrole",              localField: "userId",              foreignField: "userId",             as: "user_role"         }     },     {   $unwind:"$user_role" },      // define some conditions here      {         $match:{             $and:[{"userName" : "admin"}]         }     },      // define which fields are you want to fetch     {            $project:{             _id : 1,             email : 1,             userName : 1,             userPhone : "$user_info.phone",             role : "$user_role.role",         }      } ]); 

This will give result like this:

{     "_id" : ObjectId("5684f3c454b1fd6926c324fd"),     "email" : "[email protected]",     "userName" : "admin",     "userPhone" : "0000000000",     "role" : "admin" } 

Hope this will help you or someone else.

Thanks

Answer 2

You can actually chain multiple $lookup stages. Based on the names of the collections shared by profesor79, you can do this :

db.sivaUserInfo.aggregate([     {         $lookup: {            from: "sivaUserRole",            localField: "userId",            foreignField: "userId",            as: "userRole"         }     },     {         $unwind: "$userRole"     },     {         $lookup: {             from: "sivaUserInfo",             localField: "userId",             foreignField: "userId",             as: "userInfo"         }     },     {         $unwind: "$userInfo"     } ]) 

This will return the following structure :

{     "_id" : ObjectId("56d82612b63f1c31cf906003"),     "userId" : "AD",     "phone" : "0000000000",     "userRole" : {         "_id" : ObjectId("56d82612b63f1c31cf906003"),         "userId" : "AD",         "role" : "admin"     },     "userInfo" : {         "_id" : ObjectId("56d82612b63f1c31cf906003"),         "userId" : "AD",         "phone" : "0000000000"     } } 

Maybe this could be considered an anti-pattern because MongoDB wasn't meant to be relational but it is useful.