How to compare field values in MongoDB?

To compare field values in MongoDB, you can use the $where operator for JavaScript-based comparisons or the $expr operator (recommended) for aggregation expression comparisons. Both methods allow you to compare fields within the same document.

Syntax

// Using $where (JavaScript evaluation)
db.collection.find({ $where: "this.field1 > this.field2" });

// Using $expr (Aggregation expressions - recommended)
db.collection.find({ $expr: { $gt: ["$field1", "$field2"] } });

Sample Data

db.comparingFieldDemo.insertMany([
    {"Value1": 30, "Value2": 40},
    {"Value1": 60, "Value2": 70},
    {"Value1": 160, "Value2": 190},
    {"Value1": 200, "Value2": 160}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5c9c99ed2d6669774125246e"),
        ObjectId("5c9c99f62d6669774125246f"),
        ObjectId("5c9c99ff2d66697741252470"),
        ObjectId("5c9c9a0b2d66697741252471")
    ]
}

Method 1: Using $where Operator

Find documents where Value1 is greater than Value2 ?

db.comparingFieldDemo.find({ $where: "this.Value1 > this.Value2" });

{ "_id": ObjectId("5c9c9a0b2d66697741252471"), "Value1": 200, "Value2": 160 }

Find documents where Value1 is less than Value2 ?

db.comparingFieldDemo.find({ $where: "this.Value1 < this.Value2" });

{ "_id": ObjectId("5c9c99ed2d6669774125246e"), "Value1": 30, "Value2": 40 }
{ "_id": ObjectId("5c9c99f62d6669774125246f"), "Value1": 60, "Value2": 70 }
{ "_id": ObjectId("5c9c99ff2d66697741252470"), "Value1": 160, "Value2": 190 }

Method 2: Using $expr Operator (Recommended)

Using $expr with aggregation operators is more efficient and can utilize indexes ?

db.comparingFieldDemo.find({ $expr: { $gt: ["$Value1", "$Value2"] } });

{ "_id": ObjectId("5c9c9a0b2d66697741252471"), "Value1": 200, "Value2": 160 }

Key Differences

Conclusion

Use $expr for simple field comparisons as it's more efficient and can utilize indexes. Reserve $where for complex JavaScript-based logic that cannot be expressed with aggregation operators.