Mongodb Aggregate Exercise2

MongoDB – Aggregation exercises - 2 In the collection tutorial you have the following data { _id: ObjectId(7df78ad8902c) title: 'MongoDB Overview', description: 'MongoDB is no sql database', by_user: 'tutorials point', url: 'http://www.tutorialspoint.com', tags: ['mongodb', 'database', 'NoSQL'], likes: 100 }, { _id: ObjectId(7df78ad8902d) title: 'NoSQL Overview', description: 'No sql database is very fast', by_user: 'tutorials point', url: 'http://www.tutorialspoint.com', tags: ['mongodb', 'database', 'NoSQL'], likes: 10 }, { _id: ObjectId(7df78ad8902e) title: 'Neo4j Overview', description: 'Neo4j is no sql database', by_user: 'Neo4j', url: 'http://www.neo4j.com', tags: ['neo4j', 'database', 'NoSQL'], likes: 750 },

1. Display a list stating how many tutorials are written by each user. db.tutorial.aggregate({$group:{_id:"$by_user",tutorial: {$push:"$title"},tutorial_count:{$sum:1}}})

2. Calculate and display the total likes, average likes, minimum and maximum likes for every user. db.tutorial.aggregate({$group:{_id:"$by_user",total_like: {$sum:"$likes"},avg_likes:{$avg:"$likes"},min_likes: {$min:"$likes"},max_likes:{$max:"$likes"}} 3.

Retrieve the url of each user as an array. db.tutorial.aggregate({$group:{_id:"$by_user",tutorial: {$push:"$url"}}})

Expression

$sum

$avg

$min

$max

$push

$addToSet

$first

Description

Example

Sums up the defined value from all documents in the collection.

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])

Calculates the average of all given values from all documents in the collection.

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}])

Gets the minimum of the corresponding values from all documents in the collection.

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}])

Gets the maximum of the corresponding values from all documents in the collection.

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}])

Inserts the value to an array in the resulting document.

db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}])

Inserts the value to an array in the resulting document but does not create duplicates.

db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}])

Gets the first document from the source documents according to the grouping. Typically this makes only sense together with some previously applied “$sort”-stage.

db.mycol.aggregate([{$group : {_id : "$by_user", first_url : {$first : "$url"}}}])

$last

Gets the last document from the source documents according to the grouping. Typically this makes only sense together with some previously applied “$sort”-stage.

db.mycol.aggregate([{$group : {_id : "$by_user", last_url : {$last : "$url"}}}])

Pipeline Concept In UNIX command, shell pipeline means the possibility to execute an operation on some input and use the output as the input for the next command and so on. MongoDB also supports same concept in aggregation framework. There is a set of possible stages and each of those is taken as a set of documents as an input and produces a resulting set of documents (or the final resulting JSON document at the end of the pipeline). This can then in turn be used for the next stage and so on. Following are the possible stages in aggregation framework − $project − Used to select some specific fields from a collection. $match − This is a filtering operation and thus this can reduce the amount of documents that are given as input to the next stage. $group − This does the actual aggregation as discussed above. $sort − Sorts the documents. $skip − With this, it is possible to skip forward in the list of documents for a given amount of documents. $limit − This limits the amount of documents to look at, by the given number starting from the current positions. $unwind − This is used to unwind document that are using arrays. When using an array, the data is kind of pre-joined and this operation will be undone with this to have individual documents again. Thus with this stage we will increase the amount of documents for the next stage.

Data in JSON format, shows the hosting provider for website. website.json { "_id" : 1, "domainName" : "test1.com", "hosting" : "hostgator.com" } { "_id" : 2, "domainName" : "test2.com", "hosting" : "aws.amazon.com"} { "_id" : 3, "domainName" : "test3.com", "hosting" : "aws.amazon.com" } { "_id" : 4, "domainName" : "test4.com", "hosting" : "hostgator.com" } { "_id" : 5, "domainName" : "test5.com", "hosting" : "aws.amazon.com" } { "_id" : 6, "domainName" : "test6.com", "hosting" : "cloud.google.com" } { "_id" : 7, "domainName" : "test7.com", "hosting" : "aws.amazon.com" } { "_id" : 8, "domainName" : "test8.com", "hosting" : "hostgator.com" } { "_id" : 9, "domainName" : "test9.com", "hosting" : "cloud.google.com" } { "_id" : 10, "domainName" : "test10.com", "hosting" : "godaddy.com" }

4. Display the total number of documents in each hosting. db.hosting.aggregate({$group:{_id:"$hosting",total_documents:{$sum:1}}}) 5. Display the total number of documents for each hosting in descending order of total number. db.hosting.aggregate({$group:{_id:"$hosting",total_documents: {$sum:1}}},{$sort:{"total_documents":-1}}) 6. Display the top five hosting by total number of documents. db.hosting.aggregate({$group:{_id:"$hosting",total_documents:{$sum:1}}}, {$sort:{"total_documents":-1}},{$limit:3}) 7. Calculate the number of hosting done by “aws.amazon.com” only. db.hosting.aggregate({$match:{hosting:"aws.amazon.com"}},{$group: {_id:"$hosting",total_documents:{$sum:1}}})

References

1. 2. 3. 4.

MongoDB Aggregation MongoDB db.collection.aggregate() Aggregation Pipeline Limits MongoDB Hello World Example

Tags : group mongodb sort

Documents { "id": "1", "firstName": "Jane", "lastName": "Doe", "phoneNumber": "555-555-1212", "city": "Beverly Hills", "state: "CA", "zip": 90210 "email": "[email protected]" } 8. Find all users that live in the area with 90210 area code. db.area.aggregate([{$match:{"zip":90210}},{$project: {"zip":1,"firstName":1}}]); 9. Find the number of users that live in the area with 90212 area code. db.area.aggregate([{$match:{"zip":90212}},{$group:{"_id":"$zip","count": {$sum:1}}}]); 10. Find the total number of users. db.area.aggregate([{$group:{"_id":"$firstName","user_count":{$sum:1}}}]); 11. Retrieve the documents whose zip code is 90210 db.area.aggregate([{$match:{"zip":90210}}]); 12. Retrieve First name, city, zip code and email of the documents whose state is “CA” db.area.aggregate([{$match:{"state":"CA"}},{$project: {firstName:1,city:1,zip:1,email:1}}]); 13. Find the number of documents whose zip code is 90210 . db.area.aggregate([{$match:{"zip":90210}},{$group:{"_id":"$zip","count": {$sum:1}}}]);

Our transaction model looks like this: { "id": "1", "productId": "1", "customerId": "1", "amount": 20.00, "transactionDate": ISODate("2017-02-23T15:25:56.314Z") }

9. Calculate the total amount of sales made for the month of January. 10. Calculate the total amount of sales, average, minimum, maximum transaction amount made for the month of January. 11. Calculate the total amount of sales, average, minimum, maximum transaction amount product wise. 12. Calculate the total amount of sales, average, minimum, maximum transaction amount customer wise. 13. Calculate the total amount of sales, average, minimum, maximum transaction amount product wise made for the month of February. 14. Calculate the total amount of sales, average, minimum, maximum transaction amount customer wise made for the month of February.

Now suppose we have a School database and have a Student Collection as below db.Student.insert({StudentName : "Vijay",Section : "A",Marks:70,Subject:["Hindi","English","Math"]}) db.Student.insert({StudentName : "Gaurav",Section : "A",Marks:90,Subject:["English"]}) db.Student.insert({StudentName : "Ajay",Section : "A",Marks:70,Subject:["Math"]}) db.Student.insert({StudentName : "Ankur",Section : "B",Marks:10,Subject:["Hindi"]}) db.Student.insert({StudentName : "Sunil",Section : "B",Marks:70,Subject:["Math"]}) db.Student.insert({StudentName : "Preeti",Section : "C",Marks:80,Subject:["Hindi","English"]}) db.Student.insert({StudentName : "Anuj",Section : "C",Marks:50,Subject:["English"]}) db.Student.insert({StudentName : "Palka",Section : "D",Marks:40,Subject:["Math"]}) db.Student.insert({StudentName : "Soniya",Section : "D",Marks:20,Subject:["English","Math"]}) db.Student.insert({StudentName : "Tarun",Section : "A",Marks:95,Subject:[]}) db.Student.insert({StudentName : "Saurabh",Section : "A",Marks:95})

1. Find out all the documents where Section is A and Marks is greater then 80/ 2. Findout StudentName,ection and Marks from Student Collectionwhere Section is 'A'. 3. Display a document for each subject for the student ‘Vijay’ only the fields StudentName,Marks,Subject 4. Include the array index in the previous query. 5. Findout Total Marks group by Section. 6. Find out Total Marks for Section A 7. Fetchthe count of students in each section and Total marks and average marks as well

8. Renamethe column Names in above query(Section to SectionName and TotalMarls to Total) 9. Sort the result of previous query as follows i) ii)

by Section name by descending order of total marks

10. Display the top 3 sections (make use of previous query) 11. Display the second and third topper of each section.