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

Question

I have two collections lets call them collection A and collection B and they both have a price field and item number. so lets say there are 2 items in each:

Collection A:

name = item1 , price = 30

name = item2 , price = 20

Collection B:

name = item1 , price = 50

name = item2 , price = 10

I want to display the items for which collection A's price is bigger than collection B's price. For the above example that would be item2 for instance.

How can I write such a query? I'm using robomongo.

(EDIT)

Follow up on my previous question;

I want to display the items for which collection A's price is bigger than collection B's price * (exchange rate) + (some constant)

Let's say condition is;

where A's price > B's price*1.5+4

so in this case it should still display the same item.

Let's say condition is;

where A's price > B's price*1.5+10

then it shouldn't display any of the items

Answer 1

You could use the $lookup operator to first do a join on collectionB from collectionA, flatten the single element array returned from the result with $unwind and then use $redact pipeline to do document level redaction where documents that match the specified condition are retained using the $$KEEP system variable, and those that do not satisfy the condition are discarded using $$PRUNE. In the end, you would need to run the following aggregate pipeline:

var exchangeRate = 1.5;
db.collectionA.aggregate([
    { "$match": { "price": { "$exists": true }, "name": { "$exists": true } } },
    {
        "$lookup": {
            "from": "collectionB",
            "localField": "name",
            "foreignField": "name",
            "as": "collectionB"
        }
    },
    { "$unwind": "$collectionB" },
    {
        "$redact": {
            "$cond": [
                { 
                    "$gt": [
                        "$price", {
                            "$add": [
                                { "$multiply": [ "$collectionB.price", exchangeRate ] },
                                4
                            ]
                        }
                    ]
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

---

Robomongo Demo

Populate test collections:

db.collectionA.insert([
    { "name": "item1", "price": 30 },
    { "name": "item2", "price": 20 }    
])

db.collectionB.insert([
    { "name": "item1", "price": 50 },
    { "name": "item2", "price": 10 }    
])

Run and debug aggregate pipeline:

Sample Output:

{
    "_id" : ObjectId("58ad50a429b2961777f91c95"),
    "name" : "item2",
    "price" : 20,
    "collectionB" : {
        "_id" : ObjectId("58ad50a429b2961777f91c97"),
        "name" : "item2",
        "price" : 10
    }
}