Aggregate functions allow you to group together the documents and performs some computation on them, so that a single document is been return. .
Retrieve the document from the database
Note: We are using testDB database, and from the previous tutorial we are using collection as testCollection
1) Open a new command Prompt.
2) Enter “mongod“, and press ENTER (it will start your mongodb server. Don’t close it)
3) Open Robo3T.
4) Right click on Connection name.
5) Open shell.
6)Write “use testDB“, and press CTRL+ENTER. It will switch to testDB database.
7) Write “db.testCollection.find({})” , and press CTRL+ENTER.
Output Below
{“Emp.No”:”1”, “FirstName”:”Shubh”, “Age”:”20”,“Gender”:”Male”,“Salary”:”40000”}
{“Emp.No”:”2”,“FirstName”:”Honey”“Age”:”21”,“Gender”:”Male”,“Salary”:”10000”}
{“Emp.No”:”3”,“FirstName”:”Apoorv”“Age”:”20”,“Gender”:”Male”,“Salary”:”50000”}
{“Emp.No”:”4”,“FirstName”:”Ayush”,“Age”:”21”,“Gender”:”Male”,“Salary”:”30000”}
{“Emp.No”:”5”,“FirstName”:”Ashish”,“Age”:”20”,“Gender”:”Male”,“Salary”:”16000”}
{“Emp.No”:”6”,“FirstName”:”Bharat”,“Age”:”21”,“Gender”:”Male”,“Salary”:”51000”}
Aggregate In MongoDB
Mongodb command is : “db.collectionName.aggregate(Aggregate_Operation)”
There are various aggregate expressions in Mongodb:
1) $sum – It sums up the defined value from all documents in the collection.
Example – db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$sum : "$targetValue"}}}])
2) $avg – It calculates the average of all given values from all documents in the collection.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$avg : "$ targetValue”}}}])
3) $min – It gets the minimum of the corresponding values from all documents in the collection.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$min : "$ targetValue "}}}])
4) $max – It gets the maximum of the corresponding values from all documents in the collection.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$max : "$ targetValue "}}}])
5) push – It inserts the value to an array in the resulting document.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$push: "$ targetValue "}}}])
6) $addToSet – It inserts the value to an array in the resulting document but does not create duplicates.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName : {$addToSet : "$ targetValue "}}}])
7) $first – It gets the first document from all the source documents according to their grouping.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$first : "$ targetValue "}}}])
8) $last – It gets the document from all the source documents according to their grouping.
Example – db.collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$last : "$ targetValue "}}}])
indexValue is been the target value or unique value which is use for identifying the particular document from the collection. It is given under the $id field.
fieldName is the new field name that you want to give after the aggregate function is being properly processed its calculations.
targetValue is being the field in a collection through which you want to use the various aggregate functions. The field which actually want to perform aggregate functions.
SUM in MongoDB – $sum in MongoDB
It sums up the defined value from all documents in the collection.
MongoDB command is: “db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$sum : "$targetValue"}}}])”
Example: Retrieve how many male females are there in testCollection.
indexValue = Gender
fieldname = Total
targetValue = 1(as we have to increase the counter by 1 at each document is being fetched)
MongoDb command is: “db.testCollection.aggregate([{$group : {_id : "$Gender", Total : {$sum : 1}}}])”
Write MongoDB command in shell and press CTRL+ENTER.
It will give output as:
{“_id”:”Male”, “Total”: 6}
{“_id”:”Female”, “Total”: 0}
Average in MongoDB – $avg in MongoDB
It calculates the average of all given values from all documents in the collection.
MongoDB command is: “db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$avg : "$targetValue"}}}])”
Example: Retrieve the average salary of all the male and females employee in testCollection.
indexValue = Gender
fieldname = Average_Salary
targetValue = Salary
MongoDb command is: “db.testCollection.aggregate([{$group : {_id : "$Gender", Average_Salary: {$avg: “$Salary“}}}])”
Write MongoDB command in shell and press CTRL+ENTER.
It will give output as:
{“_id”:”Male”, “Average_Salary”: 32833.33}
{“_id”:”Female”, “Average_Salary”: 0}
MAX inMongoDB – $max in MongoDB
It gets the maximum of the corresponding values from all documents in the collection.
MongoDB command is: “db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$max : "$targetValue"}}}])”
Example: Retrieve the Maximum salary among all the male and female employee in testCollection.
indexValue = Gender
fieldname = Maximum
targetValue = Salary
MongoDb command is: “db.testCollection.aggregate([{$group : {_id : "$Gender", Maximum: {$max: “$Salary“}}}])”
Write MongoDB command in shell and press CTRL+ENTER.
It will give output as:
{“_id”:”Male”, “Maximum”: 51000}
{“_id”:”Female”, “Maximum”: 0}
Minimum in MongoDB – $min in MongoDB
It gets the minimum of the corresponding values from all documents in the collection.
MongoDB command is: “db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$min : "$targetValue"}}}])”
Write MongoDB command in shell and press CTRL+ENTER.
Example: Retrieve the Minimum salary among all the male and female employee in testCollection.
indexValue = Gender
fieldname = Minimum
targetValue = Salary
MongoDb command is: “db.testCollection.aggregate([{$group : {_id : "$Gender", Minimum: {$min: “$Salary“}}}])”
It will give output as:
{“_id”:”Male”, “Minimum”: 16000}
{“_id”:”Female”, “Minimum”: 0}
Push in MongoDB – $push in MongoDB
It inserts the value to an array in the resulting document.
MongoDB command is: “db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$push : "$targetValue"}}}])”
Write MongoDB command in shell and press CTRL+ENTER.
Example: You have four documents in your testCollection.
{ "_id": 1, "user" : "Jan", "age" : 25, "score": 80 }
{ "_id": 2, "user" : "Mel", "age" : 35, "score": 70 }
{ "_id": 3, "user" : "Ty", "age" : 20, "score": 102 }
{ "_id": 4, "user" : "Lee", "age" : 25, "score": 45 }
->To group by age and return all the firstName values for each age, use the $push operator.
indexValue = Age
fieldname = First_Name
targetValue = FirstName
MongoDb command is: “db.testCollection.aggregate([{$group : {_id : "$ age ", users: {$avg: “$user “}}}])”
It will give output as:
{
"result" : [
{ "_id" : 20, "users" : [ "Ty" ] },
{ "_id" : 35, "users" : [ "Mel" ] },
{ "_id" : 25, "users" : [ "Jan", "Lee" ] } ],
"ok" : 1
}
$addToSet in MongoDB
It inserts the value to an array in the resulting document but does not create duplicates.
MongoDB command is: “db.collectionName.aggregate([{$group : {_id : "$indexValue", fieldName : {$addToSet : "$targetValue"}}}])”
Example: You have four documents in your testCollection.
{ "type" : "phone", "price" : 389.99, "stocked" : 270000 },
{ "type" : "phone", "price" : 376.99 , "stocked" : 97000},
{ "type" : "phone", "price" : 389.99 , "stocked" : 97000},
{ "type" : "chair", "price" : 59.99, "stocked" : 108 }
->Return all the documents that are grouped on the value of the type field, having the sets of unique values of the prices and stocked in the input documents.
Mongodb command is: “db.testCollection.aggregate( { $group : { _id : "$type", price: { $addToSet: "$price" }, stocked: { $addToSet: "$stocked" }, } } )”
Write MongoDB command in shell and press CTRL+ENTER.
It will give output as:
{ "_id" : "chair",
"price" : [
59.99 ],
"stocked" : [
108
] },
{ "_id" : "phone",
"price" : [
376.99, 389.99
],
"stocked" : [
97000,
270000,
] }
$first in MongoDB
It gets the first document from all the source documents according to their grouping.
MongoDB command is: “db. collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$first : "$ targetValue "}}}])”
Note: Only use $first when the $group follows a $sort operation. Otherwise, the result of this operation is unpredictable.
$last in MongoDB
It gets the last document from all the source documents according to their grouping.
MongoDB command is: “db. collectionName.aggregate([{$group : {_id : "$ indexValue ", fieldName: {$ last: "$ targetValue "}}}])”
Note: Only use $ last when the $group follows a $sort operation. Otherwise, the result of this operation is unpredictable.
If you have any queries regarding aggregate functions in MongoDB, drop a comment below.