MongoDB Aggregation Stages Operator - $group

Description

The MongoDB $group stages operator groups the documents by some specified expression and groups the document for each distinct grouping. An _id field in the output documents 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. This operator does not order its output documents.

Syntax:

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

Points to remember:

• The _id field is mandatory; an _id value can be specified as a null to calculate accumulated values for all the input documents as a whole.
• The rest of the fields eligible to be computed are optional and computed using the <accumulator> operators.
• The _id and the <accumulator> expressions can accept any valid expression.

Sample collection invoice

{
        "_id" : 1,
        "item" : "doz",
        "qty" : 20,
        "rate" : 10,
        "inv_date" : "02/02/2014"
}
{
        "_id" : 2,
        "item" : "sam",
        "qty" : 15,
        "rate" : 8,
        "inv_date" : "05/12/2014"
}
{
        "_id" : 3,
        "item" : "amp",
        "qty" : 25,
        "rate" : 8,
        "inv_date" : "07/02/2014"
}
{
        "_id" : 4,
        "item" : "doz",
        "qty" : 20,
        "rate" : 10,
        "inv_date" : "02/02/2014"
}
{
        "_id" : 5,
        "item" : "amp",
        "qty" : 10,
        "rate" : 8,
        "inv_date" : "05/12/2014"
}
{
        "_id" : 6,
        "item" : "doz",
        "qty" : 30,
        "rate" : 10,
        "inv_date" : "13/04/2014"
}
{
        "_id" : 7,
        "item" : "sam",
        "qty" : 15,
        "rate" : 8,
        "inv_date" : "05/12/2014"
}
{
        "_id" : null,
        "item" : "mks",
        "qty" : 10,
        "rate" : 20,
        "inv_date" : "17/12/2014"
}

Example-1 : $group

The following example groups by the invoice date field, and display the total cost, average quantity and number of an invoice in the same date.

db.invoice.aggregate(
   [
      {
        $group : {_id : "$inv_date",
           totalCost: { $sum: { $multiply: [ "$rate", "$qty" ] } },
           avgQty: { $avg: "$qty" },
           count: { $sum: 1 }
        }
     }
  ]
).pretty();

Output:

{ "_id" : "17/12/2014", "totalCost" : 200, "avgQty" : 10, "count" : 1 }
{ "_id" : "13/04/2014", "totalCost" : 300, "avgQty" : 30, "count" : 1 }
{
        "_id" : "05/12/2014",
        "totalCost" : 320,
        "avgQty" : 13.333333333333334,
        "count" : 3
}
{ "_id" : "07/02/2014", "totalCost" : 200, "avgQty" : 25, "count" : 1 }
{ "_id" : "02/02/2014", "totalCost" : 400, "avgQty" : 20, "count" : 2 }

Here from the result it shows that, the document for the field invoice date have grouped and shows the total cost, average quentiry and how many invoice made for that date.

Example-2 : $group on multiple keys

The following example groups by the invoice date and then by item field, and display the total cost, average quantity and number of an invoice in the same date.

db.invoice.aggregate(
   [
      {
        $group : {_id : {inv_date : "$inv_date",item : "$item"},
           totalCost: { $sum: { $multiply: [ "$rate", "$qty" ] } },
           avgQty: { $avg: "$qty" },
           count: { $sum: 1 }
        }
     }
  ]
).pretty();

Output :

{
        "_id" : {
                "inv_date" : "17/12/2014",
                "item" : "mks"
        },
        "totalCost" : 200,
        "avgQty" : 10,
        "count" : 1
}
{
        "_id" : {
                "inv_date" : "13/04/2014",
                "item" : "doz"
        },
        "totalCost" : 300,
        "avgQty" : 30,
        "count" : 1
}
{
        "_id" : {
                "inv_date" : "05/12/2014",
                "item" : "amp"
        },
        "totalCost" : 80,
        "avgQty" : 10,
        "count" : 1
}
{
        "_id" : {
                "inv_date" : "07/02/2014",
                "item" : "amp"
        },
        "totalCost" : 200,
        "avgQty" : 25,
        "count" : 1
}
{
        "_id" : {
                "inv_date" : "05/12/2014",
                "item" : "sam"
        },
        "totalCost" : 240,
        "avgQty" : 15,
        "count" : 2
}
{
        "_id" : {
                "inv_date" : "02/02/2014",
                "item" : "doz"
        },
        "totalCost" : 400,
        "avgQty" : 20,
        "count" : 2
}

Here from the above result it shows that, the document for the invoice date 05/12/2014 and 02/02/2014 have the same item, combination of this two field have make a group.

Example-3 : $group on multiple keys with $match

The following example groups by the invoice date and then by item field, and display the total cost, average quantity and number of an invoice in the same date for those documents which invoice date is 05/12/2014.

db.invoice.aggregate(
   [
    { 
	$match : {inv_date : "05/12/2014"}
    },
      {
        $group : {_id : {inv_date : "$inv_date",item : "$item"},
           totalCost: { $sum: { $multiply: [ "$rate", "$qty" ] } },
           avgQty: { $avg: "$qty" },
           count: { $sum: 1 }
        }
     }
  ]
).pretty();

Output :

{
        "_id" : {
                "inv_date" : "05/12/2014",
                "item" : "amp"
        },
        "totalCost" : 80,
        "avgQty" : 10,
        "count" : 1
}
{
        "_id" : {
                "inv_date" : "05/12/2014",
                "item" : "sam"
        },
        "totalCost" : 240,
        "avgQty" : 15,
        "count" : 2
}

Here from the above result it shows that, the document for the invoice date 05/12/2014 have three invoices but with the same item combination have made a group.

Example-4 : $group distinct values

The following aggregation operation uses the $group stage to group the documents by the item to retrieve the distinct item values:

> db.invoice.aggregate( [ { $group : { _id : "$item" } } ] ).pretty();

Output :

> db.invoice.aggregate( [ { $group : { _id : "$item" } } ] ).pretty();
{ "_id" : "mks" }
{ "_id" : "amp" }
{ "_id" : "sam" }
{ "_id" : "doz" }

Example-5 : $group

The following aggregation operation uses the $group stage to group the invoice date of the documents by the item.

db.invoice.aggregate(
   [
     { $group : { _id : "$item", invoiceDate: { $push: "$inv_date" } } }
   ]
).pretty();

Output :

{ "_id" : "mks", "invoiceDate" : [ "17/12/2014" ] }
{ "_id" : "amp", "invoiceDate" : [ "07/02/2014", "05/12/2014" ] }
{ "_id" : "sam", "invoiceDate" : [ "05/12/2014", "05/12/2014" ] }
{
        "_id" : "doz",
        "invoiceDate" : [
                "02/02/2014",
                "02/02/2014",
                "13/04/2014"
        ]
}

Previous: $unwind
Next: $out