Good Morning:
I have the following MongoDB structure:
{
"_id": 19,
"name": "Fredia Donan",
"faculty": "Zoomzone",
"lectures": [
{
"lecturerID": 25,
"name": "Sigismondo Brecknell",
"email": "[email protected]",
"imparts": [
{
"_id": 76,
"codCourse": 23,
"language": "Malay",
"max_students": 59,
"students": [
{
"studentID": 25
}
],
"classes": [
{
"date": ISODate("2022-02-02T09:23:32.59"),
"cod_classroom": 76
}
]
}
]
}
]
}
Having that I want to find the lecturer that imparts class to the maximum number of students. So I have to count the number of students that a lecturer teaches to and the find the max from across all lecturers from the department. How could I do it? Can it be done within the same code of the aggregation bellow? It is way over my MongoDB knowledge and I would be forever grateful if anyone could help.
I have read about $size and $count but everywhere I try it gives me different errors.
Thank you very much in advance!
The output above is done by an aggregation of two collections department and group with the following code:
[{"$unwind": "$lecturers"}, {"$unwind": "$lecturers.imparts"},
{"$lookup":
{"from": "group",
"localField": "lecturers.imparts.groupID",
"foreignField": "_id",
"as": "lecturers.imparts"}},
{"$group":
{"_id": {"_id": "$_id", "lecturersID": "$lecturers.lecturerID"},
"name": {"$first": "$name"},
"faculty": {"$first": "$faculty"},
"lecturers":
{"$first":
{"lecturerID": "$lecturers.lecturerID",
"name": "$lecturers.name",
"email": "$lecturers.email"}},
"imparts": {"$push": "$lecturers.imparts"}}},
{"$set":
{"lecturers":
{"$mergeObjects":
["$lecturers",
{"imparts":
{"$reduce":
{"input": "$imparts",
"initialValue": [],
"in": {"$concatArrays": ["$$value", "$$this"]}}}}]},
"imparts": "$$REMOVE"}},
{"$group":
{"_id": "$_id._id",
"name": {"$first": "$name"},
"faculty": {"$first": "$faculty"},
"lectures": {"$push": "$lecturers"}}}])
My desired output would be to get the lecturer of each department that teaches the most students. For instance, if I have this:
{
"_id": 19,
"name": "Fredia Donan",
"faculty": "Zoomzone",
"lectures": [
{
"lecturerID": 25,
"name": "Sigismondo Brecknell",
"email": "[email protected]",
"imparts": [
{
"_id": 76,
"codCourse": 23,
"language": "Malay",
"max_students": 59,
"students": [
{
"studentID": 25
}
],
"classes": [
{
"date": ISODate("2022-02-09T23:32:59.000Z"),
"cod_classroom": 76
}
]
},
{
"_id": 77,
"codCourse": 24,
"language": "Malayff",
"max_students": 59,
"students": [
{
"studentID": 28
}
],
"classes": [
{
"date": ISODate("2022-02-09T23:32:59.000Z"),
"cod_classroom": 77
}
]
}
]
},
{
"lecturerID": 36,
"name": "Sigismondo Brecknell",
"email": "[email protected]",
"imparts": [
{
"_id": 76,
"codCourse": 23,
"language": "Malay",
"max_students": 59,
"students": [
{
"studentID": 45
}
],
"classes": [
{
"date": ISODate("2022-02-09T23:32:59.000Z"),
"cod_classroom": 76
}
]
},
{
"_id": 77,
"codCourse": 24,
"language": "Malayff",
"max_students": 59,
"students": [
{
"studentID": 54
},
{
"studentID": 435
},
{
"studentID": 45
}
],
"classes": [
{
"date": ISODate("2022-02-09T23:32:59.000Z"),
"cod_classroom": 77
}
]
}
]
}
]
}
]
I would like to get that for the department with _id 19 the lecturer that teaches the most students is the one with id 36 since he imparts class to 4 students while the lecturer with id 25 imparts class to only 2 students.
So I would like to get the following output:
"_id": 19,
"name": "Fredia Donan",
"lecturerID": 36,
"maxImpartsStudents": 4
}
And I would like to get this information for every existing department. Every document in the collection department
CodePudding user response：
Welcome Cristina Aranda!
EDIT:
- For each
lecturer, iterate allimpartsusing$mapand creates one set that includes all students, returning its size asmaxImpartsStudents. We use$setUnionto avoid counting the same student more than once, if they are in more than one class of the same lecturer. $reducethelecturersin each document to include only the one with maximal value ofmaxImpartsStudents.- Format
db.collection.aggregate([
{
$project: {
name: 1,
lectures: {
$map: {
input: "$lectures",
as: "item",
in: {
$mergeObjects: [
{
maxImpartsStudents: {
$size: {
$reduce: {
input: "$$item.imparts",
initialValue: [],
in: {$setUnion: ["$$value", "$$this.students"]}
}
}
}
},
{
name: "$$item.name",
lecturerID: "$$item.lecturerID"
}
]
}
}
}
}
},
{
$set: {
lectures: {
$reduce: {
input: "$lectures",
initialValue: {maxImpartsStudents: 0},
in: {
$cond: [
{$gte: ["$$this.maxImpartsStudents", "$$value.maxImpartsStudents"]},
"$$this", "$$value"]
}
}
}
}
},
{
$project: {
lecturerID: "$lectures.lecturerID",
maxImpartsStudents: "$lectures.maxImpartsStudents",
departmentName: "$name"
}
}
])
See how it works on the playground example