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?

You want all the tags in whole application or all the tags for a particular person? Because if you want to list all the tags without concerning person, you can directly query on tags collection. — Puneet Singh, Oct 07 '16 at 07:58
I want the tags in the whole application. All tags existing in project.tags for all persons. — Joe, Oct 07 '16 at 08:05

chridam · Accepted Answer · 2016-10-07T09:11:02.283

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

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? — Joe, Oct 07 '16 at 09:09
You can fix this by prefixing another `{ "$unwind": "$projects" }` pipeline, as in the updated answer. — chridam, Oct 07 '16 at 09:13
Very powerful stuff. Gotta learn this aggregation framework one day. Thx! — Joe, Oct 07 '16 at 11:11

score 0 · Answer 2 · answered Oct 07 '16 at 07:58

0

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

answered Oct 07 '16 at 07:58

Clement Amarnath

5,301
1
21
34

Mongodb, aggregate query with $lookup

2 Answers2

Linked