How to Group Values by Multiple Fields Using MongoDB

1. the $group Operator in MongoDB
2. $group on Multiple Keys in MongoDB
3. $group on Multiple Keys With $match in MongoDB
4. $group Distinct Values in MongoDB
5. $group Stage to Group the Invoice Date in MongoDB

MongoDB group by multiple fields is used to group values by multiple fields using various methodologies.

One of the most efficient ways of grouping the various fields present inside the documents of MongoDB is by using the $group operator, which helps in performing multiple other aggregation functions as well on the grouped data.

This article will discuss multiple ways of using operators in MongoDB to group values by multiple fields inside the document. It will also look at the list of operators used along with aggregation and how you can implement them with the help of examples.

the $group Operator in MongoDB

The input and output of MongoDB’s $group operator for several aggregating fields are nothing more than a written record of the database. It accepts a single document and returns one or more documents.

While grouping the various fields, you can use a variety of operators, which are described below.

1. $first – extracts only the first document from a group of documents, commonly used while sorting.
2. $push – insert a new value of a field inside the resultant field.
3. $last – insert a new value of a field inside the resultant field at last.
4. $addToSet – helps add a new value to the existing values of the array without any duplication taking place.
5. $min – This operator helps find and return the smallest integer or smallest value from the supplied and passed integer value.
6. $avg – calculates the average value of all the specified numeric values between the fields.
7. $max – helps find and return the most significant integer value or biggest value from the supplied and passed integer value.
8. $sum – It will calculate the sum of all the specified numeric values.

Aggregation Pipeline

When the POSIX mode is set to true, another way to group fields in MongoDB is to use pipelines. Using polymer tube functions, you can filter out documents that meet the aggregation pipelines’ criteria.

The stage operators are defined for each level of the aggregation pipeline. The stage operators can internally use the expression operators to have a line break before each level stage or even calculate the average or sum or concatenate a particular value.

The aggregation pipeline’s output is regarded as the final output to be returned, and it can even be stored in collections if necessary.

Processing Flow

Another way of grouping the multiple fields is by using processing flow. You can use various channels for processing the data simultaneously by using the Db.collection.aggregate() function.

The Db.collection.aggregate() function does the aggregation internally and provides the support for the multiple operations to be used.

The Db.collection.aggregate() function can be utilized effectively with a sequence of slices without causing data loss. The function Db.collection.aggregate() returns the data stored in memory as a cursor, which can be used directly as MongoShell.

Example of $group Operator in MongoDB

You can use the following collection for this article.

db={
  "invoice": [
    {
      "_id": 1,
      "item": "apple",
      "qty": 20,
      "rate": 10,
      "inv_date": "02/02/2020"
    },
    {
      "_id": 2,
      "item": "orange",
      "qty": 15,
      "rate": 8,
      "inv_date": "05/12/2020"
    },
    {
      "_id": 3,
      "item": "mango",
      "qty": 25,
      "rate": 8,
      "inv_date": "07/02/2020"
    },
    {
      "_id": 4,
      "item": "apple",
      "qty": 20,
      "rate": 10,
      "inv_date": "02/02/2020"
    },
    {
      "_id": 5,
      "item": "mango",
      "qty": 10,
      "rate": 8,
      "inv_date": "05/12/2020"
    },
    {
      "_id": 6,
      "item": "apple",
      "qty": 30,
      "rate": 10,
      "inv_date": "13/04/2020"
    },
    {
      "_id": 7,
      "item": "orange",
      "qty": 15,
      "rate": 8,
      "inv_date": "05/12/2020"
    },
    {
      "_id": null,
      "item": "banana",
      "qty": 10,
      "rate": 20,
      "inv_date": "17/12/2020"
    },

  ]
}

The following example is grouped by the invoice date field and displays the total cost, average quantity, and the number of invoices on the same date.

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

The result shows that the document for the field invoice date has grouped and shows the total cost, average quantity, and how many invoices were made for that date.

$group on Multiple Keys in MongoDB

The following example groups by the invoice date and then by item field and displays the total cost, average quantity, and the number of invoices on 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
      }
    }
  }
])

The result below shows that the document for the invoice dates 05/12/2020 and 02/02/2020 have the same item; a combination of these two fields has made a group.

$group on Multiple Keys With $match in MongoDB

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

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

The result below shows that the document for the invoice date 05/12/2020 has three invoices, but with the same item combination have made a group.

$group Distinct Values in MongoDB

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"
    }
  }
])

The output is shown in the screenshot below.

$group Stage to Group the Invoice Date in MongoDB

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

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

The screenshot below is the output.

This article teaches you the most efficient ways of grouping the multiple fields present inside the documents of MongoDB by using the $group operator.

It also discussed all the numerous forms of using operators in MongoDB and a list of operators used with aggregation, and how you can implement them with the help of examples.