I have two collections:
- Courses
- Students
I want to get details of students that are assigned to courses in related courses object.
E.g here are my collections and data
Courses:
[
{
_id: 1,
title: "Maths",
studentGroups: [
{
group: 'A',
students: [1, 5]
},
{
group: 'B',
students: [3]
}
]
},
{
_id: 2,
title: "Chemistry",
studentGroups: [
{
group: 'C',
students: [2]
},
{
group: 'B',
students: [4]
}
]
}
]
Students:
[
{
_id: 1,
name: 'Henry',
age: 15
},
{
_id: 2,
name: 'Kim',
age: 20
},
{
_id: 3,
name: 'Michel',
age: 14
},
{
_id: 4,
name: 'Max',
age: 16
},
{
_id: 5,
name: 'Nathan',
age: 19
}
]
Now I want this response:
[
{
_id: 1,
title: "Maths",
studentGroups: [
{
group: 'A',
students: [
{
_id: 1,
name: 'Henry',
age: 15
},
{
_id: 5,
name: 'Nathan',
age: 19
}
]
},
{
group: 'B',
students: [
{
_id: 3,
name: 'Michel',
age: 14
}
]
}
]
},
{
_id: 2,
title: "Chemistry",
studentGroups: [
{
group: 'C',
students: [
{
_id: 2,
name: 'Kim',
age: 20
}
]
},
{
group: 'B',
students: [
{
_id: 4,
name: 'Max',
age: 16
}
]
}
]
}
]
What I have tried gives my the correct result but it is not a good approach, I need to achieve this by single aggregation query
My solutions:
var courses = Courses.find({}).fetch();
courses.forEach(c => {
if (c.studentGroups && c.studentGroups.length) {
c.studentGroups.forEach(s => {
s.students = Students.find({_id: {$in: s.students}}).fetch()
})
}
})
Can anyone suggest me a better solution with single aggregation query?
CodePudding user response：
First $unwind the studentGroups. Then, $lookup from students. Finally, regroup the results using $group`.
db.courses.aggregate([
{
"$unwind": "$studentGroups"
},
{
"$lookup": {
"from": "students",
"localField": "studentGroups.students",
"foreignField": "_id",
"as": "studentGroups.students"
}
},
{
$group: {
_id: "$_id",
title: {
$first: "$title"
},
studentGroups: {
$push: "$studentGroups"
},
description: {
$first: "$description"
},
timeline: {
$first: "$timeline"
}
}
}
])