Not able to use $exists in $lookup pipeline

I have the below query

db.orders.aggregate([
{
$lookup:
{
from: “warehouses”,
let: { order_item: “$item”, order_qty: “$ordered” },
pipeline: [
{ $match:
{ $expr:
{ $and:
[
{ $eq: [ “$stock_item”, “$$order_item” ] },
{“instock”: { “$exists”: true }}, ---- Tried exists in both ways but nothing is working
and giving syntax error.
{ $exists: [ “$instock”, true ] }
]
}
}
},
{ $project: { stock_item: 0, _id: 0 } }
],
as: “stockdata”
}
}
])
Could you please let me know how to use exists in pipeline query of lookup
Below is the sample collections data

db.orders.insert([
{ “_id” : 1, “item” : “almonds”, “price” : 12, “ordered” : 2 },
{ “_id” : 2, “item” : “pecans”, “price” : 20, “ordered” : 1 },
{ “_id” : 3, “item” : “cookies”, “price” : 10, “ordered” : 60 }
])

db.warehouses.insert([
{ “_id” : 1, “stock_item” : “almonds”, warehouse: “A” },
{ “_id” : 2, “stock_item” : “pecans”, warehouse: “A”, “instock” : 80 },
{ “_id” : 3, “stock_item” : “almonds”, warehouse: “B”},
{ “_id” : 4, “stock_item” : “cookies”, warehouse: “B”, “instock” : 40 },
{ “_id” : 5, “stock_item” : “cookies”, warehouse: “A” }
])

Thanks

You cannot use $exists within a $expr; only aggregation expressions are allowed. But, you can use something like this:
{ $match: { instock: { $exists: true } }

So, your present $match may look like this; a combination of two stages instead of the earlier one stage with $and:

{ $match: { $expr:{ $eq: [ "$stock_item", "$$order_item" ] } } },
{ $match: { instock: { $exists: true } } },

Now, the aggregation will run without any errors!

2 Likes

Thank you for your response. It works for me.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.