14. MongoDB : Custom Search
Sample Data
{
"_id":0,
"name":"Pavan",
"results": [
{"evaluation":"term1","score":25.46},
{"evaluation":"term2","score":11.78},
{"evaluation":"term3","score":56.67}
]
}
1.
Find count and percentage of employees who
failed in term 1, the passing score being 37
2.
Find employees who failed in aggregate (term1 +
term2 + term3)
3.
Find the Average score of trainees for term1
4.
Find the Average score of trainees for aggregate
(term1 + term2 + term3)
5.
Find number of employees who failed in all the three
(term1 + term2 + term3)
1.
Find the number of employees who failed
in any of the three (term1 + term2 + term3)
SSample data:
cDatabase - "employee"
Collection - "training"
{"_id":0,"name":"Pavan","results":[{"evaluation":"term1","score":1.463179736705023},{"evaluation":"term2","score":11.78273309957772},{"evaluation":"term3","score":6.676176060654615}]}
{"_id":1,"name":"Vijay","results":[{"evaluation":"term1","score":60.06045071030959},{"evaluation":"term2","score":52.79790691903873},{"evaluation":"term3","score":71.76133439165544}]}
{"_id":2,"name":"amar","results":[{"evaluation":"term1","score":67.03077096065002},{"evaluation":"term2","score":6.301851677835235},{"evaluation":"term3","score":20.18160621941858}]}
{"_id":3,"name":"Girish","results":[{"evaluation":"term1","score":71.64343899778332},{"evaluation":"term2","score":24.80221293650313},{"evaluation":"term3","score":1.694720653897219}]}
{"_id":4,"name":"Shiv","results":[{"evaluation":"term1","score":78.68385091304332},{"evaluation":"term2","score":90.2963101368042},{"evaluation":"term3","score":34.41620148042529}]}
{"_id":5,"name":"Shobith","results":[{"evaluation":"term1","score":44.87186330181261},{"evaluation":"term2","score":25.72395114668016},{"evaluation":"term3","score":10.53058536508186}]}
{"_id":6,"name":"Bhavana","results":[{"evaluation":"term1","score":37.32285459166097},{"evaluation":"term2","score":28.32634976913737},{"evaluation":"term3","score":16.58341639738951}]}
{"_id":7,"name":"Monica","results":[{"evaluation":"term1","score":90.37826509157176},{"evaluation":"term2","score":42.48780666956811},{"evaluation":"term3","score":67.18328596625217}]}
################################################################################
package com.mongodb.customsearch;
import static java.util.Arrays.asList;
import java.util.ArrayList;
import java.util.Collection;
import org.bson.Document;
import com.mongodb.Block;
import com.mongodb.MongoClient;
import com.mongodb.client.AggregateIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
public class EmployeeTrainingScores {
public static void main(String[] args) {
try{
// Creating mongo client default connection host:localhost port:12027
//MongoClient mongoClient = new MongoClient();
MongoClient mongoClient = new MongoClient("localhost", 27017);
// fetching a database with name students
MongoDatabase db = mongoClient.getDatabase("employee");
// fetching collection scores from database students
MongoCollection collection = db.getCollection("training");
//find one record
findFailedStudInTerm1(collection);
//failed employees in aggregate (term1+term2+term3)
failedInAggregate(collection);
//average score of trainees in term1
averageScoreTerm1(collection);
//Average score of class for aggregate (term1 + term2 + term3)
averageClassScore(collection);
//count of employee failed in all three terms
employeeCountFailInAlLTerms(collection);
//count of employee failed in either of three terms
employeeCountFailAtleastATerm(collection);
mongoClient.close();
}catch(Exception exception){
System.err.println(exception.getClass().getName() + ":" + exception.getMessage());
}
}
/**
* function to find a record input : mongodb collection
* 1. Find count and percentage of employees who failed in term 1, the passing score being 37
*/
public static Long findFailedStudInTerm1(MongoCollection<Document> collection) {
Long count = collection.count(new Document("results.evaluation", "term1").append("results.score", new Document("$lt",37)));
Long totalStudents=collection.count();
Long per_Stud=(count*100/totalStudents);
System.out.println("############################# 1. Find count and percentage of employees who failed in term 1, the passing score being 37 #############################");
System.out.println("Number of students failed in exams in Term1: passing marks 37 ======> " + count);
System.out.println("Percentage of students failed in exams in Term1: passing marks 37 ======> " + per_Stud +" %");
return per_Stud;
}
/**
* function to find a record input : mongodb collection
* 2. Find employees who failed in aggregate (term1 + term2 + term3)
*/
private static void failedInAggregate(MongoCollection collection) {
System.out.println("");
System.out.println("############################# 2. Find employees who failed in aggregate (term1 + term2 + term3) #############################");
//term1 + term2 + term3
//37 + 37 + 37 = 111
Collection<Document> employees = collection.aggregate(asList(new Document("$unwind","$results"),new Document("$group", new Document("_id","$name").append("total", new Document("$sum","$results.score"))),new Document("$match", new Document("total",new Document("$lt",111))))).into(new ArrayList<Document>());
for(Document doc:employees){
System.out.println("employees who failed in aggregate (term1+ term2 + term3) : " + doc.toJson());
}
}
/**
* function to find a record input : mongodb collection
* 3. Find the Average score of trainees for term1
*/
private static void averageScoreTerm1(MongoCollection collection) {
System.out.println("");
System.out.println("############################# 3. Find the Average score of trainees for term1 #############################");
Collection<Document> employees = collection.aggregate(asList( new Document("$unwind","$results"), new Document("$match",new Document("results.evaluation","term1")),new Document("$group",new Document("_id",null).append("Average", new Document("$avg","$results.score"))))).into(new ArrayList<Document>());
for(Document doc:employees){
System.out.println("Average score of trainees for term1 : " + doc.toJson());
}
}
/**
* function to find a record input : mongodb collection
* 4. Find the Average score of trainees for aggregate (term1 + term2 + term3)
*/
private static void averageClassScore(MongoCollection collection) {
System.out.println("");
System.out.println("############################# 4. Find the Average score of trainees for aggregate (term1 + term2 + term3) #############################");
Collection<Document> employees = collection.aggregate(asList(new Document("$unwind","$results"),new Document("$group", new Document("_id","$name").append("Average", new Document("$avg","$results.score"))))).into(new ArrayList<Document>());
for(Document doc:employees){
System.out.println("Average score of trainees for aggregate (term1+ term2 + term3) : " + doc.toJson());
}
}
/**
* function to find a record input : mongodb collection
* 5. Find number of employees who failed in all the three (term1 + term2 + term3)
*/
private static void employeeCountFailInAlLTerms(MongoCollection collection) {
System.out.println("");
System.out.println("############################# 5. Find number of employees who failed in all the three (term1 + term2 + term3) #############################");
Long count=collection.count(new Document("results.0.score",new Document("$lt",37)).append("results.1.score",new Document("$lt",37)).append("results.2.score",new Document("$lt",37)));
System.out.println("Count of employees failing in all terms : " + count);
}
/**
* function to find a record input : mongodb collection
* 6. Find the number of employees who failed in any of the three (term1 + term2 + term3)
*/
private static void employeeCountFailAtleastATerm(MongoCollection collection) {
System.out.println("");
System.out.println("############################# 6. Find the number of employees who failed in any of the three (term1 + term2 + term3) #############################");
Long count=collection.count(new Document("$or",asList(new Document("results.0.score",new Document("$lt",37)), new Document("results.1.score",new Document("$lt",37)),new Document("results.2.score",new Document("$lt",37)))));
System.out.println("Count of employees failing in either of the terms : " + count);
}
}
################################################################################
