如何在mongodb中加入查询？

Question

我有这样的用户文档集：

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

一个用户有很多朋友，我在SQL中有以下查询：

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

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

Answer 1

编辑：此答案仅适用于v3.2之前的MongoDb版本。

你不能只在一个查询中做你想做的事。您必须首先检索朋友用户id的列表，然后将这些id传递给第二个查询以检索文档并按年龄对其进行排序。

var user = db.user.findOne({"id" : "001"}, {"friends": 1})
db.user.find( {"id" : {$in : user.friends }}).sort("age" : 1);

Answer 2

https://docs.mongodb.org/manual/reference/operator/aggregation/lookup/

这是mongodb中连接查询的文档，这是版本3.2的新特性。

所以这会有帮助的。

Answer 3

要使用聚合框架的$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
        }
    ]
}