I have two collection. User and Course
User Collection
[{
"_id": "11111",
"name": "john",
},
{
"_id": "11112",
"name": "smith",
}]
Course Collection
[{
"_id": "00011",
"user_id": "11111",
"location_id": "9999",
},
{
"_id": "00012",
"user_id": "11111",
"location_id": "8888",
},
{
"_id": "00013",
"user_id": "11111",
"location_id": "7777",
},
{
"_id": "00014",
"user_id": "11112",
"location_id": "7777",
}]
If I apply the filter by region_id 7777 then I want below output. If I apply the region id 7777 and 8888, I want same output. So basically, I want all user regions, if it matches at least one region_id. I am getting correct response if there is no region_id filter
Expected result:
[
{
"_id": "11111",
"name": "john",
"regions": [
{
"_id": "00011",
"user_id": "11111",
"location_id": "9999"
},
{
"_id": "00012",
"user_id": "11111",
"location_id": "8888"
},
{
"_id": "00013",
"user_id": "11111",
"location_id": "7777"
}
]
},
{
"_id": "11112",
"name": "smith",
"regions": [
{
"_id": "00014",
"user_id": "11112",
"location_id": "7777"
}
]
}
]
Below is my aggregate query
db.user.aggregate([
{
"$match": {}
},
{
"$lookup": {
"from": "region",
"localField": "_id",
"foreignField": "user_id",
"as": "regions"
}
},
{
"$addFields": {
"regions": {
"$filter": {
input: "$regions",
as: "region",
cond: {
$in: [
"$$region.location_id",
[
"7777"
]
]
}
}
}
}
}
])
Actual Result (I am getting below result if I apply filter region_id: 7777)
[
{
"_id": "11111",
"name": "john",
"regions": [
{
"_id": "00013",
"user_id": "11111",
"location_id": "7777"
}
]
},
{
"_id": "11112",
"name": "smith",
"regions": [
{
"_id": "00014",
"user_id": "11112",
"location_id": "7777"
}
]
}
]
Don’t exactly understand what your filtering requirement, especially in the cases that no match on the input filtering list (e.g. [“6666”], so no region is matched). But here is what I guess you want to do with $filter: apply a $anyElementTrue after $lookup in the $filter.
db.user.aggregate([
{
"$lookup": {
"from": "region",
"localField": "_id",
"foreignField": "user_id",
"as": "regions"
}
},
{
"$set": {
"regions": {
"$filter": {
"input": "$regions",
"as": "r",
"cond": {
"$anyElementTrue": {
"$map": {
"input": "$regions",
"as": "rr",
"in": {
"$in": [
"$$rr.location_id",
[
// your input filter here
"7777",
"8888"
]
]
}
}
}
}
}
}
}
}
])
Mongo Playground
1
Instead of filtering regions, just match on region.location_id having any of the regions you’re filtering on. So if any array items have that, the whole document (with the full regions array) is returned.
db.user.aggregate([
{
"$lookup": {
"from": "region",
"localField": "_id",
"foreignField": "user_id",
"as": "regions"
}
},
{
$match: {
// your location filter here
"regions.location_id": { $in: ["7777", "8888"] }
}
}
])
Mongo Playground
2