Mongoose nested query on Model by field of its referenced model

Question

It seems like there is a lot of Q/A's on this topic on stackoverflow, but I can't seem to find an exact answer anywhere.

What I have:

I have Company and Person models:

var mongoose = require('mongoose');
var PersonSchema = new mongoose.Schema{
                        name: String, 
                        lastname: String};

// company has a reference to Person
var CompanySchema = new mongoose.Schema{
                        name: String, 
                        founder: {type:Schema.ObjectId, ref:Person}};

What I need:

Find all companies that people with lastname "Robertson" have founded

What I tried:

Company.find({'founder.id': 'Robertson'}, function(err, companies){
    console.log(companies); // getting an empty array
});

Then I figured that Person is not embedded but referenced, so I used populate to populate founder-Person and then tried to use find with 'Robertson' lastname

// 1. retrieve all companies
// 2. populate their founders
// 3. find 'Robertson' lastname in populated Companies
Company.find({}).populate('founder')
       .find({'founder.lastname': 'Robertson'})
       .exec(function(err, companies) {
        console.log(companies); // getting an empty array again
    });

I still can query companies with Person's id as a String. But it's not exactly what I want as you can understand

Company.find({'founder': '525cf76f919dc8010f00000d'}, function(err, companies){
    console.log(companies); // this works
});

Solution 1

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

Solution 2

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.

Solution 3

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

Comments

[AzaFromKaza]

You're right, that does look like a joint. I gave you the simplest nested query that I am thinking to implement, that's probably the case where relational DB's are more handy. But anyways, your solution worked. Thnx!

[maumercado]

Hello, I was looking for this answer since I have the same problem, one last question though, has this changed or is it possible on mongoose 3.8.5 now? I realized since this question was posted mongoose upped a few versions.

[JohnnyHK]

@maumercado No, it hasn't changed, and isn't likely to.

[Steve2955]

Are there any updates on this or do I still need to use this workaround?

[SS-Salt]

How would you do this and access the author's name if you were to do findMany({}) instead and there were multiple authors authors [{ type: Schema.ObjectId, ref: 'Author'}]?

[Abanoub Istfanous]

He asking for querying for nested ref not to populate He want to find story by author name

[EzPizza]

You can also write this in a more readable way: aggregate().lookup({}).unwind({}).match({});

[EzPizza]

After matching the desired fields, I sometimes want to "un-do" the lookup/populate, to get back the original fields of my model. Here I would do .addFields({ founder: '$founder._id' }) at the end of the aggregate pipeline. This will override the founder object by it's id.