MongoDB：聚集和分组例子

 

在本文章中，会演示MongoDB使用聚集函数对文档进行分组的用法。

1.测试数据

网站主机域名列表的JSON格式文件

 

 

website.json
{ "_id" : 1, "domainName" : "test1.com", "hosting" : "hostgator.com" }
{ "_id" : 2, "domainName" : "test2.com", "hosting" : "aws.amazon.com"}
{ "_id" : 3, "domainName" : "test3.com", "hosting" : "aws.amazon.com" }
{ "_id" : 4, "domainName" : "test4.com", "hosting" : "hostgator.com" }
{ "_id" : 5, "domainName" : "test5.com", "hosting" : "aws.amazon.com" }
{ "_id" : 6, "domainName" : "test6.com", "hosting" : "cloud.google.com" }
{ "_id" : 7, "domainName" : "test7.com", "hosting" : "aws.amazon.com" }
{ "_id" : 8, "domainName" : "test8.com", "hosting" : "hostgator.com" }
{ "_id" : 9, "domainName" : "test9.com", "hosting" : "cloud.google.com" }
{ "_id" : 10, "domainName" : "test10.com", "hosting" : "godaddy.com" }

 

 

导入website文档

 

 

> mongoimport -d testdb -c website --file website.json
connected to: 127.0.0.1
Mon Jan 13 14:30:22.662 imported 10 objects

 

 

 

注意
如果是已存在的文档, 加 --upsert 选项来覆盖数据.
> mongoimport -d testdb -c website --file website.json --upsert

 

 

2.分组例子

使用db.collection.aggregate和$group函数进行文档分组

  2.1 下面的例子通过“hosting”字段分组并显示每个主机总数

  

> db.website.aggregate(
    { 
	$group : {_id : "$hosting", total : { $sum : 1 }}
    }
  );

  结果

 

  

  

{
        "result" : [
                {
                        "_id" : "godaddy.com",
                        "total" : 1
                },
                {
                        "_id" : "cloud.google.com",
                        "total" : 2
                },
                {
                        "_id" : "aws.amazon.com",
                        "total" : 4
                },
                {
                        "_id" : "hostgator.com",
                        "total" : 3
                }
        ],
        "ok" : 1
}

 

 

用SQL可以表示为：

  

SELECT hosting, SUM(hosting) AS total
       FROM website
       GROUP BY hosting

 

 2.2 使用$sort函数进行排序

  

>  db.website.aggregate(
     { 
	$group : {_id : "$hosting", total : { $sum : 1 }}
     },
     {
	$sort : {total : -1}
     }
  );

 

  结果 -- “total“的降序显示，升序显示的话用$sort : {total : 1}实现

 

{
        "result" : [
                {
                        "_id" : "aws.amazon.com",
                        "total" : 4
                },
                {
                        "_id" : "hostgator.com",
                        "total" : 3
                },
                {
                        "_id" : "cloud.google.com",
                        "total" : 2
                },
                {
                        "_id" : "godaddy.com",
                        "total" : 1
                }
        ],
        "ok" : 1
}

 

  2.3 使用$match函数来对匹配”aws.amazon.com“的数据根据”hosting“分组

  

> db.website.aggregate(
    { 
	$match : {hosting : "aws.amazon.com"}
    },
    { 
	$group : { _id : "$hosting", total : { $sum : 1 } }
    }
  );

 

  输出：

  

{
        "result" : [
                {
                        "_id" : "aws.amazon.com",
                        "total" : 4
                }
        ],
        "ok" : 1
}

 

 详细例子请参照官方手册 MongoDB Aggregation guide 

 

3.把分组数据导出成CSV或者JSON格式文件

通过函数mongoexport来实现

  3.1 将分组的结果保存到变量中，本例变量为”groupdata“

  

> var groupdata = db.website.aggregate(
    { 
	$group : {_id : "$hosting", total : { $sum : 1 }}
    },
    {
	$sort : {total : -1}
    }
  );

 

  3.2 将”groupdata.result“插入到新集合中

  

> db.websitegroup.insert(groupdata.result);
 
> db.websitegroup.find().pretty()
{ "_id" : "aws.amazon.com", "total" : 4 }
{ "_id" : "hostgator.com", "total" : 3 }
{ "_id" : "cloud.google.com", "total" : 2 }
{ "_id" : "godaddy.com", "total" : 1 }
>

 

  3.3 将集合websitegroup导出到CSV文件

   

c:\> mongoexport -d testdb -c websitegroup -f _id,total -o group.csv --csv
connected to: 127.0.0.1
exported 4 records

    

    CSV文件中数据

    

group.csv
_id,total
"aws.amazon.com",4.0
"cloud.google.com",2.0
"godaddy.com",1.0
"hostgator.com",3.0

    

    3.4 将”websitegroup“集合中数据导出到JSON文件

    

c:\> mongoexport -d testdb -c websitegroup -o group.json
connected to: 127.0.0.1
exported 4 records

    

    group.json文件内容

    

group.json
{ "_id" : "aws.amazon.com", "total" : 4 }
{ "_id" : "cloud.google.com", "total" : 2 }
{ "_id" : "godaddy.com", "total" : 1 }
{ "_id" : "hostgator.com", "total" : 3 }