Match between fields in MongoDB aggregation framework?

To match between fields in MongoDB aggregation framework, use the $cmp operator within a $project stage to compare field values, followed by a $match stage to filter results based on the comparison.

Syntax

db.collection.aggregate([
    {
        $project: {
            comparisonResult: { $cmp: ["$field1", "$field2"] }
        }
    },
    {
        $match: { comparisonResult: { $gt: 0 } }
    }
]);

Sample Data

db.matchBetweenFieldsDemo.insertMany([
    { "FirstValue": 40, "SecondValue": 70 },
    { "FirstValue": 20, "SecondValue": 5 }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("..."),
        ObjectId("...")
    ]
}

Display Sample Data

db.matchBetweenFieldsDemo.find();

{
    "_id": ObjectId("5c92c9625259fcd19549980d"),
    "FirstValue": 40,
    "SecondValue": 70
}
{
    "_id": ObjectId("5c92c96b5259fcd19549980e"),
    "FirstValue": 20,
    "SecondValue": 5
}

Example: Match Documents Where FirstValue > SecondValue

db.matchBetweenFieldsDemo.aggregate([
    {
        $project: {
            firstGreaterThanOrNot: { $cmp: ["$FirstValue", "$SecondValue"] }
        }
    },
    {
        $match: { firstGreaterThanOrNot: { $gt: 0 } }
    }
]);

{ "_id": ObjectId("5c92c96b5259fcd19549980e"), "firstGreaterThanOrNot": 1 }

How It Works

• $cmp returns 1 if the first field is greater than the second
• $cmp returns 0 if both fields are equal
• $cmp returns -1 if the first field is less than the second
• $match: { field: { $gt: 0 } } filters documents where the comparison result is greater than 0

Conclusion

Use $cmp in the aggregation pipeline to compare field values numerically. Combine it with $match to filter documents based on field comparisons, enabling powerful field-to-field matching in your queries.