MongoDB之聚合
MongoDB的產生背景是在大資料環境，所謂的大資料實際上就是資訊的收集彙總。那麼就必須存在資訊的統計操作，而這樣的統計操作就稱為聚合（分組統計就是一種聚合操作）。
1、取得集合的資料量
對於集合的資料量而言，在MongoDB裡面直接使用count()函式就可以完成了。
範例：統計emp表中的資料量
> db.emp.count();
8
範例：模糊查詢
> db.emp.count({"name":/孫/i})
1
2、消除重複資料
在學習SQL的時候對於重複的資料可以使用“DISTINCE”，那麼這一操作在MongoDB之中依然支援。
範例：查詢所有name的資訊
本次的操作沒有直接的函式支援，只能夠利用Command()指令。
> db.runCommand({"distinct":"emp","key":"name"})
{
"values" : [
"趙一",
"錢二",
"孫三",
"李四",
"週五",
"吳六",
"鄭七",
"王八"
],
"ok" : 1
}
此時實現了對於name資料重複值的篩選。
3、group操作
使用“group”操作可以實現資料的分組操作，在MongoDB裡面會將集合依據指定的key進行分組操作。並且每一組都會產生一個處理的文件結果。
範例：查詢所有年齡大於等於30歲的人員，並且按照年齡分組
> db.runCommand({"group":{
... "ns":"emp",
... "key":{"age":true},
... "initial":{"count":0},
... "condition":{"age":{"$gte":30}},
... "$reduce":function(doc,prev){prev.count++;}
... }})
{
"retval" : [
{
"age" : 30,
"count" : 4
},
{
"age" : 40,
"count" : 1
},
{
"age" : 50,
"count" : 1
},
{
"age" : 35,
"count" : 2
}
],
"count" : NumberLong(8),
"keys" : NumberLong(4),
"ok" : 1
}
以上的操作程式碼就屬於一種MapReduce，很少這樣寫。這樣只是根據傳統的資料庫的設計思路，實現了一個所謂的分組操作。但是這個分組操作最終結果是有限的。
4、MapReduce
MapReduce是整個大資料的精髓所在（實際中別用），所謂的MapReduce實際上就是分為兩步處理資料：
● Map：將資料分別取出
● Reduce：負責資料的最後處理
可是要想在MongoDB裡面實現MapReduce處理，那麼複雜度是相當高的。
範例：建立一組人員資料
db.emps.insert({"name":"趙一","age":30,"sex":"女","job":"CLERK","sal":1000});
db.emps.insert({"name":"錢二","age":22,"sex":"男","job":"ACCOUNT","sal":2000});
db.emps.insert({"name":"孫三","age":28,"sex":"女","job":"CLERK","sal":3000});
db.emps.insert({"name":"李四","age":35,"sex":"女","job":"IT","sal":4000});
db.emps.insert({"name":"週五","age":31,"sex":"男","job":"SEC","sal":5000});
db.emps.insert({"name":"吳六","age":40,"sex":"女","job":"MANAGER","sal":6000});
db.emps.insert({"name":"鄭七","age":44,"sex":"男","job":"CLERK","sal":1500});
db.emps.insert({"name":"王八","age":55,"sex":"男","job":"SAL","sal":5500});
使用MapReduce操作最終會將處理結果儲存在一個單獨的集合裡面，而最終的處理效果如下：
範例：按照職位分組，取得每個職位的姓名
第一步：編寫分組的定義：
var jobMapFun=function(){
emit(this.job,this.name);
}
第二步：編寫Reduce操作：
> var jobReduceFun=function(key,values){
... return {"job":key,"name":values};
... }
第三步：針對於MapReduce處理完成的資料實際上也可以執行一個最後處理。
var jobFinalizeFun=function(key,values){
if(key=="MANAGER"){
return {"job":key,"name":values,"info":"公司老大"};}
return {"job":key,"name":values};
}
進行操作的整合：
db.runCommand({
"mapreduce":"emps",
"map":jobMapFun,
"reduce":jobReduceFun,
"out":"t_emps_job",
"finalize":jobFinalizeFun});
{
"result" : "t_emps_job",
"timeMillis" : 28,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 1,
"output" : 6
},
"ok" : 1
}
現在執行之後，所有的處理結果都儲存在了"t_emps_job"集合裡面。
> db.t_emps_job.find();
{ "_id" : "ACCOUNT", "value" : { "job" : "ACCOUNT", "name" : "錢二" } }
{ "_id" : "CLERK", "value" : { "job" : "CLERK", "name" : { "job" : "CLERK", "name" : [ "趙一", "孫三", "鄭七" ] } } }
{ "_id" : "IT", "value" : { "job" : "IT", "name" : "李四" } }
{ "_id" : "MANAGER", "value" : { "job" : "MANAGER", "name" : "吳六", "info" : "公司老大" } }
{ "_id" : "SAL", "value" : { "job" : "SAL", "name" : "王八" } }
{ "_id" : "SEC", "value" : { "job" : "SEC", "name" : "週五" } }
範例：統計出各性別的人數，平均工資，最低工資，僱員姓名
var sexMapFun=function(){
emit(this.sex,{"ccount":1,"csal":this.sal,"cmax":this.sal,"cmin":this.sal,"cname":this.name}); //定義分組的條件以及每個集合要取出的內容
}
var sexReduceFun=function(key,values){
var total=0; //統計
var sum=0; //計算總工資
var max=values[0].cmax; //假設第一個資料是最高工資
var min=values[0].cmin; //假設第一個資料是最低工資
var names=new Array(); //定義陣列內容
for (var x in values){ //迴圈取出裡面的資料
total += values[x].ccount; //人數增加
sum += values[x].csal; //迴圈取出所有的工資，並且累加
if (max<values[x].cmax){
max=values[x].cmax; //找到最高工資
}
if (min>values[x].cmin){
min=values[x].cmin; //找到最低工資
}
names[x]=values[x].cname; //儲存姓名
}
var avg=(sum/total).toFixed(2);
return {"count":total,"avg":avg,"max":max,"min":min,"name":names};
}; //返回資料處理結果
db.runCommand({
"mapreduce":"emps",
"map":sexMapFun,
"reduce":sexReduceFun,
"out":"t_emps_sex"
})
{
"result" : "t_emps_sex",
"timeMillis" : 31,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 2
},
"ok" : 1
}
> db.t_emps_sex.find().pretty();
{
"_id" : "女",
"value" : {
"count" : 4,
"avg" : "3500.00",
"max" : 6000,
"min" : 1000,
"name" : [
"趙一",
"孫三",
"李四",
"吳六"
]
}
}
{
"_id" : "男",
"value" : {
"count" : 4,
"avg" : "3500.00",
"max" : 5500,
"min" : 1500,
"name" : [
"錢二",
"週五",
"鄭七",
"王八"
]
}
}
雖然大資料時代提供有最強悍的MapReduce支援，但是從現實的開發來講，真的不可能使用起來。
5、聚合框架
MapReduce功能強大，但是複雜度太大，很多時候需要MapReduce功能，可是又不想把程式碼寫得太複雜。所以在MongoDB 2.x版本之後開始引入了聚合框架並且提供了聚合函式：aggregate()。
5.1 $group
group主要進行分組的資料操作。
範例：實現聚合查詢的功能--求出每個職位的人員數量
> db.emps.aggregate([{"$group":{"_id":"$job",job_count:{"$sum":1}}}])
{ "_id" : "MANAGER", "job_count" : 1 }
{ "_id" : "ACCOUNT", "job_count" : 1 }
{ "_id" : "IT", "job_count" : 1 }
{ "_id" : "SAL", "job_count" : 1 }
{ "_id" : "CLERK", "job_count" : 3 }
{ "_id" : "SEC", "job_count" : 1 }
這樣的操作更加符合傳統的group by子句的操作使用。
範例：求出每個職位的總工資
> db.emps.aggregate([{"$group":{"_id":"$job",job_sal:{"$sum":"$sal"}}}])
{ "_id" : "MANAGER", "job_sal" : 6000 }
{ "_id" : "ACCOUNT", "job_sal" : 2000 }
{ "_id" : "IT", "job_sal" : 4000 }
{ "_id" : "SAL", "job_sal" : 5500 }
{ "_id" : "CLERK", "job_sal" : 5500 }
{ "_id" : "SEC", "job_sal" : 5000 }
在整個聚合框架裡面如果要引用每行的資料使用："$欄位名稱"。
範例：計算出每個職位的平均工資
> db.emps.aggregate([{"$group":{"_id":"$job","job_sal":{"$sum":"$sal"},"job_avg":{"$avg":"$sal"}}}])
{ "_id" : "MANAGER", "job_sal" : 6000, "job_avg" : 6000 }
{ "_id" : "ACCOUNT", "job_sal" : 2000, "job_avg" : 2000 }
{ "_id" : "IT", "job_sal" : 4000, "job_avg" : 4000 }
{ "_id" : "SAL", "job_sal" : 5500, "job_avg" : 5500 }
{ "_id" : "CLERK", "job_sal" : 5500, "job_avg" : 1833.3333333333333 }
{ "_id" : "SEC", "job_sal" : 5000, "job_avg" : 5000 }
範例：求出最高與最低工資
> db.emps.aggregate([{"$group":{"_id":"$job","max_sal":{"$max":"$sal"},"min_avg":{"$min":"$sal"}}}])
{ "_id" : "MANAGER", "max_sal" : 6000, "min_avg" : 6000 }
{ "_id" : "ACCOUNT", "max_sal" : 2000, "min_avg" : 2000 }
{ "_id" : "IT", "max_sal" : 4000, "min_avg" : 4000 }
{ "_id" : "SAL", "max_sal" : 5500, "min_avg" : 5500 }
{ "_id" : "CLERK", "max_sal" : 3000, "min_avg" : 1000 }
{ "_id" : "SEC", "max_sal" : 5000, "min_avg" : 5000 }
以上的幾個與SQL類似的操作計算成功實現了。
範例：計算出每個職位的工資資料（陣列顯示）
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$push":"$sal"}}}])
{ "_id" : "MANAGER", "sal_data" : [ 6000 ] }
{ "_id" : "ACCOUNT", "sal_data" : [ 2000 ] }
{ "_id" : "IT", "sal_data" : [ 4000 ] }
{ "_id" : "SAL", "sal_data" : [ 5500 ] }
{ "_id" : "CLERK", "sal_data" : [ 1000, 3000, 1500 ] }
{ "_id" : "SEC", "sal_data" : [ 5000 ] }
範例：求出每個職位的人員
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$push":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : [ "吳六" ] }
{ "_id" : "ACCOUNT", "sal_data" : [ "錢二" ] }
{ "_id" : "IT", "sal_data" : [ "李四" ] }
{ "_id" : "SAL", "sal_data" : [ "王八" ] }
{ "_id" : "CLERK", "sal_data" : [ "趙一", "孫三", "鄭七" ] }
{ "_id" : "SEC", "sal_data" : [ "週五" ] }
使用“$push”的確可以將資料變為陣列進行儲存，但是有一個問題出現了，重複的內容也會進行儲存，那麼在MongoDB裡面提供有取消重複的設定。
> db.emps.insert({"name":"吳六","age":40,"sex":"女","job":"MANAGER","sal":6000});
WriteResult({ "nInserted" : 1 })
> db.emps.insert({"name":"鄭七","age":44,"sex":"男","job":"CLERK","sal":1500});
WriteResult({ "nInserted" : 1 })
> db.emps.insert({"name":"王八","age":55,"sex":"男","job":"SAL","sal":5500});
WriteResult({ "nInserted" : 1 })
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$push":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : [ "吳六", "吳六" ] }
{ "_id" : "ACCOUNT", "sal_data" : [ "錢二" ] }
{ "_id" : "IT", "sal_data" : [ "李四" ] }
{ "_id" : "SAL", "sal_data" : [ "王八", "王八" ] }
{ "_id" : "CLERK", "sal_data" : [ "趙一", "孫三", "鄭七", "鄭七" ] }
{ "_id" : "SEC", "sal_data" : [ "週五" ] }
範例：取消重複的資料
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$addToSet":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : [ "吳六" ] }
{ "_id" : "ACCOUNT", "sal_data" : [ "錢二" ] }
{ "_id" : "IT", "sal_data" : [ "李四" ] }
{ "_id" : "SAL", "sal_data" : [ "王八" ] }
{ "_id" : "CLERK", "sal_data" : [ "鄭七", "孫三", "趙一" ] }
{ "_id" : "SEC", "sal_data" : [ "週五" ] }
預設情況下是將所有的資料都儲存進去了，但是現在只希望可以保留第一個或者是最後一個。
範例：儲存第一個內容
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$first":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : "吳六" }
{ "_id" : "ACCOUNT", "sal_data" : "錢二" }
{ "_id" : "IT", "sal_data" : "李四" }
{ "_id" : "SAL", "sal_data" : "王八" }
{ "_id" : "CLERK", "sal_data" : "趙一" }
{ "_id" : "SEC", "sal_data" : "週五" }
範例：儲存最後一個內容
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$last":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : "吳六" }
{ "_id" : "ACCOUNT", "sal_data" : "錢二" }
{ "_id" : "IT", "sal_data" : "李四" }
{ "_id" : "SAL", "sal_data" : "王八" }
{ "_id" : "CLERK", "sal_data" : "鄭七" }
{ "_id" : "SEC", "sal_data" : "週五" }
雖然可以方便的實現分組處理，但是有一點需要注意，所有的分組資料是無序的，並且都是在記憶體之中完成的，所以不可能支援大資料量。
5.2 $project
可以利用$project來控制資料列的顯示規則，規則如下：
● 普通列({成員:1|true})：表示要顯示的內容
● “_id”列({"_id":0|false})：表示“_id”列是否顯示
● 條件過濾列({成員:表示式})：滿足表示式之後的資料可以進行顯示
範例：只顯示name、job列，不顯示_id列
> db.emps.aggregate([{"$project":{"_id":0,"name":1}}])
{ "name" : "趙一" }
{ "name" : "錢二" }
{ "name" : "孫三" }
{ "name" : "李四" }
{ "name" : "週五" }
{ "name" : "吳六" }
{ "name" : "鄭七" }
{ "name" : "王八" }
{ "name" : "吳六" }
{ "name" : "鄭七" }
{ "name" : "王八" }
此時只有設定進去的列才可以被顯示出來，而其他的列不能被顯示出來。實際上這就屬於資料庫的投影機制。
實際上在資料投影的過程裡面也支援四則運算：加法（"$add"）、減法（"$subtract"）、乘法（“$mulitipy”）、除法（“”$devided）、求模（"$mod"）
範例：四則運算
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"職位":"$job","sal":1}}])
{ "name" : "趙一", "sal" : 1000, "職位" : "CLERK" }
{ "name" : "錢二", "sal" : 2000, "職位" : "ACCOUNT" }
{ "name" : "孫三", "sal" : 3000, "職位" : "CLERK" }
{ "name" : "李四", "sal" : 4000, "職位" : "IT" }
{ "name" : "週五", "sal" : 5000, "職位" : "SEC" }
{ "name" : "吳六", "sal" : 6000, "職位" : "MANAGER" }
{ "name" : "鄭七", "sal" : 1500, "職位" : "CLERK" }
{ "name" : "王八", "sal" : 5500, "職位" : "SAL" }
{ "name" : "吳六", "sal" : 6000, "職位" : "MANAGER" }
{ "name" : "鄭七", "sal" : 1500, "職位" : "CLERK" }
{ "name" : "王八", "sal" : 5500, "職位" : "SAL" }
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"年薪":{"$multiply":["$sal",12]}}}])
{ "name" : "趙一", "job" : "CLERK", "年薪" : 12000 }
{ "name" : "錢二", "job" : "ACCOUNT", "年薪" : 24000 }
{ "name" : "孫三", "job" : "CLERK", "年薪" : 36000 }
{ "name" : "李四", "job" : "IT", "年薪" : 48000 }
{ "name" : "週五", "job" : "SEC", "年薪" : 60000 }
{ "name" : "吳六", "job" : "MANAGER", "年薪" : 72000 }
{ "name" : "鄭七", "job" : "CLERK", "年薪" : 18000 }
{ "name" : "王八", "job" : "SAL", "年薪" : 66000 }
{ "name" : "吳六", "job" : "MANAGER", "年薪" : 72000 }
{ "name" : "鄭七", "job" : "CLERK", "年薪" : 18000 }
{ "name" : "王八", "job" : "SAL", "年薪" : 66000 }
除了四則運算之外也支援如下的各種運算子：
● 關係運算：大小比較（"$cmp"）、等於（"$eq"）、大於（"$gt"）、大於等於（"$gte"）、小於（"$lt"）、小於等於（"$lte"）、不等於（"$ne"）、判斷NULL（"$ifNull"），這些操作返回的結果都是布林型資料。
● 邏輯運算：與（"$and"）、或（"$or"）、非（"$not"）
● 字串操作：連線（"$concat"）、擷取（"$substr"）、轉小寫（"$toLower"）、轉大小（"$toUpper"）、不區分大小寫比較（"$strcasecmp"）
範例：找出所有薪水大於等於2000的人員姓名，職位和薪水
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"工資":"$sal","是否大於2000":{"$gte":["$sal",2000]}}}])
{ "name" : "趙一", "job" : "CLERK", "工資" : 1000, "是否大於2000" : false }
{ "name" : "錢二", "job" : "ACCOUNT", "工資" : 2000, "是否大於2000" : true }
{ "name" : "孫三", "job" : "CLERK", "工資" : 3000, "是否大於2000" : true }
{ "name" : "李四", "job" : "IT", "工資" : 4000, "是否大於2000" : true }
{ "name" : "週五", "job" : "SEC", "工資" : 5000, "是否大於2000" : true }
{ "name" : "吳六", "job" : "MANAGER", "工資" : 6000, "是否大於2000" : true }
{ "name" : "鄭七", "job" : "CLERK", "工資" : 1500, "是否大於2000" : false }
{ "name" : "王八", "job" : "SAL", "工資" : 5500, "是否大於2000" : true }
{ "name" : "吳六", "job" : "MANAGER", "工資" : 6000, "是否大於2000" : true }
{ "name" : "鄭七", "job" : "CLERK", "工資" : 1500, "是否大於2000" : false }
{ "name" : "王八", "job" : "SAL", "工資" : 5500, "是否大於2000" : true }
範例：查詢職位是manager的資訊
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"$eq":["$job","MANAGER"]}}}])
{ "name" : "趙一", "job" : false, "職位" : "CLERK" }
{ "name" : "錢二", "job" : false, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : false, "職位" : "CLERK" }
{ "name" : "李四", "job" : false, "職位" : "IT" }
{ "name" : "週五", "job" : false, "職位" : "SEC" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"$eq":["$job",{"$toUpper":"manager"}]}}}])
{ "name" : "趙一", "job" : false, "職位" : "CLERK" }
{ "name" : "錢二", "job" : false, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : false, "職位" : "CLERK" }
{ "name" : "李四", "job" : false, "職位" : "IT" }
{ "name" : "週五", "job" : false, "職位" : "SEC" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"$strcasecmp":["$job","manager"]}}}])
{ "name" : "趙一", "job" : -1, "職位" : "CLERK" }
{ "name" : "錢二", "job" : -1, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : -1, "職位" : "CLERK" }
{ "name" : "李四", "job" : -1, "職位" : "IT" }
{ "name" : "週五", "job" : 1, "職位" : "SEC" }
{ "name" : "吳六", "job" : 0, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : -1, "職位" : "CLERK" }
{ "name" : "王八", "job" : 1, "職位" : "SAL" }
{ "name" : "吳六", "job" : 0, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : -1, "職位" : "CLERK" }
{ "name" : "王八", "job" : 1, "職位" : "SAL" }
範例：使用字串擷取
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"前面三位":{"$substr":["$job",0,3]}}}}])
{ "name" : "趙一", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "錢二", "job" : { "前面三位" : "ACC" }, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "李四", "job" : { "前面三位" : "IT" }, "職位" : "IT" }
{ "name" : "週五", "job" : { "前面三位" : "SEC" }, "職位" : "SEC" }
{ "name" : "吳六", "job" : { "前面三位" : "MAN" }, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "王八", "job" : { "前面三位" : "SAL" }, "職位" : "SAL" }
{ "name" : "吳六", "job" : { "前面三位" : "MAN" }, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "王八", "job" : { "前面三位" : "SAL" }, "職位" : "SAL" }
利用"$project"實現的投影操作功能相當強大，所有可能出現的操作幾乎都能夠使用。
5.3、$sort
使用"$sort"可以實現排序，設定1表示升序，設定-1表示降序。
範例：實現排序
> db.emps.aggregate([{"$project":{"_id":0,"age":1,"sal":1}},{"$sort":{"age":-1,"sal":1}}])
{ "age" : 55, "sal" : 5500 }
{ "age" : 55, "sal" : 5500 }
{ "age" : 44, "sal" : 1500 }
{ "age" : 44, "sal" : 1500 }
{ "age" : 40, "sal" : 6000 }
{ "age" : 40, "sal" : 6000 }
{ "age" : 35, "sal" : 4000 }
{ "age" : 31, "sal" : 5000 }
{ "age" : 30, "sal" : 1000 }
{ "age" : 28, "sal" : 3000 }
{ "age" : 22, "sal" : 2000 }
> db.emps.aggregate([{"$match":{"sal":{"$gte":3000,"$lte":5000}}},{"$project":{"_id":0,"age":1,"sal":1}},{"$sort":{"age":-1,"sal":1}}])
{ "age" : 35, "sal" : 4000 }
{ "age" : 31, "sal" : 5000 }
{ "age" : 28, "sal" : 3000 }
> db.emps.aggregate([
... {"$match":{"sal":{"$gte":3000,"$lte":6000}}},
... {"$project":{"_id":0,"age":1,"sal":1}},
... {"$group":{"_id":"$age","count":{"$sum":1}}},
... {"$sort":{"count":-1}}])
{ "_id" : 55, "count" : 2 }
{ "_id" : 40, "count" : 2 }
{ "_id" : 28, "count" : 1 }
{ "_id" : 35, "count" : 1 }
{ "_id" : 31, "count" : 1 }
> db.emps.aggregate([
... {"$match":{"sal":{"$gte":3000,"$lte":6000}}},
... {"$project":{"_id":0,"name":1,"sal":1,"job":1}},
... {"$group":{"_id":"$job","count":{"$sum":1},"avg":{"$avg":"$sal"}}},
... {"$sort":{"count":-1}}])
{ "_id" : "SAL", "count" : 2, "avg" : 5500 }
{ "_id" : "MANAGER", "count" : 2, "avg" : 6000 }
{ "_id" : "IT", "count" : 1, "avg" : 4000 }
{ "_id" : "CLERK", "count" : 1, "avg" : 3000 }
{ "_id" : "SEC", "count" : 1, "avg" : 5000 }
5.4、分頁處理：$limit、$skip
"$limit"：資料取出個數
“$skip”：資料跨過個數
範例：使用“$limit”設定取出個數
> db.emps.aggregate([
... {"$project":{"_id":0,"name":1,"sal":1}},
... {"$limit":2}
... ])
{ "name" : "趙一", "sal" : 1000 }
{ "name" : "錢二", "sal" : 2000 }
範例：跨過3行資料
> db.emps.aggregate([
... {"$project":{"_id":0,"name":1,"sal":1}},
... {"$skip":3},
... {"$limit":2}
... ])
{ "name" : "李四", "sal" : 4000 }
{ "name" : "週五", "sal" : 5000 }
> db.emps.aggregate([
... {"$match":{"sal":{"$gte":3000,"$lte":6000}}},
... {"$project":{"_id":0,"name":1,"sal":1,"job":1}},
... {"$group":{"_id":"$job","count":{"$sum":1},"avg":{"$avg":"$sal"}}},
... {"$sort":{"count":-1}},
... {"$skip":3},
... {"$limit":2}
... ])
{ "_id" : "IT", "count" : 1, "avg" : 4000 }
{ "_id" : "CLERK", "count" : 1, "avg" : 3000 }
5.5 $unwind
在查詢資料的時候經常會返回陣列資訊，但是陣列並不方便資訊的瀏覽，所以提供有"$unwind"可以將陣列資料變為對立的字串內容。
範例：新增一些資訊
db.depts.insert({"title":"技術部","busi":["研發","生產","培訓"]});
db.depts.insert({"title":"技術部","busi":["薪酬","稅務"]});
範例：將資訊進行轉化
> db.depts.aggregate([
... {"$project":{"_id":0,"title":1,"busi":1}},
... {"$unwind":"$busi"}
... ]);
{ "title" : "技術部", "busi" : "研發" }
{ "title" : "技術部", "busi" : "生產" }
{ "title" : "技術部", "busi" : "培訓" }
{ "title" : "技術部", "busi" : "薪酬" }
{ "title" : "技術部", "busi" : "稅務" }
此時相當於將陣列的資料變為了單行的資料。
5.6 $geoNear
使用"$geoNear"可以得到附件的座標點。
範例：準備測試資料
db.shop.drop();
db.shop.insert({loc:[10,10]});
db.shop.insert({loc:[20,10]});
db.shop.insert({loc:[10,20]});
db.shop.insert({loc:[20,20]});
db.shop.insert({loc:[100,100]});
db.shop.insert({loc:[80,30]});
db.shop.insert({loc:[30,50]});
db.shop.createIndex({"loc":"2d"})
範例：設定查詢
> db.shop.aggregate([
... {"$geoNear":{"near":[30,30],"distanceField":"loc","maxDistance":20,"num":2}}
... ])
{ "_id" : ObjectId("5994ff7c0184ff511bf02bdc"), "loc" : 14.142135623730951 }
{ "_id" : ObjectId("5994ff7d0184ff511bf02bdf"), "loc" : 20 }
地理資訊的檢索必須存在索引的支援。
5.7 $out
"$out"可以將查詢結果輸出到指定的集合裡面。
範例：將投影的結果輸出到集合裡
> db.emps.aggregate([
... {"$project":{"_id":0,"name":1,"sal":1}},
... {"$out":"emps_infos"}
... ])
> db.emps_infos.find();
{ "_id" : ObjectId("599501eeca6455d4a46874e0"), "name" : "趙一", "sal" : 1000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e1"), "name" : "錢二", "sal" : 2000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e2"), "name" : "孫三", "sal" : 3000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e3"), "name" : "李四", "sal" : 4000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e4"), "name" : "週五", "sal" : 5000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e5"), "name" : "吳六", "sal" : 6000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e6"), "name" : "鄭七", "sal" : 1500 }
{ "_id" : ObjectId("599501eeca6455d4a46874e7"), "name" : "王八", "sal" : 5500 }
{ "_id" : ObjectId("599501eeca6455d4a46874e8"), "name" : "吳六", "sal" : 6000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e9"), "name" : "鄭七", "sal" : 1500 }
{ "_id" : ObjectId("599501eeca6455d4a46874ea"), "name" : "王八", "sal" : 5500 }
相當於實現了資料表的複製操作。
1、取得集合的資料量
對於集合的資料量而言，在MongoDB裡面直接使用count()函式就可以完成了。
範例：統計emp表中的資料量
> db.emp.count();
8
範例：模糊查詢
> db.emp.count({"name":/孫/i})
1
2、消除重複資料
在學習SQL的時候對於重複的資料可以使用“DISTINCE”，那麼這一操作在MongoDB之中依然支援。
範例：查詢所有name的資訊
本次的操作沒有直接的函式支援，只能夠利用Command()指令。
> db.runCommand({"distinct":"emp","key":"name"})
{
"values" : [
"趙一",
"錢二",
"孫三",
"李四",
"週五",
"吳六",
"鄭七",
"王八"
],
"ok" : 1
}
此時實現了對於name資料重複值的篩選。
3、group操作
使用“group”操作可以實現資料的分組操作，在MongoDB裡面會將集合依據指定的key進行分組操作。並且每一組都會產生一個處理的文件結果。
範例：查詢所有年齡大於等於30歲的人員，並且按照年齡分組
> db.runCommand({"group":{
... "ns":"emp",
... "key":{"age":true},
... "initial":{"count":0},
... "condition":{"age":{"$gte":30}},
... "$reduce":function(doc,prev){prev.count++;}
... }})
{
"retval" : [
{
"age" : 30,
"count" : 4
},
{
"age" : 40,
"count" : 1
},
{
"age" : 50,
"count" : 1
},
{
"age" : 35,
"count" : 2
}
],
"count" : NumberLong(8),
"keys" : NumberLong(4),
"ok" : 1
}
以上的操作程式碼就屬於一種MapReduce，很少這樣寫。這樣只是根據傳統的資料庫的設計思路，實現了一個所謂的分組操作。但是這個分組操作最終結果是有限的。
4、MapReduce
MapReduce是整個大資料的精髓所在（實際中別用），所謂的MapReduce實際上就是分為兩步處理資料：
● Map：將資料分別取出
● Reduce：負責資料的最後處理
可是要想在MongoDB裡面實現MapReduce處理，那麼複雜度是相當高的。
範例：建立一組人員資料
db.emps.insert({"name":"趙一","age":30,"sex":"女","job":"CLERK","sal":1000});
db.emps.insert({"name":"錢二","age":22,"sex":"男","job":"ACCOUNT","sal":2000});
db.emps.insert({"name":"孫三","age":28,"sex":"女","job":"CLERK","sal":3000});
db.emps.insert({"name":"李四","age":35,"sex":"女","job":"IT","sal":4000});
db.emps.insert({"name":"週五","age":31,"sex":"男","job":"SEC","sal":5000});
db.emps.insert({"name":"吳六","age":40,"sex":"女","job":"MANAGER","sal":6000});
db.emps.insert({"name":"鄭七","age":44,"sex":"男","job":"CLERK","sal":1500});
db.emps.insert({"name":"王八","age":55,"sex":"男","job":"SAL","sal":5500});
使用MapReduce操作最終會將處理結果儲存在一個單獨的集合裡面，而最終的處理效果如下：
範例：按照職位分組，取得每個職位的姓名
第一步：編寫分組的定義：
var jobMapFun=function(){
emit(this.job,this.name);
}
第二步：編寫Reduce操作：
> var jobReduceFun=function(key,values){
... return {"job":key,"name":values};
... }
第三步：針對於MapReduce處理完成的資料實際上也可以執行一個最後處理。
var jobFinalizeFun=function(key,values){
if(key=="MANAGER"){
return {"job":key,"name":values,"info":"公司老大"};}
return {"job":key,"name":values};
}
進行操作的整合：
db.runCommand({
"mapreduce":"emps",
"map":jobMapFun,
"reduce":jobReduceFun,
"out":"t_emps_job",
"finalize":jobFinalizeFun});
{
"result" : "t_emps_job",
"timeMillis" : 28,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 1,
"output" : 6
},
"ok" : 1
}
現在執行之後，所有的處理結果都儲存在了"t_emps_job"集合裡面。
> db.t_emps_job.find();
{ "_id" : "ACCOUNT", "value" : { "job" : "ACCOUNT", "name" : "錢二" } }
{ "_id" : "CLERK", "value" : { "job" : "CLERK", "name" : { "job" : "CLERK", "name" : [ "趙一", "孫三", "鄭七" ] } } }
{ "_id" : "IT", "value" : { "job" : "IT", "name" : "李四" } }
{ "_id" : "MANAGER", "value" : { "job" : "MANAGER", "name" : "吳六", "info" : "公司老大" } }
{ "_id" : "SAL", "value" : { "job" : "SAL", "name" : "王八" } }
{ "_id" : "SEC", "value" : { "job" : "SEC", "name" : "週五" } }
範例：統計出各性別的人數，平均工資，最低工資，僱員姓名
var sexMapFun=function(){
emit(this.sex,{"ccount":1,"csal":this.sal,"cmax":this.sal,"cmin":this.sal,"cname":this.name}); //定義分組的條件以及每個集合要取出的內容
}
var sexReduceFun=function(key,values){
var total=0; //統計
var sum=0; //計算總工資
var max=values[0].cmax; //假設第一個資料是最高工資
var min=values[0].cmin; //假設第一個資料是最低工資
var names=new Array(); //定義陣列內容
for (var x in values){ //迴圈取出裡面的資料
total += values[x].ccount; //人數增加
sum += values[x].csal; //迴圈取出所有的工資，並且累加
if (max<values[x].cmax){
max=values[x].cmax; //找到最高工資
}
if (min>values[x].cmin){
min=values[x].cmin; //找到最低工資
}
names[x]=values[x].cname; //儲存姓名
}
var avg=(sum/total).toFixed(2);
return {"count":total,"avg":avg,"max":max,"min":min,"name":names};
}; //返回資料處理結果
db.runCommand({
"mapreduce":"emps",
"map":sexMapFun,
"reduce":sexReduceFun,
"out":"t_emps_sex"
})
{
"result" : "t_emps_sex",
"timeMillis" : 31,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 2
},
"ok" : 1
}
> db.t_emps_sex.find().pretty();
{
"_id" : "女",
"value" : {
"count" : 4,
"avg" : "3500.00",
"max" : 6000,
"min" : 1000,
"name" : [
"趙一",
"孫三",
"李四",
"吳六"
]
}
}
{
"_id" : "男",
"value" : {
"count" : 4,
"avg" : "3500.00",
"max" : 5500,
"min" : 1500,
"name" : [
"錢二",
"週五",
"鄭七",
"王八"
]
}
}
雖然大資料時代提供有最強悍的MapReduce支援，但是從現實的開發來講，真的不可能使用起來。
5、聚合框架
MapReduce功能強大，但是複雜度太大，很多時候需要MapReduce功能，可是又不想把程式碼寫得太複雜。所以在MongoDB 2.x版本之後開始引入了聚合框架並且提供了聚合函式：aggregate()。
5.1 $group
group主要進行分組的資料操作。
範例：實現聚合查詢的功能--求出每個職位的人員數量
> db.emps.aggregate([{"$group":{"_id":"$job",job_count:{"$sum":1}}}])
{ "_id" : "MANAGER", "job_count" : 1 }
{ "_id" : "ACCOUNT", "job_count" : 1 }
{ "_id" : "IT", "job_count" : 1 }
{ "_id" : "SAL", "job_count" : 1 }
{ "_id" : "CLERK", "job_count" : 3 }
{ "_id" : "SEC", "job_count" : 1 }
這樣的操作更加符合傳統的group by子句的操作使用。
範例：求出每個職位的總工資
> db.emps.aggregate([{"$group":{"_id":"$job",job_sal:{"$sum":"$sal"}}}])
{ "_id" : "MANAGER", "job_sal" : 6000 }
{ "_id" : "ACCOUNT", "job_sal" : 2000 }
{ "_id" : "IT", "job_sal" : 4000 }
{ "_id" : "SAL", "job_sal" : 5500 }
{ "_id" : "CLERK", "job_sal" : 5500 }
{ "_id" : "SEC", "job_sal" : 5000 }
在整個聚合框架裡面如果要引用每行的資料使用："$欄位名稱"。
範例：計算出每個職位的平均工資
> db.emps.aggregate([{"$group":{"_id":"$job","job_sal":{"$sum":"$sal"},"job_avg":{"$avg":"$sal"}}}])
{ "_id" : "MANAGER", "job_sal" : 6000, "job_avg" : 6000 }
{ "_id" : "ACCOUNT", "job_sal" : 2000, "job_avg" : 2000 }
{ "_id" : "IT", "job_sal" : 4000, "job_avg" : 4000 }
{ "_id" : "SAL", "job_sal" : 5500, "job_avg" : 5500 }
{ "_id" : "CLERK", "job_sal" : 5500, "job_avg" : 1833.3333333333333 }
{ "_id" : "SEC", "job_sal" : 5000, "job_avg" : 5000 }
範例：求出最高與最低工資
> db.emps.aggregate([{"$group":{"_id":"$job","max_sal":{"$max":"$sal"},"min_avg":{"$min":"$sal"}}}])
{ "_id" : "MANAGER", "max_sal" : 6000, "min_avg" : 6000 }
{ "_id" : "ACCOUNT", "max_sal" : 2000, "min_avg" : 2000 }
{ "_id" : "IT", "max_sal" : 4000, "min_avg" : 4000 }
{ "_id" : "SAL", "max_sal" : 5500, "min_avg" : 5500 }
{ "_id" : "CLERK", "max_sal" : 3000, "min_avg" : 1000 }
{ "_id" : "SEC", "max_sal" : 5000, "min_avg" : 5000 }
以上的幾個與SQL類似的操作計算成功實現了。
範例：計算出每個職位的工資資料（陣列顯示）
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$push":"$sal"}}}])
{ "_id" : "MANAGER", "sal_data" : [ 6000 ] }
{ "_id" : "ACCOUNT", "sal_data" : [ 2000 ] }
{ "_id" : "IT", "sal_data" : [ 4000 ] }
{ "_id" : "SAL", "sal_data" : [ 5500 ] }
{ "_id" : "CLERK", "sal_data" : [ 1000, 3000, 1500 ] }
{ "_id" : "SEC", "sal_data" : [ 5000 ] }
範例：求出每個職位的人員
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$push":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : [ "吳六" ] }
{ "_id" : "ACCOUNT", "sal_data" : [ "錢二" ] }
{ "_id" : "IT", "sal_data" : [ "李四" ] }
{ "_id" : "SAL", "sal_data" : [ "王八" ] }
{ "_id" : "CLERK", "sal_data" : [ "趙一", "孫三", "鄭七" ] }
{ "_id" : "SEC", "sal_data" : [ "週五" ] }
使用“$push”的確可以將資料變為陣列進行儲存，但是有一個問題出現了，重複的內容也會進行儲存，那麼在MongoDB裡面提供有取消重複的設定。
> db.emps.insert({"name":"吳六","age":40,"sex":"女","job":"MANAGER","sal":6000});
WriteResult({ "nInserted" : 1 })
> db.emps.insert({"name":"鄭七","age":44,"sex":"男","job":"CLERK","sal":1500});
WriteResult({ "nInserted" : 1 })
> db.emps.insert({"name":"王八","age":55,"sex":"男","job":"SAL","sal":5500});
WriteResult({ "nInserted" : 1 })
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$push":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : [ "吳六", "吳六" ] }
{ "_id" : "ACCOUNT", "sal_data" : [ "錢二" ] }
{ "_id" : "IT", "sal_data" : [ "李四" ] }
{ "_id" : "SAL", "sal_data" : [ "王八", "王八" ] }
{ "_id" : "CLERK", "sal_data" : [ "趙一", "孫三", "鄭七", "鄭七" ] }
{ "_id" : "SEC", "sal_data" : [ "週五" ] }
範例：取消重複的資料
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$addToSet":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : [ "吳六" ] }
{ "_id" : "ACCOUNT", "sal_data" : [ "錢二" ] }
{ "_id" : "IT", "sal_data" : [ "李四" ] }
{ "_id" : "SAL", "sal_data" : [ "王八" ] }
{ "_id" : "CLERK", "sal_data" : [ "鄭七", "孫三", "趙一" ] }
{ "_id" : "SEC", "sal_data" : [ "週五" ] }
預設情況下是將所有的資料都儲存進去了，但是現在只希望可以保留第一個或者是最後一個。
範例：儲存第一個內容
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$first":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : "吳六" }
{ "_id" : "ACCOUNT", "sal_data" : "錢二" }
{ "_id" : "IT", "sal_data" : "李四" }
{ "_id" : "SAL", "sal_data" : "王八" }
{ "_id" : "CLERK", "sal_data" : "趙一" }
{ "_id" : "SEC", "sal_data" : "週五" }
範例：儲存最後一個內容
> db.emps.aggregate([{"$group":{"_id":"$job","sal_data":{"$last":"$name"}}}])
{ "_id" : "MANAGER", "sal_data" : "吳六" }
{ "_id" : "ACCOUNT", "sal_data" : "錢二" }
{ "_id" : "IT", "sal_data" : "李四" }
{ "_id" : "SAL", "sal_data" : "王八" }
{ "_id" : "CLERK", "sal_data" : "鄭七" }
{ "_id" : "SEC", "sal_data" : "週五" }
雖然可以方便的實現分組處理，但是有一點需要注意，所有的分組資料是無序的，並且都是在記憶體之中完成的，所以不可能支援大資料量。
5.2 $project
可以利用$project來控制資料列的顯示規則，規則如下：
● 普通列({成員:1|true})：表示要顯示的內容
● “_id”列({"_id":0|false})：表示“_id”列是否顯示
● 條件過濾列({成員:表示式})：滿足表示式之後的資料可以進行顯示
範例：只顯示name、job列，不顯示_id列
> db.emps.aggregate([{"$project":{"_id":0,"name":1}}])
{ "name" : "趙一" }
{ "name" : "錢二" }
{ "name" : "孫三" }
{ "name" : "李四" }
{ "name" : "週五" }
{ "name" : "吳六" }
{ "name" : "鄭七" }
{ "name" : "王八" }
{ "name" : "吳六" }
{ "name" : "鄭七" }
{ "name" : "王八" }
此時只有設定進去的列才可以被顯示出來，而其他的列不能被顯示出來。實際上這就屬於資料庫的投影機制。
實際上在資料投影的過程裡面也支援四則運算：加法（"$add"）、減法（"$subtract"）、乘法（“$mulitipy”）、除法（“”$devided）、求模（"$mod"）
範例：四則運算
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"職位":"$job","sal":1}}])
{ "name" : "趙一", "sal" : 1000, "職位" : "CLERK" }
{ "name" : "錢二", "sal" : 2000, "職位" : "ACCOUNT" }
{ "name" : "孫三", "sal" : 3000, "職位" : "CLERK" }
{ "name" : "李四", "sal" : 4000, "職位" : "IT" }
{ "name" : "週五", "sal" : 5000, "職位" : "SEC" }
{ "name" : "吳六", "sal" : 6000, "職位" : "MANAGER" }
{ "name" : "鄭七", "sal" : 1500, "職位" : "CLERK" }
{ "name" : "王八", "sal" : 5500, "職位" : "SAL" }
{ "name" : "吳六", "sal" : 6000, "職位" : "MANAGER" }
{ "name" : "鄭七", "sal" : 1500, "職位" : "CLERK" }
{ "name" : "王八", "sal" : 5500, "職位" : "SAL" }
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"年薪":{"$multiply":["$sal",12]}}}])
{ "name" : "趙一", "job" : "CLERK", "年薪" : 12000 }
{ "name" : "錢二", "job" : "ACCOUNT", "年薪" : 24000 }
{ "name" : "孫三", "job" : "CLERK", "年薪" : 36000 }
{ "name" : "李四", "job" : "IT", "年薪" : 48000 }
{ "name" : "週五", "job" : "SEC", "年薪" : 60000 }
{ "name" : "吳六", "job" : "MANAGER", "年薪" : 72000 }
{ "name" : "鄭七", "job" : "CLERK", "年薪" : 18000 }
{ "name" : "王八", "job" : "SAL", "年薪" : 66000 }
{ "name" : "吳六", "job" : "MANAGER", "年薪" : 72000 }
{ "name" : "鄭七", "job" : "CLERK", "年薪" : 18000 }
{ "name" : "王八", "job" : "SAL", "年薪" : 66000 }
除了四則運算之外也支援如下的各種運算子：
● 關係運算：大小比較（"$cmp"）、等於（"$eq"）、大於（"$gt"）、大於等於（"$gte"）、小於（"$lt"）、小於等於（"$lte"）、不等於（"$ne"）、判斷NULL（"$ifNull"），這些操作返回的結果都是布林型資料。
● 邏輯運算：與（"$and"）、或（"$or"）、非（"$not"）
● 字串操作：連線（"$concat"）、擷取（"$substr"）、轉小寫（"$toLower"）、轉大小（"$toUpper"）、不區分大小寫比較（"$strcasecmp"）
範例：找出所有薪水大於等於2000的人員姓名，職位和薪水
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"工資":"$sal","是否大於2000":{"$gte":["$sal",2000]}}}])
{ "name" : "趙一", "job" : "CLERK", "工資" : 1000, "是否大於2000" : false }
{ "name" : "錢二", "job" : "ACCOUNT", "工資" : 2000, "是否大於2000" : true }
{ "name" : "孫三", "job" : "CLERK", "工資" : 3000, "是否大於2000" : true }
{ "name" : "李四", "job" : "IT", "工資" : 4000, "是否大於2000" : true }
{ "name" : "週五", "job" : "SEC", "工資" : 5000, "是否大於2000" : true }
{ "name" : "吳六", "job" : "MANAGER", "工資" : 6000, "是否大於2000" : true }
{ "name" : "鄭七", "job" : "CLERK", "工資" : 1500, "是否大於2000" : false }
{ "name" : "王八", "job" : "SAL", "工資" : 5500, "是否大於2000" : true }
{ "name" : "吳六", "job" : "MANAGER", "工資" : 6000, "是否大於2000" : true }
{ "name" : "鄭七", "job" : "CLERK", "工資" : 1500, "是否大於2000" : false }
{ "name" : "王八", "job" : "SAL", "工資" : 5500, "是否大於2000" : true }
範例：查詢職位是manager的資訊
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"$eq":["$job","MANAGER"]}}}])
{ "name" : "趙一", "job" : false, "職位" : "CLERK" }
{ "name" : "錢二", "job" : false, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : false, "職位" : "CLERK" }
{ "name" : "李四", "job" : false, "職位" : "IT" }
{ "name" : "週五", "job" : false, "職位" : "SEC" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"$eq":["$job",{"$toUpper":"manager"}]}}}])
{ "name" : "趙一", "job" : false, "職位" : "CLERK" }
{ "name" : "錢二", "job" : false, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : false, "職位" : "CLERK" }
{ "name" : "李四", "job" : false, "職位" : "IT" }
{ "name" : "週五", "job" : false, "職位" : "SEC" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
{ "name" : "吳六", "job" : true, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : false, "職位" : "CLERK" }
{ "name" : "王八", "job" : false, "職位" : "SAL" }
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"$strcasecmp":["$job","manager"]}}}])
{ "name" : "趙一", "job" : -1, "職位" : "CLERK" }
{ "name" : "錢二", "job" : -1, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : -1, "職位" : "CLERK" }
{ "name" : "李四", "job" : -1, "職位" : "IT" }
{ "name" : "週五", "job" : 1, "職位" : "SEC" }
{ "name" : "吳六", "job" : 0, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : -1, "職位" : "CLERK" }
{ "name" : "王八", "job" : 1, "職位" : "SAL" }
{ "name" : "吳六", "job" : 0, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : -1, "職位" : "CLERK" }
{ "name" : "王八", "job" : 1, "職位" : "SAL" }
範例：使用字串擷取
> db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"職位":"$job","job":{"前面三位":{"$substr":["$job",0,3]}}}}])
{ "name" : "趙一", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "錢二", "job" : { "前面三位" : "ACC" }, "職位" : "ACCOUNT" }
{ "name" : "孫三", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "李四", "job" : { "前面三位" : "IT" }, "職位" : "IT" }
{ "name" : "週五", "job" : { "前面三位" : "SEC" }, "職位" : "SEC" }
{ "name" : "吳六", "job" : { "前面三位" : "MAN" }, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "王八", "job" : { "前面三位" : "SAL" }, "職位" : "SAL" }
{ "name" : "吳六", "job" : { "前面三位" : "MAN" }, "職位" : "MANAGER" }
{ "name" : "鄭七", "job" : { "前面三位" : "CLE" }, "職位" : "CLERK" }
{ "name" : "王八", "job" : { "前面三位" : "SAL" }, "職位" : "SAL" }
利用"$project"實現的投影操作功能相當強大，所有可能出現的操作幾乎都能夠使用。
5.3、$sort
使用"$sort"可以實現排序，設定1表示升序，設定-1表示降序。
範例：實現排序
> db.emps.aggregate([{"$project":{"_id":0,"age":1,"sal":1}},{"$sort":{"age":-1,"sal":1}}])
{ "age" : 55, "sal" : 5500 }
{ "age" : 55, "sal" : 5500 }
{ "age" : 44, "sal" : 1500 }
{ "age" : 44, "sal" : 1500 }
{ "age" : 40, "sal" : 6000 }
{ "age" : 40, "sal" : 6000 }
{ "age" : 35, "sal" : 4000 }
{ "age" : 31, "sal" : 5000 }
{ "age" : 30, "sal" : 1000 }
{ "age" : 28, "sal" : 3000 }
{ "age" : 22, "sal" : 2000 }
> db.emps.aggregate([{"$match":{"sal":{"$gte":3000,"$lte":5000}}},{"$project":{"_id":0,"age":1,"sal":1}},{"$sort":{"age":-1,"sal":1}}])
{ "age" : 35, "sal" : 4000 }
{ "age" : 31, "sal" : 5000 }
{ "age" : 28, "sal" : 3000 }
> db.emps.aggregate([
... {"$match":{"sal":{"$gte":3000,"$lte":6000}}},
... {"$project":{"_id":0,"age":1,"sal":1}},
... {"$group":{"_id":"$age","count":{"$sum":1}}},
... {"$sort":{"count":-1}}])
{ "_id" : 55, "count" : 2 }
{ "_id" : 40, "count" : 2 }
{ "_id" : 28, "count" : 1 }
{ "_id" : 35, "count" : 1 }
{ "_id" : 31, "count" : 1 }
> db.emps.aggregate([
... {"$match":{"sal":{"$gte":3000,"$lte":6000}}},
... {"$project":{"_id":0,"name":1,"sal":1,"job":1}},
... {"$group":{"_id":"$job","count":{"$sum":1},"avg":{"$avg":"$sal"}}},
... {"$sort":{"count":-1}}])
{ "_id" : "SAL", "count" : 2, "avg" : 5500 }
{ "_id" : "MANAGER", "count" : 2, "avg" : 6000 }
{ "_id" : "IT", "count" : 1, "avg" : 4000 }
{ "_id" : "CLERK", "count" : 1, "avg" : 3000 }
{ "_id" : "SEC", "count" : 1, "avg" : 5000 }
5.4、分頁處理：$limit、$skip
"$limit"：資料取出個數
“$skip”：資料跨過個數
範例：使用“$limit”設定取出個數
> db.emps.aggregate([
... {"$project":{"_id":0,"name":1,"sal":1}},
... {"$limit":2}
... ])
{ "name" : "趙一", "sal" : 1000 }
{ "name" : "錢二", "sal" : 2000 }
範例：跨過3行資料
> db.emps.aggregate([
... {"$project":{"_id":0,"name":1,"sal":1}},
... {"$skip":3},
... {"$limit":2}
... ])
{ "name" : "李四", "sal" : 4000 }
{ "name" : "週五", "sal" : 5000 }
> db.emps.aggregate([
... {"$match":{"sal":{"$gte":3000,"$lte":6000}}},
... {"$project":{"_id":0,"name":1,"sal":1,"job":1}},
... {"$group":{"_id":"$job","count":{"$sum":1},"avg":{"$avg":"$sal"}}},
... {"$sort":{"count":-1}},
... {"$skip":3},
... {"$limit":2}
... ])
{ "_id" : "IT", "count" : 1, "avg" : 4000 }
{ "_id" : "CLERK", "count" : 1, "avg" : 3000 }
5.5 $unwind
在查詢資料的時候經常會返回陣列資訊，但是陣列並不方便資訊的瀏覽，所以提供有"$unwind"可以將陣列資料變為對立的字串內容。
範例：新增一些資訊
db.depts.insert({"title":"技術部","busi":["研發","生產","培訓"]});
db.depts.insert({"title":"技術部","busi":["薪酬","稅務"]});
範例：將資訊進行轉化
> db.depts.aggregate([
... {"$project":{"_id":0,"title":1,"busi":1}},
... {"$unwind":"$busi"}
... ]);
{ "title" : "技術部", "busi" : "研發" }
{ "title" : "技術部", "busi" : "生產" }
{ "title" : "技術部", "busi" : "培訓" }
{ "title" : "技術部", "busi" : "薪酬" }
{ "title" : "技術部", "busi" : "稅務" }
此時相當於將陣列的資料變為了單行的資料。
5.6 $geoNear
使用"$geoNear"可以得到附件的座標點。
範例：準備測試資料
db.shop.drop();
db.shop.insert({loc:[10,10]});
db.shop.insert({loc:[20,10]});
db.shop.insert({loc:[10,20]});
db.shop.insert({loc:[20,20]});
db.shop.insert({loc:[100,100]});
db.shop.insert({loc:[80,30]});
db.shop.insert({loc:[30,50]});
db.shop.createIndex({"loc":"2d"})
範例：設定查詢
> db.shop.aggregate([
... {"$geoNear":{"near":[30,30],"distanceField":"loc","maxDistance":20,"num":2}}
... ])
{ "_id" : ObjectId("5994ff7c0184ff511bf02bdc"), "loc" : 14.142135623730951 }
{ "_id" : ObjectId("5994ff7d0184ff511bf02bdf"), "loc" : 20 }
地理資訊的檢索必須存在索引的支援。
5.7 $out
"$out"可以將查詢結果輸出到指定的集合裡面。
範例：將投影的結果輸出到集合裡
> db.emps.aggregate([
... {"$project":{"_id":0,"name":1,"sal":1}},
... {"$out":"emps_infos"}
... ])
> db.emps_infos.find();
{ "_id" : ObjectId("599501eeca6455d4a46874e0"), "name" : "趙一", "sal" : 1000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e1"), "name" : "錢二", "sal" : 2000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e2"), "name" : "孫三", "sal" : 3000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e3"), "name" : "李四", "sal" : 4000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e4"), "name" : "週五", "sal" : 5000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e5"), "name" : "吳六", "sal" : 6000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e6"), "name" : "鄭七", "sal" : 1500 }
{ "_id" : ObjectId("599501eeca6455d4a46874e7"), "name" : "王八", "sal" : 5500 }
{ "_id" : ObjectId("599501eeca6455d4a46874e8"), "name" : "吳六", "sal" : 6000 }
{ "_id" : ObjectId("599501eeca6455d4a46874e9"), "name" : "鄭七", "sal" : 1500 }
{ "_id" : ObjectId("599501eeca6455d4a46874ea"), "name" : "王八", "sal" : 5500 }
相當於實現了資料表的複製操作。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-2144106/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mongoDB資料庫之聚合MongoDB資料庫
- MongoDB學習之聚合操作MongoDB
- mongodb聚合MongoDB
- mongodb 聚合管道MongoDB
- MongoDB - 聚合查詢MongoDB
- MongoDB的聚合筆記MongoDB筆記
- mongodb聚合操作記錄MongoDB
- 005.MongoDB索引及聚合MongoDB索引
- MongoDB系列--深入理解MongoDB聚合（Aggregation ）MongoDB
- 快速掌握mongoDB(二)——聚合管道和MapReduceMongoDB
- mongoDB中聚合函式java處理MongoDB函式Java
- MongoDB三種聚合命令用法介紹MongoDB
- JAVA - mongodb 聚合幾種查詢方式JavaMongoDB
- es筆記七之聚合操作之桶聚合和矩陣聚合筆記矩陣
- elasticsearch 聚合之 date_histogram 聚合ElasticsearchHistogram
- es筆記六之聚合操作之指標聚合筆記指標
- elasticsearch之metric聚合Elasticsearch
- MongoDB 聚合嵌入的陣列（扁平化資料+管道）MongoDB陣列
- elasticsearch bucket 之rare terms聚合Elasticsearch
- mongodb之shard分片MongoDB
- MongoDB之副本集MongoDB
- MongoDB之監控MongoDB
- MongoDB之GridFSMongoDB
- MongoDB之固定集合MongoDB
- MongoDB之遊標MongoDB
- MongoDB之基本操作MongoDB
- elasticsearch 之 histogram 直方圖聚合ElasticsearchHistogram直方圖
- Flink SQL之Over 聚合操作SQL
- 小丸子學MongoDB系列之——安裝MongoDBMongoDB
- 小丸子學MongoDB系列之——部署MongoDB副本集MongoDB
- MongoDB之索引（簡介）MongoDB索引
- jmeter學習指南之聚合報告JMeter
- Elasticsearch聚合學習之四：結果排序Elasticsearch排序
- MongoDB原始碼分析之MongosXFMongoDB原始碼
- MongoDB之使用者管理MongoDB
- MongoDB之索引（地理資訊索引）MongoDB索引
- MongoDB之索引（全文索引）MongoDB索引
- MongoDB之索引（過期索引）MongoDB索引