Dec 2, 2015

$group (aggregation) in MongoDB

IF we want to group document by specific expression and want to output for each distinct grouping of document here we have to use $group. IF you familiar with Relational Database like SQL Server, It's work same like GROUP BY clause.

Output document contains the _id field which contains the distinct group by key also output document contains computed fields which grasp the value of some accumulator expression grouped by the _id.

Syntax:


{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... }


Accumulator Operator:

$num : It's returns sum of the numeric value. It can be use in $project also in mongodb 3.2 version.

$avg  : It's returns the average of numeric values. It can be use in $project also in mongodb 3.2 version.

$first : It's returns a value from the first document for each group.

$last : It's returns a value from the first document for each group.

$max : It's returns the highest expression value for each group. It can be use in $project also in mongodb 3.2 version.

$min : It's returns the lowest expression value for each group.  It can be use in $project also in mongodb 3.2 version.

$push : It's returns the an array of expression values for each group.

$addToSet : It's returns an array of unique expression values for each group. Order of the array elements is undefined.

$stdDevPop : It's returns the population standard deviation of the input values.

$stdDevSamp : It's returns the sample standard deviation of the input values.

Example :


{ "_id" : 1, "Book Title": "book1", "price" : 20, "quantity" : 1, "date" : ISODate("2016-08-05T07:00:00Z") }
{ "_id" : 2, " Book Title " : " book2", "price" : 10, "quantity" : 2, "date" : ISODate("2016-08-05T08:00:00Z") }
{ "_id" : 3, " Book Title " : " book3", "price" : 30, "quantity" : 4, "date" : ISODate("2016-08-17T10:00:00Z") }
{ "_id" : 4, " Book Title " : " book4", "price" : 10, "quantity" : 2, "date" : ISODate("2014-09-01T11:20:39.736Z") }
{ "_id" : 5, " Book Title " : " book5", "price" : 20, "quantity" : 6, "date" : ISODate("2014-09-04T20:23:13.331Z") }


Group by Month, and Year


db.BookOrder.aggregate(
   [
      {
        $group : {
           _id : { month: { $month: "$date" , year: { $year: "$date" } },
           totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
           totalQty: { $sum: "$quantity" },
           count: { $sum: 1 }
        }
      }
   ]
)


Result:


{ "_id" : { "month" : 8, "year" : 2016 }, " totalAmount " : 60, " totalQty " : 7, "count" : 3 }
{ "_id" : { "month" : 9, "year" : 2016 }, " totalAmount " : 30, " totalQty " : 8, "count" : 2 }


Note : _id filed is mandatory, except _id all fields is optional and computed using accumulator.

Limitation of Group Stage: $group stage memory can't exceed more than 100 megabytes RAM. If $group stage exceed his limit by default give an error.