MongoDB Aggregation
MongoDB Aggregation (aggregate) is primarily used to process data (such as statistical averages, sums, etc.) and returns the computed data results. A bit like the count (*) in the SQL statement.
The basic syntax is:db. Collection. Aggregate( [ <stage1>, <stage2>, ... ] )
The following data is now available in the MyCol collection:
{"_id": 1, "name": "Tom", "Sex": "Male", "score": +, "age": 34}
{"_id": 2, "name": "Jeke", "Sex": "Male", "score": +, "age": 24}
{"_id": 3, "name": "Kite", "sex": "female", "score": +, "age": 36}
{"_id": 4, "name": "Herry", "Sex": "Male", "score": +, "age": 56}
{"_id": 5, "name": "Marry", "sex": "female", "score": +, "age": 18}
{"_id": 6, "name": "John", "Sex": "Male", "score": +, "age": 31}
1, $sum calculate the sum.
Sql:select Sex,count (*) from MyCol GROUP by sex
MongoDb:db.mycol.aggregate ([{$group: {_id: ' $sex ', Personcount: {$sum: 1}}])
Sql:select Sex,sum (Score) Totalscore from MyCol GROUP by sex
MongoDb:db.mycol.aggregate ([{$group: {_id: ' $sex ', Totalscore: {$sum: ' $score '}}])
2, $avg calculate the average
Sql:select Sex,avg (Score) Avgscore from MyCol GROUP by sex
Mongodb:db.mycol.aggregate ([{$group: {_id: ' $sex ', Avgscore: {$avg: ' $score '}}])
3. $max gets the maximum value for all documents in the collection.
Sql:select Sex,max (Score) Maxscore from MyCol GROUP by sex
Mongodb:db.mycol.aggregate ([{$group: {_id: ' $sex ', Maxscore: {$max: ' $score '}}])
4. $min gets the minimum value that corresponds to all documents in the collection.
Sql:select Sex,min (Score) Minscore from MyCol GROUP by sex
Mongodb:db.mycol.aggregate ([{$group: {_id: ' $sex ', Minscore: {$min: ' $score '}}])
5. $push insert values into an array for all data corresponding to a column in the document.
Mongodb:db.mycol.aggregate ([{$group: {_id: ' $sex ', scores: {$push: ' $score '}}])
6. $addToSet insert all data from a column in the document into an array, removing the duplicate
Db.mycol.aggregate ([{$group: {_id: ' $sex ', scores: {$addToSet: ' $score '}}])
7. $first get the first document data based on the ordering of the resource documents.
Db.mycol.aggregate ([{$group: {_id: ' $sex ', Firstperson: {$first: ' $name '}}])
8. $last get the last document data based on the sorting of the resource documents.
Db.mycol.aggregate ([{$group: {_id: ' $sex ', Lastperson: {$last: ' $name '}}])
The concept of piping
Pipelines are typically used in UNIX and Linux to use the output of the current command as a parameter to the next command.
MongoDB's aggregation pipeline passes the MongoDB document to the next pipeline processing after a pipeline has finished processing. Pipeline operations can be repeated.
Expression: Processes the input document and outputs it. The expression is stateless and can only be used to calculate the document for the current aggregated pipeline and cannot process other documents.
Here we introduce several common operations in the aggregation framework:
- $project: Modifies the structure of the input document. You can use it to rename, add, or delete fields, or to create calculations and nested documents.
- $match: Used to filter data and only output documents that match the criteria. $match a standard query operation using MONGODB.
- $limit: Used to limit the number of documents returned by the MongoDB aggregation pipeline.
- $skip: Skips the specified number of documents in the aggregation pipeline and returns the remaining documents.
- $unwind: Splits one of the array type fields in the document into multiple bars, each containing a value in the array.
- $group: Groups The documents in the collection to be used for statistical results.
- $sort: Sorts the input documents after the output.
- $geoNear: Outputs an ordered document that is close to a geographic location.
1. $project Example
Db.mycol.aggregate ({$project: {name:1, score:1}})
In this case, there are only _id,name and score three fields in the result, and by default _id fields are included, if you want to not include _id, you can:
Db.mycol.aggregate ({$project: {_id:0, name:1, score:1}})
2. $match Example
$match used to get records with a score greater than 30 less than and less than 100, and then send eligible records to the next stage $group pipeline operator for processing
Db.mycol.aggregate ([{$match: {score: {$gt: $lt: 100}}},{$group: {_id: ' $sex ', count:{$sum: 1}}])
MongoDB MongoDB Aggregation Group