I have a list of students in one collection and their grades in another collection. The schema (stripped of other details) look like
Students
{
_id: 1234,
student: {
code: "WUKD984KUK"
}
}
Grades
{
_id: 3456,
grade: 24,
studentCode: "WUKD984KUK"
}
There can be multiple grade entries for each student. I need a total count of students who are present in grades table but not in the student table. I also need a count of grades for each student who are not in the student table. The following is the query I had written,
var existingStudents = db.students.find({}, {_id: 0, 'student.code': 1});
db.grades.aggregate(
{$match: { 'studentCode': {'$nin': existingStudents}}},
{$group: {_id: '$studentCode', count:{$sum: 1}}},
{$project: {tmp: {code: '$_id', count: '$count'}}},
{$group: {_id: null, total:{$sum:1}, data:{$addToSet: '$tmp'}}}
);
But this returns me all of the student details as if the match is not working. When I run just the first part of this query, I get the student details as
{ "student" : { "code" : "A210225504104" } }
I feel that because the return value is two deep the match isnt working. What is the right way to get this?
Use this code
var existingStudents=[];
db.students.find({}, {_id: 0, 'student.code': 1}).forEach(function(doc){existingStudents.push(doc.student.code)})
db.grades.aggregate(
{$match: { 'studentCode': {'$nin': existingStudents}}},
{$group: {_id: '$studentCode', count:{$sum: 1}}},
{$project: {tmp: {code: '$_id', count: '$count'}}},
{$group: {_id: null, total:{$sum:1}, data:{$addToSet: '$tmp'}}}
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With