Mongoose nested query on Model by field of its referenced model

I'm pretty late to this one :p But I was just searching for a similar answer and I thought I'd share what I came up with in case anyone finds this for the same reason.

I couldn't find a way to achieve this through mongoose queries, but I think it works using the MongoDB aggregation pipeline

To get the query you're looking for you could do something like this:

const result=await Company.aggregate([
    {$lookup: {
        from: 'persons', 
        localField: 'founder', 
        foreignField: '_id', 
        as: 'founder'}
    },
    {$unwind: {path: '$founder'}},
    {$match: {'founder.lastname': 'Robertson'}}
]);

$lookup acts like .populate(), replacing the reference with the actual data. It returns an array though since it can be used to match multiple documents.

$unwind removes items from an array, and in this case will just turn the single element array into a field.

$match then does what it sounds like and only returns documents matching the query. You can also do more complex matching than strict equality if you need.

In general the way the aggregation pipeline works is by continually filtering/modifying matching documents each step of the way until you have just what you want.

I haven't checked the performance on this, but I definitely prefer having Mongo do the work rather than filtering out unnecessary results server side.

I guess the only downside is that the result will be just an array of objects rather than mongoose models since the pipeline typically changes the shape of documents. So you won't be able to use the model's methods on the returned data.

In case anyone comes across this in more recent times, Mongoose now supports join like functionality with a feature called Populate.

From the Mongoose documentation:

Story.findOne({ 
    title: 'Casino Royale' 
}).populate('author').exec(function (err, story) {
    if (err) return handleError(err);
    console.log('The author is %s', story.author.name);
    // prints "The author is Ian Fleming"
});

http://mongoosejs.com/docs/populate.html

You can't do this in a single query because MongoDB doesn't support joins. Instead, you have to break it into a couple steps:

// Get the _ids of people with the last name of Robertson.
Person.find({lastname: 'Robertson'}, {_id: 1}, function(err, docs) {

    // Map the docs into an array of just the _ids
    var ids = docs.map(function(doc) { return doc._id; });

    // Get the companies whose founders are in that set.
    Company.find({founder: {$in: ids}}, function(err, docs) {
        // docs contains your answer
    });
});