14. MongoDB : Custom Search

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

}

}

################################################################################