MongoDB 高级查询（二）

约定不等于承诺〃 2022-08-07 06:40 164阅读 0赞

**1、键的筛选**  
键的筛选是查询时只返回自己感兴趣的键值，通过指定find的第二个参数来实现。这样可以节省传输的数据量，又能节省客户端解码文档的时间和内存消耗。  
可以指定要包含的字段（例如：｛field:1｝）或者指定要排除的字段（例如：｛field：0｝）。默认\_id是包含在结果集合中的，要从结果集中排除\_id字段，需要指定排除\_id字段（｛\_id:0｝）。

>db.c1.find();
    { "_id" : ObjectId("4fb5faaf6d0f9d8ea3fc91a8"), "name" : "Tony", "age" : 20 }
    { "_id" : ObjectId("4fb5fab96d0f9d8ea3fc91a9"), "name" : "Joe", "age" : 10 }
    { "_id" : ObjectId("4fb5faaf6d0f9d8ea3fc9110"), "name" : "Jack", "age" : 20 }
    > db.c1.find({},{_id:0,name:1})
    { "name" : "Tony" }
    { "name" : "Joe" }
    { "name" : "Jack" }

**2、Group**  
和数据库一样group常常用于统计。MongoDB的group还有很多限制，如：返回结果集不能超过16M， group操作不会处理超过10000个唯一键.  
参数：  
key：用来分组文档的字段。和keyf两者必须有一个  
keyf：可以接受一个javascript函数。用来动态的确定分组文档的字段。和key两者必须有一个  
initial：reduce中使用变量的初始化  
reduce：执行的reduce函数。函数需要返回值。  
cond：执行过滤的条件。  
finallize：在reduce执行完成，结果集返回之前对结果集最终执行的函数。可选的  
实例：

>db.c1.group({key:{age:true},initial:{num:0},$reduce:function(doc,prev){
    prev.num++
    }});
    [{
    	"age": 20,
    	"num": 2
    },
    {
    	"age": 10,
    	"num": 1
    }]
    >db.runCommand({group:
    {
    	ns: "c1",
    	key: {
    		age: true
    	},
    	initial: {
    		num: 0
    	},
    	$reduce: function(doc,
    	prev){
    		prev.num++
    	}
    }
    });
    {
    	"retval": [{
    		"age": 20,
    		"num": 2
    	},
    	{
    		"age": 10,
    		"num": 1
    	}],
    	"count": 3,
    	"keys": 2,
    	"ok": 1,
    	"$gleStats": {
    		"lastOpTime": {
    			"t": 0,
    			"i": 0
    		},
    		"electionId": ObjectId("5508de2cd812466ec2804cc9")
    	}
    }

参考：http://www.cnblogs.com/mxw09/archive/2011/08/31/2161457.html  
**3、游标使用**  
像大多数数据库产品一样，MongoDB 也是用游标来循环处理每一条结果数据。

> for(var c = db.c1.find(); c.hasNext(); ) { printjson( c.next()); }
    {
    "_id" : ObjectId("4fb5faaf6d0f9d8ea3fc91a8"),
    "name" : "Tony",
    "age" : 20
    }
    { "_id" : ObjectId("4fb5fab96d0f9d8ea3fc91a9"), "name" : "Joe", "age" : 10 }
    {
    "_id" : ObjectId("4fb5faaf6d0f9d8ea3fc9110"),
    "name" : "Jack",
    "age" : 20
    }

MongoDB 还有另一种方式来处理游标：

> db.c1.find().forEach( function(u){printjson(u);});
    {
    	"_id": ObjectId("4fb5faaf6d0f9d8ea3fc91a8"),
    	"name": "Tony",
    	"age": 20
    }{
    	"_id": ObjectId("4fb5fab96d0f9d8ea3fc91a9"),
    	"name": "Joe",
    	"age": 10
    }{
    	"_id": ObjectId("4fb5faaf6d0f9d8ea3fc9110"),
    	"name": "Jack",
    	"age": 20
    }

**4、存储过程**

自定义函数转换为MongoDB 的存储过程:

>use admin

>db.system.js.save(\{\_id:"addNumbers", value:function(x, y)\{ return x + y; \}\});  
存储过程可以被查看，修改和删除，所以我们用find 来查看一下是否这个存储过程已经被创建上了。  
> db.system.js.find();  
\{ "\_id" : "addNumbers", "value" : function cf\_\_1\_\_f\_(x, y) \{  
    return x + y;  
\} \}  
使用：  
>db.eval('addNumbers(3, 4.2)');  
7.2  
再如：  
> db.system.js.save(\{\_id:"get\_count", value:function()\{ return db.c1.count(); \}\});  
> db.eval('get\_count()')  
3

**5、MongoDB 聚合**

MongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等)，并返回计算后的数据结果。有点类似sql语句中的 count(\*)。

MongoDB中聚合的方法使用aggregate()。  
语法  
aggregate() 方法的基本语法格式如下所示：  
>db.COLLECTION\_NAME.aggregate(AGGREGATE\_OPERATION)  
一些聚合的表达式:

<table style=""> 
 <tbody> 
  <tr style="background-color:rgb(246,244,240)"> 
   <th style="font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; color:rgb(255,255,255); border:1px solid rgb(85,85,85); padding:3px; vertical-align:top; text-align:left; width:67px; background-color:rgb(85,85,85)"> 表达式</th> 
   <th style="font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; color:rgb(255,255,255); border:1px solid rgb(85,85,85); padding:3px; vertical-align:top; text-align:left; width:354px; background-color:rgb(85,85,85)"> 描述</th> 
   <th style="font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; color:rgb(255,255,255); border:1px solid rgb(85,85,85); padding:3px; vertical-align:top; text-align:left; background-color:rgb(85,85,85)"> 实例</th> 
  </tr> 
  <tr style="background-color:rgb(255,255,255)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $sum</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 计算总和。</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(246,244,240)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $avg</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 计算平均值</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(255,255,255)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $min</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 获取集合中所有文档对应值得最小值。</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(246,244,240)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $max</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 获取集合中所有文档对应值得最大值。</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(255,255,255)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $push</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 在结果文档中插入值到一个数组中。</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(246,244,240)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $addToSet</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 在结果文档中插入值到一个数组中，但不创建副本。</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(255,255,255)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $first</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 根据资源文档的排序获取第一个文档数据。</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", first_url : {$first : "$url"}}}])</td> 
  </tr> 
  <tr style="background-color:rgb(246,244,240)"> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> $last</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> 根据资源文档的排序获取最后一个文档数据</td> 
   <td style="line-height:16.7999992370605px; font-size:12px; font-family:'Microsoft Yahei','Helvetica Neue',Helvetica,Arial,sans-serif; border:1px solid rgb(212,212,212); padding:7px 5px; vertical-align:top"> db.mycol.aggregate([{$group : {_id : "$by_user", last_url : {$last : "$url"}}}])</td> 
  </tr> 
 </tbody> 
</table>

参考：[http://www.w3cschool.cc/mongodb/mongodb-aggregate.html][http_www.w3cschool.cc_mongodb_mongodb-aggregate.html]

各种聚合函数的实现:[http://www.cnblogs.com/shanyou/archive/2012/08/05/2624073.html][http_www.cnblogs.com_shanyou_archive_2012_08_05_2624073.html]

[http_www.w3cschool.cc_mongodb_mongodb-aggregate.html]: http://www.w3cschool.cc/mongodb/mongodb-aggregate.html
[http_www.cnblogs.com_shanyou_archive_2012_08_05_2624073.html]: http://www.cnblogs.com/shanyou/archive/2012/08/05/2624073.html