MongoDB group, count and sort example

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

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

上一篇: Logback Set log file name programmatically
下一篇: Java find class fields with specified data type
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章或者资源来源于互联网, 有时候很难判断是否侵权, 若有侵权, 请联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布无版权争议的 文章/资源.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号