Mongoose .find() is empty when querying on subdocument

Question

I'm trying to query a document based off of its subdocument data. When I do this I get no data returned. When I remove the "where" or when I query on a primitive type field within the parent it works fine. What am I missing?

My collections are broken up into separate files, here they are together for simplicity:

var PlayerSchema = new Schema({
    firstName: { type: String, default: '', required: true},
    lastName: { type: String, default: '', required: true},
    nickname: { type: String, default: '' },
});
module.exports = mongoose.model('Player', PlayerSchema);


var GameSchema = new Schema({
    winner: { type: Schema.Types.ObjectId, ref: 'Player', required: true},
    datePlayed: { type: Date, default: Date.now, required: true },
});
module.exports = mongoose.model('Game', GameSchema);


var GamePlayerSchema = new Schema({
    game: { type: Schema.Types.ObjectId, ref: 'Game', required: true},  
    player: { type: Schema.Types.ObjectId, ref: 'Player', required: true},
    points: { type: Number, default: 0 },
    place: { type: Number, default: 0 },
});
module.exports = mongoose.model('GamePlayer', GamePlayerSchema);

My query:

GamePlayerModel.find()
    //.where('player.firstName').equals('Brian') // returns empty
    //.where(place).equals(1) // returns correct dataset
    .where('game.datePlayed').gte(startDateRange).lt(endDateRange) // returns empty
    .select('game player points place')
    .populate('game')
    .populate('player')
    .exec(function (err, gamePlayers) {
        if(err) return next(err);
        res.json(gamePlayers);
    });

So again, if I query on a subdocument in any way it returns an empty dataset. I've tried game.datePlayed and even games.datePlayed. I'm not sure what to do. I don't need the player.firstName results, however I figured that'd be an easy thing to test to make sure the query is setup correctly.

Lastly, this is how I setup the date ranges. The date objects come out correctly, but are they possibly the wrong type?

var now = new Date();
var month = req.query.month ? parseInt(req.query.month) : now.getUTCMonth();
var year = req.query.year ? parseInt(req.query.year) : now.getUTCFullYear();
var endMonth = month+1;
if(endMonth > 11) endMonth = 0;
var startDateRange = new Date(year, month, 1);
var endDateRange = new Date(year, endMonth, 1);

Answer 1

Joins are not supported in MongoDB, which is what you are trying to do. The closest you can get is to filter as part of your .populate call:

.populate('game', null, {datePlayed: {$gte: startDateRange, $lt: endDateRange}})
.populate('player', null, {firstName: 'Brian'})

However what this will do is get all GamePlayer documents and only get the Game and Player subdocuments that match your criteria. If the subdocuments don't match your criteria, the GamePlayer document will still be returned with .game or .player equal to null.

You may want to reconsider your schema to be less like a SQL schema and to take advantage of the benefits of MongoDB. I'm not sure what your requirements are, but consider something like this:

var PlayerSchema = new Schema({
    firstName: { type: String, default: '', required: true},
    lastName: { type: String, default: '', required: true},
    nickname: { type: String, default: '' },
});


var GameSchema = new Schema({
    winner: { type: Schema.Types.ObjectId, ref: 'Player', required: true},
    datePlayed: { type: Date, default: Date.now, required: true },
    players: [{
        player: { type: Schema.Types.ObjectId, ref: 'Player', required: true},
        points: { type: Number, default: 0 },
        place: { type: Number, default: 0 }
    }]
});

Then your query could look something like:

GameModel.find()
    .where('players.place').equals(1) // This will limit the result set to include only Games which have at least one Player with a place of 1
    .where('datePlayed').gte(startDateRange).lt(endDateRange)
    .populate('players.player')
    .exec(function (err, games) {
        if(err) return next(err);
        res.json(games);
    });

The above example will include all Players in each returned Game, regardless of whether their place is 1, however it will only include Games which have at least one player with a place of 1.

If you want to limit the items in the players array, you might need to take it a step further and use an aggregate command to unwind the array then filter. For example:

GameModel.aggregate([
    {$unwind: 'players'},
    {$match: {'players.place' : 1}}

], function(err, results) {
    if (err) return next(err);
    res.json(results);
});

This will return a separate Game object for each Player with a place of 1. If a Game has more than one Player with a place of 1, it will return duplicate Game objects, each with a different Player.