Mongoose Query: compare two values on same document

Question

How can I query a Mongo collection using Mongoose to find all the documents that have a specific relation between two of their own properties?

For example, how can I query a characters collections to find all those characters that have their currentHitPoints value less than their maximumHitPoints value? Or all those projects that have their currentPledgedMoney less than their pledgeGoal?

I tried to something like this:

mongoose.model('Character')
    .find({
        player: _currentPlayer
    })
    .where('status.currentHitpoints').lt('status.maximumHitpoints')
    .exec(callback)

but I am getting errors since the lt argument must be a Number. The same goes if I use $.status.maximumHitpoints (I was hoping Mongoose would be able to resolve it like it does when doing collection operations).

Is this something that can be done within a Query? I would expect so, but can't find out how. Otherwise I can filter the whole collection with underscore but I suspect that is going to have a negative impact on performance.

PS: I also tried using similar approaches with the find call, no dice.

Answer 1

MongoDB 3.6 and above supports aggregation expressions within the query language:

db.monthlyBudget.find( { $expr: { $gt: [ "$spent" , "$budget" ] } } )

https://docs.mongodb.com/manual/reference/operator/query/expr/

Answer 2

Thanks to Aniket's suggestion in the question's comments, I found that the same can be done with Mongoose using the following syntax:

mongoose.model('Character')
    .find({
        player: _currentPlayer
    })
    .$where('this.status.currentHitpoints < this.status.maximumHitpoints')
    .exec(callback)

Notice the $where method is used instead of the where method.

EDIT: To expand on Derick's comment below, a more performance sensitive solution would be to have a boolean property inside your Mongoose schema containing the result of the comparison, and update it everytime the document is saved. This can be easily achieved through the use of Mongoose Schema Plugin, so you would have something like:

var CharacterSchema = new mongoose.Schema({
    // ...
    status: {
        hitpoints: Number,
        maxHitpoints: Number,
        isInFullHealth: {type: Boolean, default: false}
    }
})
.plugin(function(schema, options) {
     schema.pre('save', function(next) {
         this.status.isInFullHealth = (this.status.hitPoints >= this.status.maxHitpoints);

         next();
     })
 })

Answer 3

mongoose.model('Character')
    .find({
        player: _currentPlayer, $expr: { $lt: ['$currentHitpoints', '$maximumHitpoints'] }
    })

This above query means find the record which has currentHitpoints less than maximumHitpoints