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

Possible duplicate of [Multiple join conditions using the $lookup operator](https://stackoverflow.com/questions/37086387/multiple-join-conditions-using-the-lookup-operator) — styvane, Oct 14 '17 at 07:56
Possible duplicate of [How do I query referenced objects in MongoDB?](https://stackoverflow.com/questions/9621928/how-do-i-query-referenced-objects-in-mongodb) — sidgate, Nov 07 '17 at 12:52

Amit Kumar · Answer 1 · 2017-09-16T11:42:35.517

192

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

edited Sep 16 '17 at 11:42

answered Feb 23 '17 at 08:42

Amit Kumar

2,141
1
11
13

if you want data in array from other table than just remove **$unwind** from that table means remove "**{ $unwind:"$user_role" }**" from the query for fetching data in array from **user_role** table – Amit Kumar Feb 23 '17 at 08:46
1

this was very helpful to me; particularly the use of $unwind and sub-object references in projection – Mr.Budris Apr 26 '18 at 01:11
Hi, Amit it looks good but it did not solve my problem here is the link please give a response: https://stackoverflow.com/questions/61188497/how-to-join-collection-from-mongodb-using-python – a zEnItH Apr 13 '20 at 13:23
Try $unwind in your query for both join tables. @azEnItH – Amit Kumar Jun 29 '20 at 12:46
Hey Amit, maybee you can answere a followup question to your pretty nice answere? -> https://stackoverflow.com/questions/67138310/mongodb-how-to-remove-duplicate-records-from-a-query – Johnny Apr 17 '21 at 12:44
Excellent answer. No complexities. – Venura Jun 24 '22 at 08:21

score 48 · Answer 2 · answered May 13 '16 at 16:55

48

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.

answered May 13 '16 at 16:55

Derek

1,826
18
25

1

what if we want to show userinfo as an arry in user role? how to do this – Muneem Habib Feb 14 '17 at 00:23
wasn't meant to be a relational database yet has a 16MB cap on each document forcing you to spread out infinite arrays into further collections... catch 22 – nrmad Jun 24 '21 at 15:47
This works for small collection size only, sooner or later you will hit the wall with the following error: ```Executor error during getMore :: caused by :: BufBuilder attempted to grow() to 67109538 bytes, past the 64MB limit.``` – Tomasz Plonka Feb 21 '23 at 07:21
In this example, the localfield and foreignfield have the same id in multiple collections, so you may be achieving the join accidentally. But if your joins have to be on different fields in subsequent collections, this may not work. Professors solution addresses that problem. – Kalyan Aug 08 '23 at 13:14

score 15 · Answer 3 · edited Jun 05 '17 at 20:33

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"
        }
    }
])

Hi, Profesor your code looks good but did not solve my problem I provide my question link please help me: https://stackoverflow.com/questions/61188497/how-to-join-collection-from-mongodb-using-python — a zEnItH, Apr 13 '20 at 13:33
Does anyone know if this statement is still true: `$lookup can join only one external collection`? I didn't find any limitation in the document link. Thanks — Izzi, Sep 12 '20 at 06:44

score 7 · Answer 4 · answered Sep 10 '20 at 09:38

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.

If there will be multiple documents in a collection then all the documents will be shown with in the array. — nixxo_raa, Sep 10 '20 at 09:39
Thank you very much for the answer.This answer helped me understand aggregation. You just saved my day — Bibek, Nov 30 '20 at 06:02

score -1 · Answer 5 · edited Feb 26 '22 at 01:29

first lookup finds all the products where p.cid = categories._id,simlarly 2nd lookup finds all products where p.sid = subcategories._id.

let dataQuery :any = await ProductModel.aggregate([ { $lookup:{
            from :"categories",
            localField:"cid",
            foreignField :"_id",
            as :"products"
        }
    },
    {
        $unwind: "$products"
    },
    { $lookup:{
        from :"subcategories",
        localField:"sid",
        foreignField :"_id",
        as :"productList"
    }
    },
    {
        $unwind: "$productList"
    },
    { 
        $project:{
        productList:0
    }
}
]);

How to join multiple collections with $lookup in mongodb

5 Answers5

Linked

Related