How to aggregate two collections where a field from one collection is greater than the other in MongoDB?

To aggregate two collections where a field from one collection is greater than the other in MongoDB, use $lookup to join collections, followed by $redact and $gt to filter based on field comparison.

Syntax

db.collection1.aggregate([
    { $lookup: {
        "from": "collection2",
        "localField": "fieldName",
        "foreignField": "fieldName",
        "as": "joinedData"
    }},
    { $unwind: "$joinedData" },
    { $redact: {
        $cond: [
            { $gt: ["$field1", "$joinedData.field2"] },
            "$$KEEP",
            "$$PRUNE"
        ]
    }}
]);

Sample Data

Let's create two collections with product data ?

db.products.insertMany([
    { "ProductName": "Product1", "ProductPrice": 60 },
    { "ProductName": "Product2", "ProductPrice": 90 }
]);

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

db.competitors.insertMany([
    { "ProductName": "Product1", "ProductPrice": 40 },
    { "ProductName": "Product2", "ProductPrice": 70 }
]);

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

Example: Products with Higher Prices

Find products where our price is greater than competitor's price ?

db.products.aggregate([
    {
        $lookup: {
            "from": "competitors",
            "localField": "ProductName",
            "foreignField": "ProductName",
            "as": "competitorData"
        }
    },
    { $unwind: "$competitorData" },
    {
        $redact: {
            $cond: [
                { $gt: ["$ProductPrice", "$competitorData.ProductPrice"] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
]);

{
    "_id": ObjectId("..."),
    "ProductName": "Product1",
    "ProductPrice": 60,
    "competitorData": {
        "_id": ObjectId("..."),
        "ProductName": "Product1",
        "ProductPrice": 40
    }
}
{
    "_id": ObjectId("..."),
    "ProductName": "Product2", 
    "ProductPrice": 90,
    "competitorData": {
        "_id": ObjectId("..."),
        "ProductName": "Product2",
        "ProductPrice": 70
    }
}

How It Works

• $lookup joins collections based on matching field values
• $unwind deconstructs the array created by $lookup
• $redact with $gt filters documents where local field > foreign field
• $$KEEP retains matching documents, $$PRUNE removes non-matching ones

Conclusion

Use $lookup to join collections and $redact with comparison operators like $gt to filter aggregated results based on field comparisons between collections.