Mongodb aggregate query with not in

Question

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?

db.students.find({}, {_id: 0, 'student.code': 1}) does not give you array of student codes. It gives you array of documents containing field studentCodes.So $nin is not recognizing it. — mallik
– mallik, Commented Sep 30, 2014 at 10:02

Mallikarjuna J S · Accepted Answer · 2018-02-24 19:09:46Z

20

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

edited Feb 24, 2018 at 19:09

Mallikarjuna J S

1307 bronze badges

answered Sep 30, 2014 at 10:06

mallik

5675 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Balasubramani M Over a year ago

@mallik, $nin operator uses array format. update your answer in the following format. { $nin: [ 'value1', 'value2' ]}

Ali Abbas Over a year ago

If you are using _id in or not in , kindly use this => ids = ids.map(function(el) { return mongoose.Types.ObjectId(el) })

Collectives™ on Stack Overflow

Mongodb aggregate query with not in

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related