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
author image

mkyong

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. Read all published posts by

Comments

avatar
3000
newest oldest most voted
Edit
Guest
Edit

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 }

Mike
Guest
Mike
Diwakar
Guest
Diwakar

Thanks

rthp
Guest
rthp

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