How to write a mongoose query to filter subdocuments

Question

I have a Mongoose schema called "Users" which has a "Roles" subdocument as one of its variables like so:

var UserSchema = new mongoose.Schema({
    email: { type: String, required: true, unique: true },
    password: { type: String, required: true },
    roles: [ { type: Number, ref: 'Role' } ]
});

var RoleSchema = new mongoose.Schema({
    _id: Number,
    name: { type: String, required: true, unique: true },
    description: { type: String, required: true }
});

I want to create a Mongoose query that will find all users with roles.names of "admin" or "owner". I've tried using this query, which I thought would work, but I don't get any users when I use the where...in part.

var roles = ["owner", "admin"];
User.find()
    .populate('roles')
    .where('roles.name').in(roles)
    .sort({'_id': 1})
    .exec(function (err, users) {
        res.send(users);
    });

Can someone tell me where my logic is wrong?

Answer 1

It's not possible to query more than one collection with a single query. If Role was an embedded subdocument in User then you could do a query on roles.name but presently that's not supported because it's a separate collection that is referenced in User.

However, a workaround is to add another filter step after the query returns that manually filters out documents which don't have any roles that matched the populate criteria but first you need to use a match query in populate instead of where method:

var roles = ["owner", "admin"];
User.find()
    .populate('roles', null, { name: { $in: roles } } )
    .sort({'_id': 1})
    .exec(function (err, users) {
        users = users.filter(function(user){
            return user.roles.length;
        });
        res.send(users);
    });