Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongodb aggregate query with not in

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?

like image 689
etrast81 Avatar asked Sep 30 '14 09:09

etrast81


1 Answers

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'}}} 
);
like image 103
mallik Avatar answered Sep 22 '22 04:09

mallik