join - 如何在mongodb中加入查询？

Question

我有这样的用户文档集合:

User {
   id:"001"
   name:"John",
   age:30,
   friends:["userId1","userId2","userId3"....]
}

一个用户有很多 friend ，我在SQL中有如下查询:

select * from user where in (select friends from user where id=?) order by age

我想在 MongoDB 中有类似的东西。

Answer 1

要使用聚合框架的 $lookup 功能只需一个查询即可获得所有内容，请尝试以下操作:

db.User.aggregate(
    [
        // First step is to extract the "friends" field to work with the values
        {
            $unwind: "$friends"
        },
        // Lookup all the linked friends from the User collection
        {
            $lookup:
            {
                from: "User",
                localField: "friends",
                foreignField: "_id",
                as: "friendsData"
            }
        },
        // Sort the results by age
        {
            $sort: { 'friendsData.age': 1 }
        },
        // Get the results into a single array
        {
            $unwind: "$friendsData"
        },
        // Group the friends by user id
        {
            $group:
            {
                _id: "$_id",
                friends: { $push: "$friends" },
                friendsData: { $push: "$friendsData" }
            }
        }
    ]
)

假设您的用户集合的内容如下:

{
    "_id" : ObjectId("573b09e6322304d5e7c6256e"),
    "name" : "John",
    "age" : 30,
    "friends" : [
        "userId1",
        "userId2",
        "userId3"
    ]
}
{ "_id" : "userId1", "name" : "Derek", "age" : 34 }
{ "_id" : "userId2", "name" : "Homer", "age" : 44 }
{ "_id" : "userId3", "name" : "Bobby", "age" : 12 }

查询的结果将是:

{
    "_id" : ObjectId("573b09e6322304d5e7c6256e"),
    "friends" : [
        "userId3",
        "userId1",
        "userId2"
    ],
    "friendsData" : [
        {
            "_id" : "userId3",
            "name" : "Bobby",
            "age" : 12
        },
        {
            "_id" : "userId1",
            "name" : "Derek",
            "age" : 34
        },
        {
            "_id" : "userId2",
            "name" : "Homer",
            "age" : 44
        }
    ]
}