MongoDB $group operator

The $group operator is crucial in MongoDB, as it helps to perform various transformations of data. The $group operator groups similar data by some specified expression and groups the document for each distinct grouping.

Suppose there are 50 students in a database, and all of them love cricket. If we want to count all students who love cricket, then the $group operator is an elegant solution for such a task.

Syntax:

{
 $group:
     {
          _id: <expression>, // Group By Expression
          <field1>: { <accumulator1> : <expression1> },
           ...
      }
}

Important points:

_id: This field is mandatory for grouping. If you specify the value of the _id field as null or a constant, the $group operator counts the accumulated values for all input documents as a whole.
field: This is an optional field and is calculated using the <accumulator> operators.

Examples:

In the following examples, we are working with:

Database: JavaTpoint
Collection: invoice
Document: Ten documents that contain the details of the invoice

{
        "_id" : A1,
        "item_name" : "Blue box",
        "price" : 10,
        "qty" : 15,
        "date_of_bill" : "13/04/2015"
}
{
        "_id" : A2,
        "item_name" : "Light Red box",
        "price" : 15,
        "qty" : 20,
        "date_of_bill" : "05/12/2014"
}
{
        "_id" : null,
        "item_name" : "Green box",
        "price" : 10,
        "qty" : 30,
        "date_of_bill" : "17/12/2014"
}
{
        "_id" : A3,
        "item_name" : "White box",
        "price" : 8,
        "qty" : 25,
        "date_of_bill" : "07/02/2014"
}
{
        "_id" : A4,
        "item_name" : "Blue box",
        "price" : 15,
        "qty" : 20,
        "date_of_bill" : "13/04/2015"
}
{
        "_id" : A5,
        "item_name" : "Red box",
        "price" : 12,
        "qty" : 10,
        "date_of_bill" : "05/12/2014"
}
{
        "_id" : A6,
        "item_name" : "Black box",
        "price" : 10,
        "qty" : 30,
        "date_of_bill" : "22/04/2020"
}
{
        "_id" : A7,
        "item_name" : "Red box",
        "price" : 8,
        "qty" : 15,
        "date_of_bill" : "05/12/2014"
}
{
        "_id" : A8,
        "item_name" : "Green box",
        "price" : 20,
        "qty" : 10,
        "date_of_bill" : "17/12/2014"
}
{
        "_id" : A9,
        "item_name" : "Green box",
        "price" : 10,
        "qty" : 30,
        "date_of_bill" : "17/12/2014"
}

Example 1: $group

In this example, we'll group by bill date and display these fields (total price, average quantity, and count the number of bills in the same date).

db.invoice.aggregate(
   [
      {
        $group : {_id : "$date_of_bill ",
           Total_price: { $sum: { $multiply: [ "$price", "$qty" ] } },
           Average_qty: { $avg: "$qty" },
           count: { $sum: 1 }
        }
     }
  ]
).pretty();

Output:

{ "_id" : "13/04/2015", "Total_price" : 875, "Average_qty" : 17.5, "count" : 2 }
{ "_id" : "05/12/2014", "Total_price" : 1575, "Average_qty" : 15, "count" : 3 }
{ "_id" : "17/12/2014", "Total_price" : 2800, "Average_qty" : 28.3333333, "count" : 3 }
{ "_id" : "07/02/2014", "Total_price" : 200, "Average_qty" : 25, "count" : 1 }
{ "_id" : "22/04/2020", "Total_price" : 300, "Average_qty" : 30, "count" : 1 }

Here the result shows that the field bill date document is grouped and shows the total price, average quantity, and the number of bills done for that date.

Example 2: $group on multiple keys

In this example, we'll group by bill date and item name field and display these fields (total price, average quantity, and count the number of bills in the same date).

db.invoice.aggregate(
   [
      {
        $group : {_id : { date_of_bill : "$date_of_bill ", item : "$item_name"
           Total_price: { $sum: { $multiply: [ "$price", "$qty" ] } },
           Average_qty: { $avg: "$qty" },
           count: { $sum: 1 }
        }
     }
  ]
).pretty();

Output:

{ "_id" : { 
            "date_of_bill" : "13/04/2015", 
            "item" : "Blue box"
          }
          "Total_price" : 875, 
          "Average_qty" : 17.5, 
          "count" : 2 
}
{ "_id" : { 
            "date_of_bill" : "05/12/2014", 
            "item" : "Light Red box"
          }
          "Total_price" : 300, 
          "Average_qty" : 20, 
          "count" : 1
}

{ "_id" : { 
            "date_of_bill" : "05/12/2014", 
            "item" : "Red box"
          }
          "Total_price" : 500, 
          "Average_qty" : 12.5, 
          "count" : 2
}
{ "_id" : { 
            "date_of_bill" : "17/12/2014", 
            "item" : "Green box"
          }
          "Total_price" : 2800, 
          "Average_qty" : 28.3333333, 
          "count" : 3
}
{ "_id" : { 
            "date_of_bill" : "07/02/2014", 
            "item" : "White box"
          }
          "Total_price" : 200, 
          "Average_qty" : 25, 
          "count" : 1
}
{ "_id" : { 
            "date_of_bill" : "22/04/2020", 
            "item" : "Black box"
          }
          "Total_price" : 300, 
          "Average_qty" : 30, 
          "count" : 1
}

Example 3: $group on multiple keys with $match

In this example, we'll group by Bill Date and Item Name fields and display these fields (Total Price, Average Quantity, and Count the number of bills in the same date) for documents whose bill date is 05/12/2014.

db.invoice.aggregate(
   [
    { 
	$match : { date_of_bill : "05/12/2014"}
    },
      {
           $group : {_id : { date_of_bill : "$date_of_bill ", item : "$item_name"
           Total_price: { $sum: { $multiply: [ "$price", "$qty" ] } },
           Average_qty: { $avg: "$qty" },
           count: { $sum: 1 }
        }
     }
  ]
).pretty();

Output:

{ "_id" : { 
            "date_of_bill" : "05/12/2014", 
            "item" : "Light Red box"
          }
          "Total_price" : 300, 
          "Average_qty" : 20, 
          "count" : 1
}

{ "_id" : { 
            "date_of_bill" : "05/12/2014", 
            "item" : "Red box"
          }
          "Total_price" : 500, 
          "Average_qty" : 12.5, 
          "count" : 2
}

Next TopicMongoDB Regular Expression ($regex)

← prev next →