CODEWITHSUNDEEP
node.jsmongodbmongoose
Gian Marco Toso
Gian Marco Toso

Reputation: 12136

Mongoose Query: compare two values on same document

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.

Upvotes: 5

Views: 15673

Answers (4)

Saif Ullah
Saif Ullah

Reputation: 3067

Starting in MongoDB 5.0, the $eq, $lt, $lte, $gt, and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage.

Example The following operation uses $expr to find documents where the spent amount exceeds the budget:

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

Upvotes: 1

Ahsan Farooq
Ahsan Farooq

Reputation: 957

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

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

Upvotes: 1

TomoMiha
TomoMiha

Reputation: 1279

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/

Upvotes: 12

Gian Marco Toso
Gian Marco Toso

Reputation: 12136

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

Upvotes: 6

Related Questions