Mongodb, aggregate query with $lookup

Question

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?

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

Solution 2

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

Comments

[Joe]

Sorry! I made a mistake in my post. projects is not an object. It's an array. I've updated the question. Could you look at again?

[chridam]

You can fix this by prefixing another { "$unwind": "$projects" } pipeline, as in the updated answer.

[Joe]

Very powerful stuff. Gotta learn this aggregation framework one day. Thx!

[Shani Kehati]

Thanks for bringing up the populate() option