mongodb 如何在mongodb中加入查询？

How to join query in mongodb?

提问by L.J.W

I have user document collection like this:

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

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

A user has many friends, I have the following query in SQL:

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

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

I would like to have something similar in MongoDB.

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

采纳答案by dannie.f

Edit: this answer only applies to versions of MongoDb prior to v3.2.

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

You can't do what you want in just one query. You would have to first retrieve the list of friend user ids, then pass those ids to the second query to retrieve the documents and sort them by age.

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

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

回答by Derek

To have everything with just one query using the $lookup feature of the aggregation framework, try this :

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

Let's say the content of your User collection is the following:

假设您的 User 集合的内容如下：

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

The result of the query will be:

查询的结果将是：

{
    "_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
        }
    ]
}

回答by user3280472

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

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

This is the doc for join query in mongodb , this is new feature from version 3.2.

这是 mongodb 中连接查询的文档，这是 3.2 版的新功能。

So this will be helpful.

所以这会很有帮助。

回答by pingw33n

MongoDB doesn't have joins, but in your case you can do:

MongoDB 没有联接，但在您的情况下，您可以执行以下操作：

db.coll.find({friends: userId}).sort({age: -1})

回答by Sérgio

one kind of join a query in mongoDB, is ask at one collection for id that match , put ids in a list (idlist) , and do find using on other (or same) collection with $in : idlist

在 mongoDB 中加入查询的一种方式，是在一个集合中询问匹配的 id，将 id 放入列表 (idlist) 中，并使用 $in 在其他（或相同）集合上查找使用：idlist

u = db.friends.find({"friends": ? }).toArray()
idlist= []
u.forEach(function(myDoc) { idlist.push(myDoc.id ); } )
db.friends.find({"id": {$in : idlist} } )

回答by Mateus Pereira

Only populate array friends.

只填充数组朋友。

User.findOne({ _id: "userId"})
.populate('friends')
.exec((err, user) => {
    //do something
});

Result is same like this:

结果是一样的：

{
    "_id" : "userId",
    "name" : "John",
    "age" : 30,
    "friends" : [
        { "_id" : "userId1", "name" : "Derek", "age" : 34 }
        { "_id" : "userId2", "name" : "Homer", "age" : 44 }
        { "_id" : "userId3", "name" : "Bobby", "age" : 12 }
    ]
}

Same this: Mongoose - using Populate on an array of ObjectId

同样的：猫鼬 - 在 ObjectId 数组上使用 Populate

回答by Andrey Araya

You can use in Moongoose JS .populate()and { populate : { path : 'field' } }. Example:

您可以在 Moongoose JS.populate()和{ populate : { path : 'field' } }. 例子：

Models:

楷模：

 mongoose.model('users', new Schema({
        name:String,
        status: true,
        friends: [{type: Schema.Types.ObjectId, ref:'users'}], 
        posts: [{type: Schema.Types.ObjectId, ref:'posts'}], 

    }));
 mongoose.model('posts', new Schema({
            description: String,
            comments: [{type: Schema.Types.ObjectId, ref:'comments'}], 

        }));
 mongoose.model('comments', new Schema({
            comment:String,
            status: true

        }));

If you want to see your friends' posts, you can use this.

如果您想查看朋友的帖子，可以使用此功能。

Users.find().                    //Collection 1
        populate({path:'friends',   //Collection 2
        populate:{path:'posts'   //Collection 3
        }})
    .exec();

If you want to see your friends' posts and also bring all the comments, you can use this and too, you can indentify the collection if this not find and the query is wrong.

如果您想查看朋友的帖子并带上所有评论，您可以使用它，如果找不到并且查询错误，您也可以识别集合。

 Users.find().                                    //Collection 1
        populate({path:'friends',                 //Collection 2
        populate:{path:'posts',                   //Collection 3
        populate:{path:'commets, model:Collection'//Collection 4 and more
        }}})
    .exec();

And to finish, if you want get only some fields of some Collection, you can use the propiertie select Example:

最后，如果您只想获取某个集合的某些字段，则可以使用属性选择示例：

Users.find().                                    
        populate({path:'friends', select:'name status friends'                  
        populate:{path:'comments'               
        }})
    .exec();

回答by Piyush Dholariya

var p = db.sample1.find().limit(2) , 
    h = [];
for (var i = 0; i < p.length(); i++) 
{
  h.push(p[i]['name']);
}
db.sample2.find( { 'doc_name': { $in : h } } ); 

it works for me.

这个对我有用。

回答by Marcelo Lazaroni

You can do it in one go using mongo-join-query. Here is how it would look like:

您可以使用mongo-join-query. 这是它的样子：

const joinQuery = require("mongo-join-query");

joinQuery(
    mongoose.models.User,
    {
        find: {},
        populate: ["friends"],
        sort: { age: 1 },
    },
    (err, res) => (err ? console.log("Error:", err) : console.log("Success:", res.results))
);

The result will have your users ordered by age and all of the friends objects embedded.

结果将使您的用户按年龄排序并嵌入所有朋友对象。

How does it work?

它是如何工作的？

Behind the scenes mongo-join-querywill use your Mongoose schema to determine which models to join and will create an aggregation pipelinethat will perform the join and the query.

在幕后mongo-join-query将使用您的 Mongoose 模式来确定要加入的模型，并将创建一个聚合管道来执行连接和查询。

回答by Dean Hiller

You can use playOrm to do what you want in one Query(with S-SQL Scalable SQL).

您可以使用 playOrm 在一个查询中执行您想要的操作（使用 S-SQL 可扩展 SQL）。