M101J: MongoDB for Java Developers : Homework 2.2

Homework 2.2
Write a program in the language of your choice that will remove the grade of type "homework" with the lowest score for each student from the dataset that you imported in HW 2.1. Since each document is one grade, it should remove one document per student.

Solution:
the question say you need to write  your program to remove document of grade homework and lowest score for each student.

i will use java to solve above homework.

you have also got hint
Hint/spoiler: If you select homework grade-documents, sort by student and then by score, you can iterate through and find the lowest score for each student by noticing a change in student id. As you notice that change of student_id, remove the document.


First let us understand what we have to remove
1) hints say you need to find document with {"type":"homework"}  and then you need to sort by student_id in ascending order {"student_id":1} and then by score in ascending order {"score":1}

so your final query is
db.grades.find({"type":"homework"}).sort({"student_id":1},{"score":1})

so you will get below output  after executing above query



now the question say you need to remove each one document per student having lowest score and told us track on student_id. in the above result it is clear we need to remove first document for each student.

now let write a java program :

HomeWork.java

import java.net.UnknownHostException;

import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.MongoClient;

public class HomeWork {
    public static void main(String[] args) throws UnknownHostException {
        MongoClient client = new MongoClient("127.0.0.1");
        DB db = client.getDB("students");
        DBCollection collection = db.getCollection("grades");
        BasicDBObject searchQuery = new BasicDBObject();
        BasicDBObject sortQuery = new BasicDBObject();
        searchQuery.put("type", "homework");
        sortQuery.put("student_id", 1);
        sortQuery.put("score", 1);
        DBCursor cursor = collection.find(searchQuery).sort(sortQuery);
        System.out.println(cursor.count());
        BasicDBObject tempDocument = null;
        int i = 0;
        while (cursor.hasNext()) {
            BasicDBObject document = (BasicDBObject) cursor.next();
            System.out.println(document);
            if (tempDocument != null) {
                if (!document.get("student_id").equals(
                        tempDocument.get("student_id"))) {
                    collection.remove(document);
                    i++;
                }
            } else {
                collection.remove(document);
                i++;

            }
            tempDocument = document;

        }
        System.out.println(i);
    }
}


this will remove first document of all student.






you can cross check by

1) db.grades.count()  - 600

2) db.grades.find().sort({'score':-1}).skip(100).limit(1)
{ "_id" : ObjectId("50906d7fa3c412bb040eb709"), "student_id" : 100, "type" : "homework", "score" : 88.50425479139126 } 

3)db.grades.find({},{'student_id':1, 'type':1, 'score':1, '_id':0}).sort({'student_id':1, 'score':1, }).limit(5)
{ "student_id" : 0, "type" : "quiz", "score" : 31.95004496742112 }
{ "student_id" : 0, "type" : "exam", "score" : 54.6535436362647 }
{ "student_id" : 0, "type" : "homework", "score" : 63.98402553675503 }
{ "student_id" : 1, "type" : "homework", "score" : 44.31667452616328 }
{ "student_id" : 1, "type" : "exam", "score" : 74.20010837299897 }

if all 3 give you correct result then 

execute below query

db.grades.aggregate({'$group':{'_id':'$student_id', 'average':{$avg:'$score'}}}, {'$sort':{'average':-1}}, {'$limit':1})

you will get below result

{
        "result" : [
                {
                        "_id" : 54,
                        "average" : 96.19488173037341
                }
        ],
        "ok" : 1
}

So answer for homework 2.2 is 54