Mongodb, aggregate query with $lookup

问题:

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?

回答1:

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

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:

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 $lookup operator to do a left join on the tags collection:

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 $match pipeline as the first step to filter the documents:

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 $lookup operator is to populate the results from the aggregation as:

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 ));
    });
});

回答2:

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