Hi @A_ML, and welcome to the MongoDB Community forums!
In MongoDB the $lookuup
operator does a left outer join of two collections.
db.coll1.aggregate([
{
$lookup: {
from: "coll2",
localField: "mail",
foreignField: "mail",
as: "joined"
}
}
])
This will give results similar to the following based on your example:
[
{
_id: ObjectId("633219622627d7ac551adc7f"),
mail: 'a',
value: 2,
joined: [
{
_id: ObjectId("633219742627d7ac551adc82"),
mail: 'a',
value: 2
}
]
},
{
_id: ObjectId("633219622627d7ac551adc80"),
mail: 'b',
value: 3,
joined: []
},
{
_id: ObjectId("633219622627d7ac551adc81"),
mail: 'c',
value: 4,
joined: [
{
_id: ObjectId("633219742627d7ac551adc83"),
mail: 'c',
value: 4
}
]
}
]
However it looks you want only those documents that exist in the left side and not the right side. You can add a $match
to the above to return only where the joined
field is an empty array (i.e. no matched document on the right side:
db.coll1.aggregate([
{
$lookup: {
from: "coll2",
localField: "mail",
foreignField: "mail",
as: "joined"
}
},
{
$match: {
joined: []
}
}
])
Which returns:
[
{
_id: ObjectId("633219622627d7ac551adc80"),
mail: 'b',
value: 3,
joined: []
}
]
From there you can do what you need with the documents that remain in the pipeline.
Note that this works just fine with the very small sample data, but you will want to test with larger amounts of production style data and make sure to filter out any unneeded documents before performing the lookup and make sure that the necessary indexes are in place.