Collection One:
[
{ _id: 1,obj: { a: 123, b: "ABC" } }
]
Collection Two:
[
{ _id: 1, arr: [{ a: 123, b: "ABC" }, { a: 234, b: "BCD" }] },
{ _id: 2, arr: [{ a: 123, b: "BCD" }, { a: 234, b: "ABC" }] }
]
Requirements: Select matching documents from collection two where obj
of collection one is in arr
of collection two, and should match first (_id: 1
) document,
1) Try (Working):
I tried using $in
condition in $expr
in $match
stage, and this is working perfectly,
db.one.aggregate([
{
$lookup: {
from: "two",
let: { obj: "$obj" },
pipeline: [
{ $match: { $expr: { $in: ["$$obj", "$arr"] } } }
],
as: "matchFromTwo"
}
}
])
2) Try (Not Working):
This will fail when order of fields in object are different, like i have swap both fields (a,b) position in collection one of obj
:
Collection One:
[
{ _id: 1,obj: { b: "ABC", a: 123 } }
]
Collection Two:
[
{ _id: 1, arr: [{ a: 123, b: "ABC" }, { a: 234, b: "BCD" }] },
{ _id: 2, arr: [{ a: 123, b: "BCD" }, { a: 234, b: "ABC" }] }
]
This is not working because of fields position are different in obj
and arr
, this will also not work when other side arr
objects position are different.
3) Try (Not Working):
I have tried other way also using separate condition of each field in $and
,
db.one.aggregate([
{
$lookup: {
from: "two",
let: { obj: "$obj" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $in: ["$$obj.a", "$arr.a"] },
{ $in: ["$$obj.b", "$arr.b"] }
]
}
}
}
],
as: "matchFromTwo"
}
}
])
This will not match and condition of array in particular element like $elemMatch
, this returns both elements,
Is there any other way to deal with this kind of conditions?