Mongodb，使用 $lookup 聚合查询

Mongodb, aggregate query with $lookup

提问by Joe

Got two collecetions, tags and persons.

有两个集合、标签和人员。

tags model:

标签模型：

{
  en: String,
  sv: String
}

person model:

人物模型：

{
  name: String,
  projects: [
    title: String,
    tags: [
      {
        type: Schema.ObjectId,
        ref: 'tag'
      }
    ]
  ]

}

I want query that returns all tags that is in use in the person model. All documents.

我想要查询返回在人员模型中使用的所有标签。所有文件。

Sometehing like

类似的东西

var query = mongoose.model('tag').find({...});

Or should I somehow use the aggregate approach to this?

或者我应该以某种方式使用聚合方法吗？

回答by chridam

For any particular person document, you can use the populate()function like

对于任何特定的个人文档，您可以使用populate()类似的功能

var query = mongoose.model("person").find({ "name": "foo" }).populate("projects.tags");

And if you want to search for any persons that have any tag with 'MongoDB' or 'Node JS' for example, you can include the query option in the populate()function overload as:

例如，如果您想搜索任何带有“MongoDB”或“Node JS”标签的人，您可以在populate()函数重载中包含查询选项，如下所示：

var query = mongoose.model("person").find({ "name": "foo" }).populate({
    "path": "projects.tags",
    "match": { "en": { "$in": ["MongoDB", "Node JS"] } }
});

---

---

If you want all tags existing in "project.tags"for all persons, then aggregation framework is the way to go. Consider running this pipeline on the person collection and uses the $lookupoperator to do a left join on the tags collection:

如果您希望所有人都存在所有标签"project.tags"，那么聚合框架就是您要走的路。考虑在人员集合上运行此管道并使用$lookup运算符对标签集合进行左连接：

mongoose.model('person').aggregate([
    { "$unwind": "$projects" },
    { "$unwind": "$projects.tags" },
    {
        "$lookup": {
            "from": "tags",
            "localField": "projects.tags",
            "foreignField": "_id",
            "as": "resultingTagsArray"
        }
    },
    { "$unwind": "$resultingTagsArray" },
    {
        "$group": {
            "_id": null,
            "allTags": { "$addToSet": "$resultingTagsArray" },
            "count": { "$sum": 1 }
        }
    }
 ]).exec(function(err, results){
    console.log(results);
 })

For any particular person then apply a $matchpipeline as the first step to filter the documents:

对于任何特定的人，然后应用$match管道作为过滤文档的第一步：

mongoose.model('person').aggregate([
    { "$match": { "name": "foo" } },
    { "$unwind": "$projects" },
    { "$unwind": "$projects.tags" },
    {
        "$lookup": {
            "from": "tags",
            "localField": "projects.tags",
            "foreignField": "_id",
            "as": "resultingTagsArray"
        }
    },
    { "$unwind": "$resultingTagsArray" },
    {
        "$group": {
            "_id": null,
            "allTags": { "$addToSet": "$resultingTagsArray" },
            "count": { "$sum": 1 }
        }
    }
 ]).exec(function(err, results){
    console.log(results);
 })

---

---

Another workaround if you are using MongoDB versions >= 2.6 or <= 3.0 which do not have support for the $lookupoperator is to populate the results from the aggregation as:

如果您使用不支持$lookup运算符的MongoDB 版本 >= 2.6 或 <= 3.0，另一种解决方法是将聚合结果填充为：

mongoose.model('person').aggregate([
    { "$unwind": "$projects" },
    { "$unwind": "$projects.tags" },    
    {
        "$group": {
            "_id": null,
            "allTags": { "$addToSet": "$projects.tags" }
        }
    }
 ], function(err, result) {
    mongoose.model('person')
    .populate(result, { "path": "allTags" }, function(err, results) {
        if (err) throw err;
        console.log(JSON.stringify(results, undefined, 4 ));
    });
});

回答by Clement Amarnath

If you are using MongoDb version 3.2 then you can use $lookupwhich performs an left outer join.

如果您使用的是 MongoDb 3.2 版，那么您可以使用$lookup执行左外连接。