Main Tutorials

MongoDB – group, count and sort example

Some MongoDB examples to show you how to perform group by, count and sort query.

1. Test Data

A whois_range collection, containing many records.


> db.whois_range.find();
{
	"_id" : 1,
	"country" : "us",
	"source" : "ARIN",
	"status" : "NEW",
	"createdDate" : ISODate("2016-05-03T08:52:32.434Z")
},
{
	"_id" : 2,
	"country" : "us",
	"source" : "ARIN",
	"status" : "NEW",
	"createdDate" : ISODate("2016-05-03T09:52:32.434Z")
},
{
	"_id" : 3,
	"country" : "cn",
	"source" : "APNIC",
	"status" : "NEW",
	"createdDate" : ISODate("2016-05-03T10:52:32.434Z")
},
{
	"_id" : 4,
	"country" : "eu",
	"source" : "RIPE",
	"status" : "NEW",
	"createdDate" : ISODate("2016-05-03T10:52:32.434Z")
},
{...}

P.S “Source” = RIPE, AFRINIC, KRNIC, LACNIC, APNIC, JPNIC and ARIN

2. Group and Count example

Group by “source”, and count the total number of “source”.


> db.whois_range.aggregate([
		{"$group" : {_id:"$source", count:{$sum:1}}}
	])
	
{ "_id" : "RIPE", "count" : 29270 }
{ "_id" : "AFRINIC", "count" : 1326 }
{ "_id" : "KRNIC", "count" : 105 }
{ "_id" : "LACNIC", "count" : 5889 }
{ "_id" : "APNIC", "count" : 6644 }
{ "_id" : "JPNIC", "count" : 167 }
{ "_id" : "ARIN", "count" : 25429 }

3. Group by multiple ids example

Group by two ids: “source” and “status”.


> db.whois_range.aggregate([
		{"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}} ])
	])
	
{ "_id" : { "source" : "RIPE", "status" : "NEW" }, "count" : 29260 }
{ "_id" : { "source" : "RIPE", "status" : "ERROR" }, "count" : 10 }
{ "_id" : { "source" : "LACNIC", "status" : "NEW" }, "count" : 5889 }
{ "_id" : { "source" : "KRNIC", "status" : "NEW" }, "count" : 105 }
{ "_id" : { "source" : "APNIC", "status" : "NEW" }, "count" : 6644 }
{ "_id" : { "source" : "AFRINIC", "status" : "NEW" }, "count" : 1326 }
{ "_id" : { "source" : "JPNIC", "status" : "NEW" }, "count" : 167 }
{ "_id" : { "source" : "ARIN", "status" : "NEW" }, "count" : 25420 }
{ "_id" : { "source" : "ARIN", "status" : "DONE" }, "count" : 9 }

3. Group, Count, and Sort example

3.1 Group by two ids: “source” and “status”, count the total number of records, and sort by “source”.


> db.whois_range.aggregate([
	{"$group" : 
		{_id:{source:"$source",status:"$status"}, count:{$sum:1}}
	}, 
	{$sort:{"_id.source":1}}
])

{ "_id" : { "source" : "AFRINIC", "status" : "NEW" }, "count" : 1326 }
{ "_id" : { "source" : "APNIC", "status" : "NEW" }, "count" : 6644 }
{ "_id" : { "source" : "ARIN", "status" : "NEW" }, "count" : 25420 }
{ "_id" : { "source" : "ARIN", "status" : "DONE" }, "count" : 9 }
{ "_id" : { "source" : "JPNIC", "status" : "NEW" }, "count" : 167 }
{ "_id" : { "source" : "KRNIC", "status" : "NEW" }, "count" : 105 }
{ "_id" : { "source" : "LACNIC", "status" : "NEW" }, "count" : 5889 }
{ "_id" : { "source" : "RIPE", "status" : "NEW" }, "count" : 29260 }
{ "_id" : { "source" : "RIPE", "status" : "ERROR" }, "count" : 10 }

3.2 Sort by “count”, descending order.


> db.whois_range.aggregate([
	{"$group" : 
		{_id:{source:"$source",status:"$status"}, count:{$sum:1}}
	}, 
	{$sort:{"count":-1}}
])

{ "_id" : { "source" : "RIPE", "status" : "NEW" }, "count" : 29260 }
{ "_id" : { "source" : "ARIN", "status" : "NEW" }, "count" : 25420 }
{ "_id" : { "source" : "APNIC", "status" : "NEW" }, "count" : 6644 }
{ "_id" : { "source" : "LACNIC", "status" : "NEW" }, "count" : 5889 }
{ "_id" : { "source" : "AFRINIC", "status" : "NEW" }, "count" : 1326 }
{ "_id" : { "source" : "JPNIC", "status" : "NEW" }, "count" : 167 }
{ "_id" : { "source" : "KRNIC", "status" : "NEW" }, "count" : 105 }
{ "_id" : { "source" : "RIPE", "status" : "ERROR" }, "count" : 10 }
{ "_id" : { "source" : "ARIN", "status" : "DONE" }, "count" : 9 }

Done.

References

  1. MongoDB Aggregation manual
  2. MongoDB sort manual
  3. MongoDB – Aggregate and Group example

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
6 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Edit
6 years ago

suppose here in the object one more filed is available and date time and store value as a TimeStamp then how can print date only before count in the o/p? any idea any one please let me know. for ex => { “_id” : { “source” : “RIPE”, “status” : “NEW” },”Datre”:”16-10-2017″, “count” : 29260 }

viknesh
3 years ago

select location,count(*) from emp group by location having count(*)>1.

how to convert this to mongo.

could some one help to solve this
Thanks in advance.

Mike
3 years ago

good article.. maybe you can help me with my stack overflow question as well ?
https://stackoverflow.com/questions/62109178/how-do-i-sort-only-the-fields-project-returned-from-mongodb-aggregation-whil

Diwakar
4 years ago

Thanks

rthp
7 years ago

Hi, Can you explain me why are you doing $sum: 1 for count? Why is it 1?

Pradeep Rathore
3 years ago
Reply to  rthp

$sum: 1 basically tells the mongodb to add 1 to count everytime a document with same $group property is found. So the count variable is counting the number of docs for each group