mongodb中的aggregation的使用
$group (aggregation)
On this page
DefinitionConsiderations
Examples
Additional Resources
Definition
- $group
-
Groups documents by some specified expression and outputs to the next stage a document for each distinct grouping. The output documents contain an _id field which contains the distinct group by key. The output documents can also contain computed fields that hold the values of some accumulator expression grouped by the $group‘s _id field. $group does not order its output documents.
The $group stage has the following prototype form:
{ $group: { _id: , : { : }, ... } }
The _id field is mandatory; however, you can specify an _id value of null to calculate accumulated values for all the input documents as a whole.
The remaining computed fields are optional and computed using the
operators. The _id and the
expressions can accept any valid expression. For more information on expressions, see Expressions. Considerations
Accumulator Operator
The
operator must be one of the following accumulator operators: Name Description $sum Returns a sum of numerical values. Ignores non-numeric values.
Changed in version 3.2: Available in both $group and $project stages.
$avg Returns an average of numerical values. Ignores non-numeric values.
Changed in version 3.2: Available in both $group and $project stages.
$first Returns a value from the first document for each group. Order is only defined if the documents are in a defined order.
Available in $group stage only.
$last Returns a value from the last document for each group. Order is only defined if the documents are in a defined order.
Available in $group stage only.
$max Returns the highest expression value for each group.
Changed in version 3.2: Available in both $group and $project stages.
$min Returns the lowest expression value for each group.
Changed in version 3.2: Available in both $group and $project stages.
$push Returns an array of expression values for each group.
Available in $group stage only.
$addToSet Returns an array of unique expression values for each group. Order of the array elements is undefined.
Available in $group stage only.
$stdDevPop Returns the population standard deviation of the input values.
Changed in version 3.2: Available in both $group and $project stages.
$stdDevSamp Returns the sample standard deviation of the input values.
Changed in version 3.2: Available in both $group and $project stages.
$group Operator and Memory
The $group stage has a limit of 100 megabytes of RAM. By default, if the stage exceeds this limit, $groupwill produce an error. However, to allow for the handling of large datasets, set the allowDiskUse option totrue to enable $group operations to write to temporary files. See db.collection.aggregate()method and the aggregate command for details.
Changed in version 2.6: MongoDB introduces a limit of 100 megabytes of RAM for the $group stage as well as the allowDiskUse option to handle operations for large datasets.
Examples
Calculate Count, Sum, and Average
Given a collection sales with the following documents:
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-03-01T08:00:00Z") } { "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-03-01T09:00:00Z") } { "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-03-15T09:00:00Z") } { "_id" : 4, "item" : "xyz", "price" : 5, "quantity" : 20, "date" : ISODate("2014-04-04T11:21:39.736Z") } { "_id" : 5, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-04-04T21:23:13.331Z") }
Group by Month, Day, and Year
The following aggregation operation uses the $group stage to group the documents by the month, day, and year and calculates the total price and the average quantity as well as counts the documents per each group:
db.sales.aggregate( [ { $group : { _id : { month: { $month: "$date" }, day: { $dayOfMonth: "$date" }, year: { $year: "$date" } }, totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } } ] )
The operation returns the following results:
{ "_id" : { "month" : 3, "day" : 15, "year" : 2014 }, "totalPrice" : 50, "averageQuantity" : 10, "count" : 1 } { "_id" : { "month" : 4, "day" : 4, "year" : 2014 }, "totalPrice" : 200, "averageQuantity" : 15, "count" : 2 } { "_id" : { "month" : 3, "day" : 1, "year" : 2014 }, "totalPrice" : 40, "averageQuantity" : 1.5, "count" : 2 }
Group by null
The following aggregation operation specifies a group _id of null, calculating the total price and the average quantity as well as counts for all documents in the collection:
db.sales.aggregate( [ { $group : { _id : null, totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } } ] )
The operation returns the following result:
{ "_id" : null, "totalPrice" : 290, "averageQuantity" : 8.6, "count" : 5 }
Retrieve Distinct Values
Given a collection sales with the following documents:
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-03-01T08:00:00Z") } { "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-03-01T09:00:00Z") } { "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-03-15T09:00:00Z") } { "_id" : 4, "item" : "xyz", "price" : 5, "quantity" : 20, "date" : ISODate("2014-04-04T11:21:39.736Z") } { "_id" : 5, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-04-04T21:23:13.331Z") }
The following aggregation operation uses the $group stage to group the documents by the item to retrieve the distinct item values:
db.sales.aggregate( [ { $group : { _id : "$item" } } ] )
The operation returns the following result:
{ "_id" : "xyz" } { "_id" : "jkl" } { "_id" : "abc" }
Pivot Data
A collection books contains the following documents:
{ "_id" : 8751, "title" : "The Banquet", "author" : "Dante", "copies" : 2 } { "_id" : 8752, "title" : "Divine Comedy", "author" : "Dante", "copies" : 1 } { "_id" : 8645, "title" : "Eclogues", "author" : "Dante", "copies" : 2 } { "_id" : 7000, "title" : "The Odyssey", "author" : "Homer", "copies" : 10 } { "_id" : 7020, "title" : "Iliad", "author" : "Homer", "copies" : 10 }
Group title by author
The following aggregation operation pivots the data in the books collection to have titles grouped by authors.
db.books.aggregate( [ { $group : { _id : "$author", books: { $push: "$title" } } } ] )
The operation returns the following documents:
{ "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] } { "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }
Group Documents by author
The following aggregation operation uses the $$ROOT system variable to group the documents by authors. The resulting documents must not exceed the BSON Document Size limit.
db.books.aggregate( [ { $group : { _id : "$author", books: { $push: "$$ROOT" } } } ] )
The operation returns the following documents:
{ "_id" : "Homer", "books" : [ { "_id" : 7000, "title" : "The Odyssey", "author" : "Homer", "copies" : 10 }, { "_id" : 7020, "title" : "Iliad", "author" : "Homer", "copies" : 10 } ] } { "_id" : "Dante", "books" : [ { "_id" : 8751, "title" : "The Banquet", "author" : "Dante", "copies" : 2 }, { "_id" : 8752, "title" : "Divine Comedy", "author" : "Dante", "copies" : 1 }, { "_id" : 8645, "title" : "Eclogues", "author" : "Dante", "copies" : 2 } ] }