I've a collection details with array of string values on quotes_id like the following
{
"quantity": 2000,
"quotes_id": ["SQ-CPQ10037"],
"status": "processed",
"logs": [""],
"product_id": "5ff5cf3dc1ceb9144901da81"
}
And my quotes collection
{
"_id":"SQ-CPQ10037",
"product_id":"5ff5cf3dc1ceb9144901da81",
...more fields here
}
Now, I'm trying to access this collection from another collection named quotes using lookup pipeline like the following
$lookup: {
from: "details",
"let": { "product_id": "$quotes.product_id", "quotes_id": "$quotes._id"},
pipeline: [
{
$match: {
$expr: {
$and: [
{ $in: [ "$quotes_id", ["$$quotes_id"] ] },
{ $eq: [ "$product_id", "$$product_id" ] },
]
}
}
}],
as: "product.productquotes",
},
This is returning empty collection. However, if I comment the line
{ $in: [ "$quotes_id", ["$$quotes_id"] ] },
then it is returning me the record. I also tried with simple $eq for the quotes_id but with same result. But if I run the query directly like
{$and: [{"product_id": ObjectId('5ff5cf3dc1ceb9144901da81')}, {"quotes_id": "SQ-CPQ10037"}]}
on mongo compass, I receive the record without any issue.
You have done almost correct. But wrongly wrote the $in args. Second arg of in should be an array.
{
$in: [
"$$quotes_id",
"$quotes_id"
]
}
So the lookup likes
db.quotes.aggregate([
{
$lookup: {
from: "details",
"let": { "product_id": "$product_id", "quotes_id": "$_id" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $in: [ "$$quotes_id", "$quotes_id" ] },
{ $eq: [ "$product_id", "$$product_id" ] }
]
}
}
}
],
as: "product.productquotes"
}
}
])
Working Mongo playground