How to compare multiple properties in MongoDB?

To compare multiple properties in MongoDB, you can use the $where operator or the $expr operator. The $where allows JavaScript expressions to compare fields, while $expr uses aggregation expressions for better performance.

Syntax

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

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

Sample Data

db.demo223.insertMany([
    {"Scores": [56, 78]},
    {"Scores": [88, 45]},
    {"Scores": [98, 79]}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e3ee4ca03d395bdc2134730"),
        ObjectId("5e3ee4d103d395bdc2134731"),
        ObjectId("5e3ee4d803d395bdc2134732")
    ]
}

Method 1: Using $where (JavaScript Expression)

Find documents where the first score is greater than the second score ?

db.demo223.find({ $where: "this.Scores[0] > this.Scores[1]" });

{ "_id": ObjectId("5e3ee4d103d395bdc2134731"), "Scores": [88, 45] }
{ "_id": ObjectId("5e3ee4d803d395bdc2134732"), "Scores": [98, 79] }

Method 2: Using $expr (Recommended)

The same comparison using $expr with $arrayElemAt for better performance ?

db.demo223.find({ 
    $expr: { 
        $gt: [
            { $arrayElemAt: ["$Scores", 0] }, 
            { $arrayElemAt: ["$Scores", 1] }
        ] 
    } 
});

{ "_id": ObjectId("5e3ee4d103d395bdc2134731"), "Scores": [88, 45] }
{ "_id": ObjectId("5e3ee4d803d395bdc2134732"), "Scores": [98, 79] }

Key Differences

Conclusion

Use $expr for comparing multiple properties as it offers better performance and index support. Reserve $where for complex JavaScript expressions that cannot be achieved with aggregation operators.