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

Licentious

Possible duplicate of Multiple join conditions using the $lookup operator – Ingalls

Possible duplicate of How do I query referenced objects in MongoDB? – Quartern

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

Viccora

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 – Viccora

this was very helpful to me; particularly the use of $unwind and sub-object references in projection – Lunatic

Hi, Amit it looks good but it did not solve my problem here is the link please give a response: #61188997 – Xylotomous

Try $unwind in your query for both join tables. @azEnItH – Viccora

Hey Amit, maybee you can answere a followup question to your pretty nice answere? -> #67138810 – Encroachment

Excellent answer. No complexities. – Degradable

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.

Chuff

what if we want to show userinfo as an arry in user role? how to do this – Henebry

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 – Gilt

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. – Unsegregated

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. – Chyle

Answer 3

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" : "[email protected]",
        "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"
        }
    }
])

Burble

Hi, Profesor your code looks good but did not solve my problem I provide my question link please help me: #61188997 – Xylotomous

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 – Ornithic

Answer 4

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.

Precessional

If there will be multiple documents in a collection then all the documents will be shown with in the array. – Precessional

Thank you very much for the answer.This answer helped me understand aggregation. You just saved my day – Phillips

Answer 5

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

Synclastic