MongoDB query condition on comparing 2 fields?

To query documents by comparing two fields in MongoDB, you can use the $where operator with JavaScript expressions or the more efficient $expr operator with aggregation expressions.

Syntax

// Using $where (JavaScript)
db.collection.find({
    $where: function() { 
        return this.field1 < this.field2; 
    }
});

// Using $expr (Recommended)
db.collection.find({
    $expr: { $lt: ["$field1", "$field2"] }
});

Sample Data

db.comparingTwoFieldsDemo.insertMany([
    {
        "StudentName": "John",
        "StudentAge": 21,
        "StudentMathMarks": 99,
        "StudentPhysicsMarks": 98
    },
    {
        "StudentName": "Carol",
        "StudentAge": 22,
        "StudentMathMarks": 79,
        "StudentPhysicsMarks": 89
    },
    {
        "StudentName": "David",
        "StudentAge": 24,
        "StudentMathMarks": 39,
        "StudentPhysicsMarks": 45
    },
    {
        "StudentName": "Bob",
        "StudentAge": 23,
        "StudentMathMarks": 87,
        "StudentPhysicsMarks": 78
    }
]);

Method 1: Using $where (JavaScript)

Find students where Math marks are less than Physics marks ?

db.comparingTwoFieldsDemo.find({
    $where: function() { 
        return this.StudentMathMarks < this.StudentPhysicsMarks; 
    }
});

{
    "_id": ObjectId("5c8ac0b46cea1f28b7aa0808"),
    "StudentName": "Carol",
    "StudentAge": 22,
    "StudentMathMarks": 79,
    "StudentPhysicsMarks": 89
}
{
    "_id": ObjectId("5c8ac0c96cea1f28b7aa0809"),
    "StudentName": "David",
    "StudentAge": 24,
    "StudentMathMarks": 39,
    "StudentPhysicsMarks": 45
}

Method 2: Using $expr (Recommended)

The same query using the more efficient $expr operator ?

db.comparingTwoFieldsDemo.find({
    $expr: { $lt: ["$StudentMathMarks", "$StudentPhysicsMarks"] }
});

{
    "_id": ObjectId("5c8ac0b46cea1f28b7aa0808"),
    "StudentName": "Carol",
    "StudentAge": 22,
    "StudentMathMarks": 79,
    "StudentPhysicsMarks": 89
}
{
    "_id": ObjectId("5c8ac0c96cea1f28b7aa0809"),
    "StudentName": "David",
    "StudentAge": 24,
    "StudentMathMarks": 39,
    "StudentPhysicsMarks": 45
}

Key Differences

• $where: Uses JavaScript functions but is slower as it cannot use indexes effectively
• $expr: Uses aggregation expressions and performs better with proper indexing
• Both methods return the same results but $expr is the recommended approach

Conclusion

Use $expr with aggregation operators like $lt, $gt, $eq for comparing fields efficiently. While $where works with JavaScript, $expr provides better performance and index utilization.