MongoDb query condition on comparing 2 fields

Question

I have a collection T, with 2 fields: Grade1 and Grade2, and I want to select those with condition Grade1 > Grade2, how can I get a query like in MySQL?

Select * from T Where Grade1 > Grade2

Answer 1

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

Answer 2

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"]}}})

Answer 3

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 } }
])

Answer 4

In case performance is more important than readability and as long as your condition consists of simple arithmetic operations, you can use aggregation pipeline. First, use $project to calculate the left hand side of the condition (take all fields to left hand side). Then use $match to compare with a constant and filter. This way you avoid javascript execution. Below is my test in python:

import pymongo
from random import randrange

docs = [{'Grade1': randrange(10), 'Grade2': randrange(10)} for __ in range(100000)]

coll = pymongo.MongoClient().test_db.grades
coll.insert_many(docs)

Using aggregate:

%timeit -n1 -r1 list(coll.aggregate([
    {
        '$project': {
            'diff': {'$subtract': ['$Grade1', '$Grade2']},
            'Grade1': 1,
            'Grade2': 1
        }
    },
    {
        '$match': {'diff': {'$gt': 0}}
    }
]))

1 loop, best of 1: 192 ms per loop

Using find and $where:

%timeit -n1 -r1 list(coll.find({'$where': 'this.Grade1 > this.Grade2'}))

1 loop, best of 1: 4.54 s per loop