CODEWITHSUNDEEP
mongodbaggregation-framework
etrast81
etrast81

Reputation: 300

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?

Upvotes: 10

Views: 17593

Answers (1)

mallik
mallik

Reputation: 567

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'}}} 
);

Upvotes: 20

Related Questions