I'm new in mongoose. I tried many queries but couldn't get correct result. My schema looks like this:
{
course_name: "Test 1",
students: [
{name: "Sam", age: 5},
{name: "Don", age: 7}
]
},
{
course_name: "Test 2",
students: [
{name: "Paul", age: 7}
]
}
In this example I have 2 documents. Every course document contains students data inside of it. I'm trying to find all students in all courses which their ages are 7. But in queries I tried, it returns Sam too.
Here's an example of which I'm expecting:
{name: "Paul", age: 7},
{name: "Don", age: 7}
And here's what I'm getting.
{name: "Paul", age: 7},
{name: "Sam", age: 5},
{name: "Don", age: 7}
My query:
const getStudents = await Courses.aggregate([
{$match: {'students.age': myAge}},
{$unwind: '$students'},
{$project: {name: '$students.name', age: '$students.age'}}
]);
Thanks for your time
Is a common mistake think that if you match by a nested value (in this case students.age) you will get the nested value you want but it doesn't work like this.
You are doing $match query before $unwind so you are getting all documents where exists any object into students array where age is 7. That is, the whole document1 pass to the next stage. The array students with two values go to next tsage because one of them match the criteria and then, the object is matched.
So, to be clearer, you are doing a $match stage where you are saying: "Give me all documents where exist an student which age is 7". Then, the first document match the criteria because one of the students match the filter, and is returned the whole document, not only the object in the array that matches.
You can match after the unwind and you will get the desired output.
Example here
@J.F. does a good job explaining why your approach is not correct. Anyway i want to show you how you can achieve the same result using the $filter.
Selects a subset of an array to return based on the specified condition. Returns an array with only those elements that match the condition. The returned elements are in the original order.
db.collection.aggregate({
$addFields: {
students: {
$filter: {
input: "$students",
as: "student",
cond: {
$eq: [
"$$student.age",
7
]
}
}
}
}
})
Result:
[
{
"_id": ObjectId("5a934e000102030405000000"),
"course_name": "Test 1",
"students": [
{
"age": 7,
"name": "Don"
}
]
},
{
"_id": ObjectId("5a934e000102030405000001"),
"course_name": "Test 2",
"students": [
{
"age": 7,
"name": "Paul"
}
]
}
]