How to join multiple collections with $lookup in mongodb

user_5492366 0
Viewed 168326

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"
}
mongodb-query mongodb join
5 Answers

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_76106940

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