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     admin gmail com        userId     AD        userName     admin      userinfo           id    ObjectId  56d82612b63f1c31cf906003         userId     AD        phone     0000000000      userrole           id    ObjectId  56d82612b63f1c31cf906003         userId     AD        role     admin

User · Answer

First add the collections and then apply lookup on these collections. Don't use $unwind as unwind will simply separate all the documents of each collections. So apply simple lookup and then use $project for projection. Here is mongoDB query:

db.userInfo.aggregate([
    {
        $lookup: {
           from: "userRole",
           localField: "userId",
           foreignField: "userId",
           as: "userRole"
        }
    },
    {
        $lookup: {
            from: "userInfo",
            localField: "userId",
            foreignField: "userId",
            as: "userInfo"
        }
    },
    {$project: {
        "_id":0,
        "userRole._id":0,
        "userInfo._id":0
        }
        } ])

Here is the output:

/* 1 */ {
    "userId" : "AD",
    "phone" : "0000000000",
    "userRole" : [ 
        {
            "userId" : "AD",
            "role" : "admin"
        }
    ],
    "userInfo" : [ 
        {
            "userId" : "AD",
            "phone" : "0000000000"
        }
    ] }

Thanks.

User · Answer

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     admin gmail com        userName     admin        userPhone     0000000000        role     admin      Hope this will help you or someone else   Thanks

User · Answer

According to the documentation   lookup can join only one external collection   What you could do is to combine userInfo and userRole in one collection  as provided example is based on relational DB schema  Mongo is noSQL database - and this require different approach for document management   Please find below 2-step query  which combines userInfo with userRole - creating new temporary collection used in last query to display combined data  In last query there is an option to use  out and create new collection with merged data for later use      create collections   db sivaUser insert              id    ObjectId  5684f3c454b1fd6926c324fd             email     admin gmail com            userId     AD            userName     admin         userinfo  db sivaUserInfo insert          id    ObjectId  56d82612b63f1c31cf906003         userId     AD        phone     0000000000         userrole  db sivaUserRole insert          id    ObjectId  56d82612b63f1c31cf906003         userId     AD        role     admin           join  them all  -    db sivaUserInfo aggregate         lookup                       from   sivaUserRole              localField   userId              foreignField   userId              as   userRole                                  unwind   userRole                        project                 id  1               userId    1               phone    1               role     userRole role                                  out  sivaUserTmp             db sivaUserTmp aggregate         lookup                       from   sivaUser              localField   userId              foreignField   userId              as   user                                  unwind   user                        project                 id  1               userId    1               phone    1               role   1               email      user email                userName      user userName

User · Answer

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

[mongodb] How to join multiple collections with $lookup in mongodb

Examples related to mongodb

Examples related to join

Examples related to mongodb-query