如何在 mongodb 中使用 $lookup 加入多个集合

How to join multiple collections with $lookup in mongodb

提问by Siva M

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

我想使用聚合在 MongoDB 中加入两个以上的集合$lookup。可以加入吗？给我一些例子。

Here I have three collections:

这里我有三个集合：

users:

users：

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

userinfo:

userinfo：

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

userrole:

userrole：

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

回答by Amit Kumar

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

Mongodb 3.2及以后版本支持的join特性。您可以通过使用聚合查询来使用连接。
您可以使用以下示例进行操作：

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

谢谢

回答by Derek

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

您实际上可以链接多个 $lookup 阶段。根据 profesor79 共享的集合名称，您可以执行以下操作：

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 并不意味着是关系型的，但它很有用。

回答by profesor79

According to the documentation, $lookup can join only one external collection.

根据文档，$lookup 只能加入一个外部集合。

What you could do is to combine userInfoand userRolein one collection, as provided example is based on relational DB schema. Mongo is noSQL database - and this require different approach for document management.

您可以做的是组合userInfo并userRole在一个集合中，因为提供的示例基于关系数据库模式。Mongo 是 noSQL 数据库 - 这需要不同的文档管理方法。

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.

请在下面找到两步查询，它结合了 userInfo 和 userRole - 创建在上次查询中使用的新临时集合以显示组合数据。在最后一个查询中，有一个选项可以使用 $out 并使用合并的数据创建新集合以供以后使用。

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