MongoDB group by count (12 examples)

by
MongoDB group by count

In this MongoDB tutorial, We are going to learn “How does MongoDB group by count works”. We will also cover this with different operations and examples. These are the following topics that we are going to cover in this tutorial:

  • What is an aggregation operation?
  • MongoDB group by count
  • MongoDB group by count sort
  • MongoDB group by count multiple fields
  • MongoDB group by count greater than 1(any number)
  • MongoDB group by count distinct
  • MongoDB group by count match
  • MongoDB group by count having
  • MongoDB group by date and count
  • MongoDB group by count filter
  • MongoDB group by count where
  • MongoDB group by count array
  • MongoDB group and count with the condition

What is an aggregation operation?

It is an operation that is used to process the data and it consists of the stages that perform a number of operations on the grouped data to return a single result.

There are 3 ways to perform aggregation operations:

  • Aggregation pipeline

Documents enter into a multi-stage pipeline that converts the documents into an aggregated result. The MongoDB aggregation pipeline consists of stages.

Example:

db.collection_name.aggregate([

   //First stage
   { $match: { status: "" } },

   //Second stage
   { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
 
   //Third Stage
    { $sort : {sort_field: -1 }}
   
])

First stage: $match stage process the document by status field.

Second stage: $group stage collects the document by _id field and calculates the amount of unique field.

Last stage: In the last stage we provide tools for grouping and sorting documents by fields.

  • Map-reduce function
  • Single purpose aggregation method

Syntax:

>db.collection_name.aggregate(aggregate_operation)

Read MongoDB sort by date

MongoDB group by count
MongoDB group by count example

MongoDB group by count

In MongoDB, the _id field of each document has given a unique group by value. The aggregation operation processes the data and returns computed results.

Syntax:

db.collection_name.aggregate([
            {$group: {_id:_id: <expression>, count:{ $count: {}}}}
])

Example:

In the example, we stored some documents into data collection and you can access the documents using the find() method.

> db.data.find().pretty()
{"_id": 1, "name": "Smith", "class": 11, "subject": "Science/Maths"}
{"_id": 2, "name": "Tom", "class": 12, "subject": "Arts"}
{"_id": 3, "name": "Adam", "class": 12, "Branch": "Commerce/Maths"}

With the help of the aggregation operation, using $group and $count we count the number of documents in the data collection.

> db.data.aggregate([ {$group:{_id:null, count:{$count:{}}}}])
{ "_id" : null, "count" : 3 }

In this example, you learned how works “group by count” in MongoDB and this is the simplest example of counting using group by.

Read MongoDB sort by field

MongoDB group by count sort

In this group by count sort topic, we use $sortByCount that is equal to $group + $sort. Using this we can sort and count a particular group into ascending and descending order.

Syntax:

{ $group: { _id: <expression>, count: { $sum: 1 } } },
{ $sort: { count: -1 } }

Example:

In this example, we stored some documents into the data collection and use the find() method to access how many documents are in our collection. 

> db.data.find().pretty()
{
        "_id" : ObjectId("611a99100a3322fc1bd8c38b"),
        "fname" : "Tom",
        "city" : "United States of America",
        "courses" : [
                "c#",
                "asp",
                "node"
        ]
}
{
        "_id" : ObjectId("611a99340a3322fc1bd8c38c"),
        "fname" : "Harry",
        "city" : "Canada",
        "courses" : [
                "python",
                "asp",
                "node"
        ]
}
{
        "_id" : ObjectId("611a99510a3322fc1bd8c38d"),
        "fname" : "Mikky",
        "city" : "New Zealand",
        "courses" : [
                "python",
                "asp",
                "c++"
        ]
}
{
        "_id" : ObjectId("611b3e88a60b5002406571c3"),
        "fname" : "Ron",
        "city" : "United Kingdom",
        "courses" : [
                "python",
                "django",
                "node"
        ]
}

In the next step, we unwind the courses array and use the $sortByCount to count the number of documents added with each course.

> db.data.aggregate( [ { $unwind: "$courses" },  
                       { $sortByCount: "$courses" } 
                    ] )
{ "_id" : "node", "count" : 3 }
{ "_id" : "python", "count" : 3 }
{ "_id" : "asp", "count" : 3 }
{ "_id" : "c#", "count" : 1 }
{ "_id" : "django", "count" : 1 }
{ "_id" : "c++", "count" : 1 }

This is the simplest way of the group by count the array and sort them in MongoDB.

Read Create tables in MongoDB

MongoDB group by count multiple fields

In MongoDB for counting the multiple fields, we use the aggregation() function. Here, $count is used to count the fields.

Example:

In this example, we stored some documents in the student collection and use the find() method to check how many documents are in our collection.

> db.student.find().pretty()
{
        "_id" : ObjectId("611de9106cc7e05e5e7fe748"),
        "timestamp" : ISODate("2021-04-01T00:00:00Z")
}
{
        "_id" : ObjectId("611de9106cc7e05e5e7fe749"),
        "timestamp" : ISODate("2021-10-31T00:00:00Z")
}
{
        "_id" : ObjectId("611de9106cc7e05e5e7fe74a"),
        "timestamp" : ISODate("2021-05-02T00:00:00Z")
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74b"),
        "name" : "Tom",
        "age" : 21,
        "timestamp" : ISODate("2021-04-01T00:00:00Z"),
        "city" : "United Kingdom"
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74c"),
        "name" : "Emma",
        "age" : 25,
        "timestamp" : ISODate("2021-10-31T00:00:00Z"),
        "city" : "United States of America"
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74d"),
        "name" : "John",
        "age" : 29,
        "timestamp" : ISODate("2021-05-02T00:00:00Z"),
        "city" : "Australia"
}

Here, we took two fields (name and timestamp). you can either take more fields as well and count the number of the timestamp in the documents.

> db.student.aggregate([ {$group: {_id: {name:"$name",                       
                                         timestamp:"$timestamp" }}},          
                                        {$count:"timestamp"} 
                      ])

{ "timestamp" : 6 }

This is how to group by count by taking the multiple fields in MongoDB. You can take as many fields as per your requirement.

Read Pros and cons of MongoDB

MongoDB group by count greater than 1 (any number)

In this topic, you will learn how to group by count a particular column that is greater than 1 (you can take here any number).

Example:

These are some of the documents we inserted into the marks collection. 

> db.marks.insertMany([
 { "_id" : 1, "subject" : "History", "score" : 88 },
 { "_id" : 2, "subject" : "Science", "score" : 92 },
 { "_id" : 3, "subject" : "Math", "score" : 97 },
 { "_id" : 4, "subject" : "English", "score" : 71 },
 { "_id" : 5, "subject" : "Hindi", "score" : 69 },
 { "_id" : 6, "subject" : "Social Science", "score" : 80 }])
{ "acknowledged" : true, "insertedIds" : [ 1, 2, 3, 4, 5, 6 ] }

you can access the documents by using the find() method.

> db.marks.find()
{ "_id" : 1, "subject" : "History", "score" : 88 }
{ "_id" : 2, "subject" : "Science", "score" : 92 }
{ "_id" : 3, "subject" : "Math", "score" : 97 }
{ "_id" : 4, "subject" : "English", "score" : 71 }
{ "_id" : 5, "subject" : "Hindi", "score" : 69 }
{ "_id" : 6, "subject" : "Social Science", "score" : 80 }

In the next step, we apply the condition and count the passing_score, whose score is greater than 70 using aggregation operation.

db.scores.aggregate(
  [
    {
      $match: {
        score: {
          $gt: 70
        }
      }
    },
    {
      $count: "passing_scores"
    }
  ]
)
{ "passing_scores" : 5 }

You can see in the output, there are only 5 records out of 6 whose score is greater than 70.

This is how to count the number of documents that is greater than a particular number in MongoDB.

MongoDB group by count distinct

In this topic, you will learn how to select distinct datasets from particular documents and count them.

Example:

We inserted some documents into the demo collection. You can check it by using the find() method.

> db.demo.find().pretty()
{
        "_id" : ObjectId("61246756a0581758e36006ea"),
        "details" : [
                {
                        "Name" : "Chris",
                        "Marks" : 78
                },
                {
                        "Name" : "Chris",
                        "Marks" : 61
                },
                {
                        "Name" : "David",
                        "Marks" : 85
                }
        ]
}
{
        "_id" : ObjectId("6124681fa0581758e36006eb"),
        "details" : [
                {
                        "Name" : "Chris",
                        "Marks" : 78
                },
                {
                        "Name" : "Carol",
                        "Marks" : 66
                },
                {
                        "Name" : "David",
                        "Marks" : 85
                }
        ]
}

Here first, we unwind the details column. after that count the distinct Name and Marks and all this we store into a variable a.

var a = [
    { "$unwind": "$details" },
    {
       "$group": {
          "_id": {
             "Name": "$details.Name",
             "Marks": "$details.Marks"
          },
          "count": { "$sum": 1 }
       }
    },
    {
       "$group": {
          "_id": "$_id.Name",
          "distinctV": {
             "$addToSet": {
                "value": "$_id.Marks",
                "numberOfValues": "$count"
             }
          }
       }
    },
    {
       "$project": {
          "_id": 0,
          "Name": "$_id",
          "distinctV": 1
       }
    }
];

Now, you can easily display the result using the aggregation operation which produced the following output.

> db.demo.aggregate(a)
{ "distinctV" : [ { "value" : 85, "numberOfValues" : 2 } ], "Name" : "David" }
{ "distinctV" : [ { "value" : 78, "numberOfValues" : 2 }, { "value" : 61, "numberOfValues" : 1 } ], "Name" : "Chris" }
{ "distinctV" : [ { "value" : 66, "numberOfValues" : 1 } ], "Name" : "Carol" }

In this topic, you learned how to group by count distinct columns works in MongoDB and you understand how can you count distinct columns.

MongoDB group by count match

With the help of the match, we filter the documents to pass only the documents that match our condition.

Syntax:

{ $match: { <query> } }

Note that $match don’t accept the raw aggregation expressions. Like NOW, CLUSTER_TIME, ROOT, CURRENT, REMOVE etc. If you want to use these variables then you have to prefix the variable name with $$.

Example:

In this example, these are some of the documents that we inserted into the inventory collection.

> db.inventory.find().pretty()
{"_id": 1, "item": "abc1", "description": "product 1", "qty": 300 }
{"_id": 2, "item": "abc2", "description": "product 2", "qty": 200 }
{"_id": 3, "item": "xyz1", "description": "product 3", "qty": 250 }
{"_id": 4, "item": "VWZ1", "description": "product 4", "qty": 300 }
{"_id": 5, "item": "VWZ2", "description": "product 5", "qty": 180 }

we perform the equality match using the $match operation and simply display the result whose quantity(qty) is 300.

> db.inventory.aggregate([ {$match: {qty:300}} ])
{"_id" : 1, "item" : "abc1", "description" : "product 1", "qty" : 300 }
{"_id" : 4, "item" : "VWZ1", "description" : "product 4", "qty" : 300 }

In this topic, you learned how to perform equality match and only those document that satisfies the condition in MongoDB.

MongoDB group by count having

In the topic group by count having is totally dependent on the question requirement. For eg. you want to display a student having this particular item, student age is greater than this(any number), the item having this much of amount etc.

Here we are taking an example and display only those documents where the “totalSaleAmount” amount is this much.

Examples:

These are some documents that we inserted into the sales collection.

db.sales.insertMany([
{"_id" : 1, "item" : "abc", "price" : NumberDecimal("10"), "quantity" : NumberInt("2"), "date" : ISODate("2021-03-01T08:00:00Z") },
{"_id" : 2, "item" : "jkl", "price" : NumberDecimal("20"), "quantity" : NumberInt("1"), "date" : ISODate("2021-03-01T09:00:00Z") },
{"_id" : 3, "item" : "xyz", "price" : NumberDecimal("5"), "quantity" : NumberInt( "10"), "date" : ISODate("2021-03-15T09:00:00Z") },
{"_id" : 4, "item" : "xyz", "price" : NumberDecimal("5"), "quantity" :  NumberInt("20") , "date" : ISODate("2021-04-04T11:21:39.736Z") },
{"_id" : 5, "item" : "abc", "price" : NumberDecimal("10"), "quantity" : NumberInt("10") , "date" : ISODate("2021-04-04T21:23:13.331Z") }
])

You can access the documents by using the find() method.

> db.sales.find().pretty()
{
        "_id" : 1,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 2,
        "date" : ISODate("2021-03-01T08:00:00Z")
}
{
        "_id" : 2,
        "item" : "jkl",
        "price" : NumberDecimal("20"),
        "quantity" : 1,
        "date" : ISODate("2021-03-01T09:00:00Z")
}
{
        "_id" : 3,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 10,
        "date" : ISODate("2021-03-15T09:00:00Z")
}
{
        "_id" : 4,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 20,
        "date" : ISODate("2021-04-04T11:21:39.736Z")
}
{
        "_id" : 5,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 10,
        "date" : ISODate("2021-04-04T21:23:13.331Z")
}

Here, For the group by count having condition, we took the two-column price and quantity multiply these two-column and sum it and after that, we use the “$match” filter and display only those documents where condition totalSaleAmount greater than 100.

db.sales.aggregate(
  [
    {
      $group :
        {
          _id : "$item",
          totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }
        }
     },

     {
       $match: { "totalSaleAmount": { $gte: 100 } }
     }
   ]
 )
{ "_id" : "abc", "totalSaleAmount" : NumberDecimal("120") }
{ "_id" : "xyz", "totalSaleAmount" : NumberDecimal("150") }

In the output, you see only two items whose totalSaleAmount is greater than 100. This way you can easily use the having and display the only result that you want to retrieve.

In this topic, you learned how to retrieve the documents using having(condition) in MongoDB. You can do this easily as per the question requirement.

MongoDB group by date and count

Sometimes we only want to count a particular date document and for this, you can use count aggregation and count a particular date document.

Example:

In this example, you will learn how to calculate the total sale amount and sale count for each day in the year 2021.

We inserted some fields product id, item name, price, quantity and date into the sales collection. You can retrieve the documents by using the find() method.

> db.sales.find().pretty()
{
        "_id" : 1,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 2,
        "date" : ISODate("2021-03-01T08:00:00Z")
}
{
        "_id" : 2,
        "item" : "jkl",
        "price" : NumberDecimal("20"),
        "quantity" : 1,
        "date" : ISODate("2021-03-01T09:00:00Z")
}
{
        "_id" : 3,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 10,
        "date" : ISODate("2021-03-15T09:00:00Z")
}
{
        "_id" : 4,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 20,
        "date" : ISODate("2021-04-04T11:21:39.736Z")
}
{
        "_id" : 5,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 10,
        "date" : ISODate("2021-04-04T21:23:13.331Z")
}

After that, we use the aggregation operation to retrieve the data as a group by date and count.

db.date.aggregate([
  {
    $match : { "date": { $gte: new ISODate("2021-01-01"), $lt: new ISODate("2015-01-01") } }
  },

  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
       totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },

       count: { $sum: 1 }
    }
  },
  {
    $sort : { totalSaleAmount: -1 }
  }
 ])

{ "_id" : "2021-04-04", "totalSaleAmount" : NumberDecimal("200"), "count" : 2 }
{ "_id" : "2021-03-15", "totalSaleAmount" : NumberDecimal("50"), "count" : 1 }
{ "_id" : "2021-03-01", "totalSaleAmount" : NumberDecimal("40"), "count" : 2 }

In the output, you can see the Date, totalSaleAmount, and count are retrieved in descending order by using sort aggregation.

MongoDB group by count filter

In MongoDB, the group by count filter uses when we want to apply any filter on the documents. Like, documents where the price is greater than this(any number) and this will display only those particular documents.

Example:

In the example, we have stored some documents in the products collection and retrieved the documents with the help of the find() function.

> db.products.find().pretty()
{
        "_id" : 0,
        "items" : [
                {
                        "item_id" : 43,
                        "quantity" : 2,
                        "price" : 10
                },
                {
                        "item_id" : 2,
                        "quantity" : 1,
                        "price" : 500
                }
        ]
}
{
        "_id" : 1,
        "items" : [
                {
                        "item_id" : 23,
                        "quantity" : 3,
                        "price" : 210
                },
                {
                        "item_id" : 103,
                        "quantity" : 4,
                        "price" : 5
                },
                {
                        "item_id" : 38,
                        "quantity" : 1,
                        "price" : 300
                }
        ]
}
{
        "_id" : 2,
        "items" : [
                {
                        "item_id" : 4,
                        "quantity" : 1,
                        "price" : 112
                }
        ]
}

In the next step, we apply the aggregation operation and filter the documents as per the question requirement.

db.sales.aggregate([
   {
      $project: {
         items: {
            $filter: {
               input: "$items",
               as: "item",
               cond: { $gte: [ "$$item.price", 120 ] }
            }
         }
      }
   }
])

{ "_id" : 0, "items" : [ { "item_id" : 2, "quantity" : 1, "price" : 500} ] }
{ "_id" : 1, "items" : [{ "item_id" : 23, "quantity" : 3, "price" : 210}, { "item_id" : 38, "quantity" : 1, "price" : 300 } ] }
{ "_id" : 2, "items" : [ ] }

You can see in the output we filtered the result and display only those documents where the item price is greater than 120.

MongoDB group by count where

If you want to count particular documents using where condition and condition could be anything as per question demand. You will more understand with the help of examples:

Example:

In this example, we are going to calculate only those items where the item quantity is greater than 5. we stored some documents in the sales collection and you can access the documents by using the find() method.

> db.sales.find()
{ "_id":1, "item":"abc", "price":NumberDecimal("10"), "quantity":2}
{ "_id":2, "item":"jkl", "price":NumberDecimal("20"), "quantity":1}
{ "_id":3, "item":"xyz", "price":NumberDecimal("5"), "quantity":10}
{ "_id":4, "item":"xyz", "price":NumberDecimal("5"), "quantity":20}
{"_id":5, "item":"abc", "price":NumberDecimal("10"), "quantity":10}

Here, we are using $match aggregation to display where quantity is greater than 5 and use $count aggregation to count the number of items.

>db.sales.aggregate([ { $match: { 
                            quantity: { $gt: 5 } } }, 
                      { $count: "quantity" } 
                   ])
{ "quantity" : 4 }

This is how to count using the where condition in MongoDB. You can do this using different aggregation operations as per the question requirement.

MongoDB group by count array

In MongoDB, we stored an array of documents in the collection, and in this topic, you will learn how to group by count array in MongoDB.

Example:

In this example, we stored some fields item name, price, and quantity in the price collection.

> db.price.find()
{ "_id":1, "item":"abc", "price":NumberDecimal("10"), "quantity":2}
{ "_id":2, "item":"jkl", "price":NumberDecimal("20"), "quantity":1}
{ "_id":3, "item":"xyz", "price":NumberDecimal("5"), "quantity":10}
{ "_id":4, "item":"xyz", "price":NumberDecimal("5"), "quantity":20}
{ "_id":5, "item":"abc", "price":NumberDecimal("10"), "quantity":10}
{ "_id":6, "item":"def", "price":NumberDecimal("7.5"), "quantity":5}
{ "_id":7, "item":"def", "price":NumberDecimal("7.5"), "quantity":10}
{ "_id":8, "item":"abc", "price":NumberDecimal("10"), "quantity":5}

Here, we are counting the item using the $group and $count aggregation operation. 

> db.price.aggregate( { $group:{ _id:{item:"$item"}, count:{ $sum:1 } } })
{ "_id" : { "item" : "def" }, "count" : 2 }
{ "_id" : { "item" : "xyz" }, "count" : 2 }
{ "_id" : { "item" : "jkl" }, "count" : 1 }
{ "_id" : { "item" : "abc" }, "count" : 3 }

This is how to count groups by array documents in MongoDB. This is the simplest way to count array fields from documents.

MongoDB group and count with the condition

A few days back, I was working on a project where I was getting errors on how to retrieve particular documents where the employee age is greater than 25. So to solve this problem I studied more about this, how can we use “MongoDB group and count with the condition”.

Here I am taking an example and this might help you to understand how you can apply conditions to a group of fields and how to count them.

Example:

In the student collection, we inserted some documents and you can access them by using the find() method.

> db.student.find().pretty()
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74b"),
        "name" : "James",
        "age" : 21,
        "timestamp" : ISODate("2021-04-01T00:00:00Z")
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74c"),
        "name" : "Michael",
        "age" : 25,
        "timestamp" : ISODate("2021-10-31T00:00:00Z")
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74d"),
        "name" : "Maria",
        "age" : 29,
        "timestamp" : ISODate("2021-05-02T00:00:00Z")
}

Here, we also use the pretty() method that is used to display the result in an easy-to-read format.

In the next step, we apply different aggregation operations $match, $group, and $count and after applying the condition you can see the output there are only two fields where age is greater than equal 25.

> db.student.aggregate([ 
             {$match:{age: {$gte: 25}}}, 
             {$group: {_id: {name:"$name", age:"$age"}}}, 
             {$count: "nameAndAge"} 
])

{ "nameAndAge" : 2 }

This is how to apply group and count conditions on documents and count the documents of a particular field in MongoDB.

You may also like the following MongoDB tutorials:

In this tutorial, we have learned “MongoDB sort by count” using different approaches with examples. These are the following topics that we covered in this tutorial

  • What is an aggregation operation?
  • MongoDB group by count
  • MongoDB group by count sort
  • MongoDB group by count multiple fields
  • MongoDB group by count greater than 1
  • MongoDB group by count distinct
  • MongoDB group by count match
  • MongoDB group by count having
  • MongoDB group by date and count
  • MongoDB group by count filter
  • MongoDB group by count where
  • MongoDB group by count array
  • MongoDB group and count with the condition