I have an issue with joining documents in mongo based on conditions on a nested field in an array. Basically, I want to filter the documents of the foreign collection.
I was following the example on the official MongoDB documentation for Multiple Join Conditions with $lookup and extended it to fit my requirements.
Testdata (Note, the warehouse
documents are extended by the field foo
):
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", "instock" : 120, 'foo': [{ 'bar': 1 }] },
{ "_id" : 2, "stock_item" : "pecans", warehouse: "A", "instock" : 80, 'foo': [{ 'bar': 1 }] },
{ "_id" : 3, "stock_item" : "almonds", warehouse: "B", "instock" : 60, 'foo': [{ 'bar': 1 }] },
{ "_id" : 6, "stock_item" : "almonds", warehouse: "A", "instock" : 61, 'foo': [{ 'bar': 2 }] },
{ "_id" : 4, "stock_item" : "cookies", warehouse: "B", "instock" : 40, 'foo': [{ 'bar': 1 }] },
{ "_id" : 5, "stock_item" : "cookies", warehouse: "A", "instock" : 80, 'foo': [{ 'bar': 1 }] }
])
On the lookup, I want to add the additional condition foo.bar: 1
:
db.orders.aggregate([
{
$lookup: {
from: "warehouses",
let: { order_item: "$item", order_qty: "$ordered" },
pipeline: [{
$match: {
$expr: {
$and: [
{ $eq: [ "$stock_item", "$$order_item" ] },
{ $gte: [ "$instock", "$$order_qty" ] },
{ $eq: [ "$foo.bar", 1 ] }
]
}
}
}, {
$project: { stock_item: 0, _id: 0 }
}],
as: "stockdata"
}
}
])
Unfortunately this extra condition does not work. Furthermore not a sigle document is returned by the lookup.
Can someone point me in the right direction? I know the problem can also be done by a pipeline using unwind, filter and group.
Thanks!