MongoDB group by multiple fields

In this MongoDB tutorial, We are going to learn “How to perform MongoDB group by multiple fields”. We will also do this with various methodologies. These are the following topics that we are going to cover in this tutorial:

• What is $group(aggregation) ?
• MongoDB group by multiple fields
• MongoDB group by multiple fields using compass
• MongoDB group by multiple fields find sort
• MongoDB group by multiple fields and count
• MongoDB group by multiple fields with condition
• MongoDB bucket group by multiple fields
• MongoDB group by multiple fields find distinct

The best way of grouping the Multiple fields present inside the documents of MongoDB is made by the $group operator. This operator helps you in executing the various other aggregation functions as well on the grouped data.

What is $group(aggregation) ?

In the MongoDB database, group by is used to group the data from the collection. We can also use the aggregation function as well and group the method. The aggregate function is used in multiple conditions.

We can group by single as well as multiple fields from the collection. We can also perform these all operations $avg, $sum, $max, $min, $push, $last, $first etc. with group by in MongoDB.

Syntax:

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

The below table briefly explain all the term that you use when you write the code:

Field	Description
_id	Required, if you define _id value null or other constant values than the $group stage calculates accumulated values for all the input documents.
field	Optional, calculate by using accumulator operator

There are some of the following accumulator operators that we used in the field:

Name	Description
$accumulator	Returns user-defined accumulator function.
$avg	Returns the average of numerical values
$count	Returns number of documents in a group
$first	Returns the first document of each group
$last	Returns the last document of each group
$max	Returns the highest expression value of each group
$min	Returns the lowest expression value of each group
$push	Returns the array of expression values
$sum	Returns the sum of numerical values

These are the operators, we use as a field and find the result as our requirement.

Read: MongoDB drop collection

MongoDB group by multiple fields

In MongoDB, When you want to perform any operation on multiple fields then you have to use $group aggregation. You will more understand with help of examples.

Example:

In the example, I will show you how you can display some particular documents with multiple fields when we have a large dataset in the collection.

> db.person.find().pretty()
{
        "_id" : ObjectId("612f146e1a18fbb8eaf2b8e2"),
        "Name" : "David",
        "Age" : 22,
        "Gender" : "Male",
        "Country": "United States of America"
}
{
        "_id" : ObjectId("612f147f1a18fbb8eaf2b8e3"),
        "Name" : "Peter",
        "Age" : 24,
        "Gender" : "Male",
        "Country": "New Zealand"
}
{
        "_id" : ObjectId("612f14a01a18fbb8eaf2b8e4"),
        "Name" : "Stromi",
        "Age" : 22,
        "Gender" : "Female",
        "Country": "United Kingdom"
}
{
        "_id" : ObjectId("612f14b21a18fbb8eaf2b8e5"),
        "Name" : "Kim",
        "Age" : 23,
        "Gender" : "Female",
        "Country": "Canada"
}

These are a few documents we inserted into the person collections.

Now, we apply the $group aggregation operation and display only fields that we want to.

db.persons.aggregate([ 
    {$group: {_id: {age:"$Age", 
                    gender:"$Gender"}
             }
    } 
])
{ "_id" : { "age" : 24, "gender" : "Male" } }
{ "_id" : { "age" : 22, "gender" : "Female" } }
{ "_id" : { "age" : 22, "gender" : "Male" } }
{ "_id" : { "age" : 23, "gender" : "Female" } }

Here, you can see in the output we display only two fields age and gender but we display more fields as per the question requirement.

Read: How to store images in MongoDB

MongoDB group by multiple fields using compass

In MongoDB, you can apply all the aggregation operations on collection using MongoDB compass as well. Here, you will learn about how you can apply $group aggregation with multiple fields using compass.

Example:

In this example, we learn how to apply $group aggregation but for that, you have to follow the below steps:

• Open MongoDB compass
• Create a new database and collection

Here, I created a database and collection as test and data respectively. I also inserted some documents into the collection.

If you want to know how to insert the data, click on ADD DATA button. Now, It will open a drop-down list choose Insert Documents and insert the documents into the collection.

You see we inserted a document into the collection. You can also insert multiple fields as well but you have to define all the fields into the list.

• Now, we apply $group aggregation

For applying aggregation operation click on aggregation(right side of the Documents). Now, see on bottom pipeline stage and click on the drop-down list and choose $group.

Here, we apply the condition on Age field them find the average age and number of records. you can also see the output on the bottom left side.

There are various aggregation operations and you can also use them in the MongoDB compass.

Read: MongoDB group by count

MongoDB group by multiple fields find sort

In MongoDB, sort by means sort the data in ascending or descending order and you can do this by using 1 or -1 respectively. Now, you will learn more about how to sort the multiple fields with examples.

Example:

In this example, we will learn how to sort the multiple fields using aggregation operations

• Open command prompt and start the MongoDB server
• Create a new database and collection

• Here, I already created a database and collection as organisation and enterprise respectively.
• Now, check the data inside the collection, use find() method.

These are some documents we inserted into the enterprise collection. Now, we can apply the $sort aggregation on the multiple fields.

Now see, we took multiple fields to sort the documents. We sort the value and _id field in descending and ascending order respectively.

Note that, If you want consistent order of at least one field in your sort that contains a unique value. The simplest way is to include the _id field in your sort query.

Read: How to check if MongoDB is installed + MongoDB Version

MongoDB group by multiple fields and count

In MongoDB, when we have a large dataset inside the collection and we want to count where the field value is repeating on multiple fields then we use $group aggregation.

Example:

Here, we are taking an example in which we apply $group aggregation with multiple fields and get the count of duplicate field values.

> db.demo.find()
{ "_id" : ObjectId("612f00901a18fbb8eaf2b8d8"), "Name1" : "Chris", "Name2" : "David" }
{ "_id" : ObjectId("612f00a41a18fbb8eaf2b8d9"), "Name1" : "David", "Name2" : "Chris" }
{ "_id" : ObjectId("612f00b61a18fbb8eaf2b8da"), "Name1" : "Bob", "Name2" : "Sam" }
{ "_id" : ObjectId("612f00ca1a18fbb8eaf2b8db"), "Name1" : "Chris", "Name2" : "David" }

These are some of the documents we inserted into the demo collection. Now, we apply the aggregation operations.

> db.demo.aggregate([
    {
       $project:
       {
          FirstName1:
          {
             $cond: { if: { $gte: [ "$Name1", "$Name2" ] }, then: "$Name2", else: "$Name1" }
          },
          FirstName2:
          {
             $cond: { if: { $lt: [ "$Name1", "$Name2" ] }, then: "$Name2", else: "$Name1" }
          }
       }
    },{
       $group:
       {
          _id:
          {
             Name1: "$FirstName1",
             Name2: "$FirstName2"
          },
          count: { $sum: 1}
       }
    }
    ])
{ "_id" : { "Name1" : "Chris", "Name2" : "David" }, "count" : 3 }
{ "_id" : { "Name1" : "Bob", "Name2" : "Sam" }, "count" : 1 }

As you can see in the output, we use $cond(aggregation) that evaluates a boolean expression to return one of the two specified expressions and after that, we count the duplicate field values and group them together.

Read: Import JSON and insert JSON into MongoDB

MongoDB group by multiple fields with condition

In MongoDB, when you have multiple fields in the documents and you only want to retrieve a particular dataset according to the condition. Here, the condition could be anything like count the documents, display the fields greater than, less than, count repeated fields, and so on.

So you will use the $group aggregation operation and apply the condition based upon the question requirement. You will more understand with the help of an example.

Example:

We are taking an example in which you will understand how to count and sort the multiple fields in a particular collection.

> db.courses.find().pretty()
{
        "_id" : ObjectId("612f36491a18fbb8eaf2b8e6"),
        "university" : "USAL",
        "name" : "Computer Science",
        "level" : "Excellent"
}
{
        "_id" : ObjectId("612f36491a18fbb8eaf2b8e7"),
        "university" : "USAL",
        "name" : "Electronics",
        "level" : "Intermediate"
}
{
        "_id" : ObjectId("612f36491a18fbb8eaf2b8e8"),
        "university" : "USAL",
        "name" : "Communication",
        "level" : "Excellent"
}

These are the few documents that we inserted into the courses collection. Now, we apply the condition with help of aggregation operations

db.courses.aggregate([ 
    {
      $group:{
       _id:{ 
         "university":"$university", 
         "level":"$level" 
      }, 
      "levelCount":{"$sum":1} }},
     {"$sort":{"levelCount":-1}}
])
{ "_id" : { "university" : "USAL", "level" : "Excellent" }, "levelCount" : 2 }
{ "_id" : { "university" : "USAL", "level" : "Intermediate" }, "levelCount" : 1 }

In the output, we count the fields using $sum aggregation and there are 2 fields are Excellent level and 1 is Intermediate. we also display the result in descending order using $sort aggregation.

Read: MongoDB sort by date

MongoDB bucket group by multiple fields

In this topic, you will learn how to group multiple fields of documents using a bucket. First, you have to understand what is $bucket aggregation. Let’s understand this briefly

What is $bucket aggregation?

In bucket aggregation, categorizing the incoming documents into groups is called buckets. We divide the documents based on a specified expression.

Syntax:

{
  $bucket: {
      groupBy: <expression>,
      boundaries: [ <lowerbound1>, <lowerbound2>, ... ],
      default: <literal>,
      output: {
         <output1>: { <$accumulator expression> }
      }
   }
}

Note:

$bucket requires at least one condition otherwise operation throws an error:

• Documents resolve the groupBy expression to a value within one of the bucket ranges specified by boundaries.
• Default value is specified to bucket document groupBy values are outside of the boundaries.

Example:

This example will help you to understand, how you can use $bucket aggregation on the group by multiple fields.

> db.artists.find().pretty()
{
        "_id" : 1,
        "last_name" : "Bernard",
        "first_name" : "Emil",
        "year_born" : 1868,
        "year_died" : 1941,
        "nationality" : "France"
}
{
        "_id" : 2,
        "last_name" : "Rippl-Ronai",
        "first_name" : "Joszef",
        "year_born" : 1861,
        "year_died" : 1927,
        "nationality" : "Hungary"
}
{
        "_id" : 3,
        "last_name" : "Ostroumova",
        "first_name" : "Anna",
        "year_born" : 1871,
        "year_died" : 1955,
        "nationality" : "Russia"
}
{
        "_id" : 4,
        "last_name" : "Van Gogh",
        "first_name" : "Vincent",
        "year_born" : 1853,
        "year_died" : 1890,
        "nationality" : "Holland"
}
{
        "_id" : 5,
        "last_name" : "Maurer",
        "first_name" : "Alfred",
        "year_born" : 1868,
        "year_died" : 1932,
        "nationality" : "USA"
}

These are the few fields inserted into the artists’ collection and apply the aggregation operation into this collection.

db.artists.aggregate( [
  {
    $bucket: {
      groupBy: "$year_born",                        
      boundaries: [ 1840, 1850, 1860, 1870, 1880 ], 
      default: "Other",                             
      output: {                                    
        "count": { $sum: 1 },
        "artists" :
          {
            $push: {
              "name": { $concat: [ "$first_name", " ", "$last_name"] },
              "year_born": "$year_born"
            }
          }
      }
    }
  }
])

{ "_id" : 1850, "count" : 1, "artists" : [ { "name" : "Vincent Van Gogh", "year_born" : 1853 } ] }
{ "_id" : 1860, "count" : 3, "artists" : [ { "name" : "Emil Bernard", "year_born" : 1868 }, { "name" : "Joszef Rippl-Ronai", "year_born" : 1861 }, { "name" : "Alfred Maurer", "year_born" : 1868 } ] }
{ "_id" : 1870, "count" : 1, "artists" : [ { "name" : "Anna Ostroumova", "year_born" : 1871 } ] }

Here, we use some of the aggregation operations $bucket, $sum, and $push. In $bucket aggregation using year_born field as groupBy and set boundaries.

After that count, the artists of year_born use $sum aggregation of particular boundaries and we also use $push aggregation to store some fields into an array and then use $concat to concatenate the artist’s first and last name.

In the example, you learned how to apply conditions with bucket groupBy multiple fields and retrieve all the documents.

Read: MongoDB sort by field

MongoDB group by multiple fields find distinct

In this topic, you will learn how you can find the distinct fields using $group aggregation when you have given multiple fields. You only need to specify the field name and $group will help you to find all the distinct fields.

Example:

In this example, you will understand how you can find the distinct fields by using $group aggregation

> db.sales.find().pretty()
{
        "_id" : 1,
        "item" : "Surf Excel",
        "price" : NumberDecimal("10"),
        "quantity" : 2,
        "date" : ISODate("2014-03-01T08:00:00Z")
}
{
        "_id" : 2,
        "item" : "Lays",
        "price" : NumberDecimal("20"),
        "quantity" : 1,
        "date" : ISODate("2014-03-01T09:00:00Z")
}
{
        "_id" : 3,
        "item" : "5star Bite",
        "price" : NumberDecimal("10"),
        "quantity" : 12,
        "date" : ISODate("2014-03-15T09:00:00Z")
}
{
        "_id" : 4,
        "item" : "5star Bite",
        "price" : NumberDecimal("10"),
        "quantity" : 7,
        "date" : ISODate("2014-04-04T11:21:39.736Z")
}
{
        "_id" : 5,
        "item" : "Surf Excel",
        "price" : NumberDecimal("10"),
        "quantity" : 5,
        "date" : ISODate("2014-04-04T21:23:13.331Z")
}
{
        "_id" : 6,
        "item" : "Fevicol",
        "price" : NumberDecimal("20"),
        "quantity" : 5,
        "date" : ISODate("2015-06-04T05:08:13Z")
}

These are the few documents we inserted into the sales collection. You can see there are multiple fields in the collection and we only want distinct fields so we apply $group aggregation.

> db.sales.aggregate( [ { $group : { _id : "$item" } } ] )
{ "_id" : "Lays" }
{ "_id" : "5star Bite" }
{ "_id" : "Fevicol" }
{ "_id" : "Surf Excel" }

In the output, after applying the $group operation on the item field, display all the distinct items from the collection. This is the easiest way to find distinct in multiple fields by using $group aggregation.

You may also like reading the following articles.

• Create tables in MongoDB
• MongoDB order by date
• Pros and cons of MongoDB
• How does MongoDB create collection
• MongoDB join two collections
• MongoDB update the documents

In this tutorial, we have learned “How to perform MongoDB group by multiple fields” using different approaches with examples. These are the following topics that we covered in this tutorial

• What is $group (aggregation)
• MongoDB group by multiple fields
• MongoDB group by multiple fields using compass
• MongoDB group by multiple fields find sort
• MongoDB group by multiple fields and count
• MongoDB group by multiple fields with condition
• MongoDB bucket group by multiple fields
• MongoDB group by multiple fields find distinct