MongoDb query condition on comparing 2 fields
You can use a $where. Just be aware it will be fairly slow (has to execute Javascript code on every record) so combine with indexed queries if you can.
db.T.find( { $where: function() { return this.Grade1 > this.Grade2 } } );
or more compact:
db.T.find( { $where : "this.Grade1 > this.Grade2" } );
UPD for mongodb v.3.6+
you can use $expr
as described in recent answer
You can use $expr ( 3.6 mongo version operator ) to use aggregation functions in regular query.
Compare query operators
vs aggregation comparison operators
.
Regular Query:
db.T.find({$expr:{$gt:["$Grade1", "$Grade2"]}})
Aggregation Query:
db.T.aggregate({$match:{$expr:{$gt:["$Grade1", "$Grade2"]}}})
If your query consists only of the $where
operator, you can pass in just the JavaScript expression:
db.T.find("this.Grade1 > this.Grade2");
For greater performance, run an aggregate operation that has a $redact
pipeline to filter the documents which satisfy the given condition.
The $redact
pipeline incorporates the functionality of $project
and $match
to implement field level redaction where it will return all documents matching the condition using $$KEEP
and removes from the pipeline results those that don't match using the $$PRUNE
variable.
Running the following aggregate operation filter the documents more efficiently than using $where
for large collections as this uses a single pipeline and native MongoDB operators, rather than JavaScript evaluations with $where
, which can slow down the query:
db.T.aggregate([
{
"$redact": {
"$cond": [
{ "$gt": [ "$Grade1", "$Grade2" ] },
"$$KEEP",
"$$PRUNE"
]
}
}
])
which is a more simplified version of incorporating the two pipelines $project
and $match
:
db.T.aggregate([
{
"$project": {
"isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] },
"Grade1": 1,
"Grade2": 1,
"OtherFields": 1,
...
}
},
{ "$match": { "isGrade1Greater": 1 } }
])
With MongoDB 3.4 and newer:
db.T.aggregate([
{
"$addFields": {
"isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] }
}
},
{ "$match": { "isGrade1Greater": 1 } }
])