mongodb中的aggregation的使用

$group (aggregation)

On this page

• Definition
• Considerations
• 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: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

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 <accumulator> operators.

The _id and the <accumulator> expressions can accept any valid expression. For more information on expressions, see Expressions.

Considerations

Accumulator Operator

The <accumulator> operator must be one of the following accumulator operators:

NameDescription$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 }     ]}