mongo學(xué)習(xí)（二）——常用查詢技巧

翻頁查詢

  • 翻頁查詢需要使用兩個函數(shù)，讀取指定行數(shù)，實(shí)現(xiàn)翻頁功能
  • db.col.find().skip(number)跳過指定行數(shù)
  • db.col.find().limit(number)讀取指定條數(shù)數(shù)據(jù)
> db.c_cons.find()
{ "_id" : 1574824688, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463215, "status" : 0 }
{ "_id" : 1574824690, "name" : "鄒老濕", "addr" : "丫蛋村", "mpId" : 10558463125, "status" : 0 }
{ "_id" : 1574824698, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }
> db.c_cons.find().skip(2).limit(2)
{ "_id" : 1574824698, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }

即skip中傳入起始行，limit中傳入每頁條數(shù)
注意：skip、limit的調(diào)用順序不影響查詢結(jié)果，執(zhí)行的時候按照sort - > skip -> limit順序執(zhí)行

排序

db.COLLECTION_NAME.find().sort({KEY:1})

  • 按指定字段排序 1升序 -1降序
> db.c_cons.find().sort({"mpId":1})
{ "_id" : 1574824690, "name" : "鄒老濕", "addr" : "丫蛋村", "mpId" : 10558463125, "status" : 0 }
{ "_id" : 1574824698, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824688, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463215, "status" : 0 }
> db.c_cons.find().sort({"mpId":-1})
{ "_id" : 1574824688, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463215, "status" : 0 }
{ "_id" : 1574824698, "name" : "李大爺", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824690, "name" : "鄒老濕", "addr" : "丫蛋村", "mpId" : 10558463125, "status" : 0 }

聚合

db.COLLECTION_NAME..aggregate( [pipeline], <optional params> )

  • 數(shù)據(jù)準(zhǔn)備
> var score=[
    {_id:1,name:"甲子","subjectId":1,score:88},
    {_id:2,name:"甲子","subjectId":2,score:81},
    {_id:3,name:"甲子","subjectId":3,score:98},
    {_id:4,name:"乙丑","subjectId":1,score:78},
    {_id:5,name:"乙丑","subjectId":2,score:91},
    {_id:6,name:"乙丑","subjectId":3,score:95},
    {_id:7,name:"丙寅","subjectId":1,score:85},
    {_id:8,name:"丙寅","subjectId":2,score:71},
    {_id:9,name:"丙寅","subjectId":3,score:66},
    {_id:10,name:"丁卯","subjectId":1,score:89},
    {_id:11,name:"丁卯","subjectId":2,score:78},
    {_id:12,name:"丁卯","subjectId":3,score:77},
    {_id:13,name:"戊辰","subjectId":1,score:85},
    {_id:14,name:"戊辰","subjectId":2,score:98},
    {_id:15,name:"戊辰","subjectId":3,score:98},
    {_id:16,name:"己巳","subjectId":1,score:88},
    {_id:17,name:"己巳","subjectId":2,score:88},
    {_id:18,name:"己巳","subjectId":3,score:78},
    {_id:19,name:"庚午","subjectId":1,score:93},
    {_id:20,name:"庚午","subjectId":2,score:91},
    {_id:21,name:"庚午","subjectId":3,score:88},
    {_id:22,name:"辛未","subjectId":1,score:97},
    {_id:23,name:"辛未","subjectId":2,score:96},
    {_id:24,name:"辛未","subjectId":3,score:78},
    {_id:25,name:"壬申","subjectId":1,score:88},
    {_id:26,name:"壬申","subjectId":2,score:82},
    {_id:27,name:"壬申","subjectId":3,score:94},
    {_id:28,name:"癸酉","subjectId":1,score:88},
    {_id:29,name:"癸酉","subjectId":2,score:91},
    {_id:30,name:"癸酉","subjectId":3,score:86}
]
> db.score.insert(score)
BulkWriteResult({
        "writeErrors" : [ ],
        "writeConcernErrors" : [ ],
        "nInserted" : 30,
        "nUpserted" : 0,
        "nMatched" : 0,
        "nModified" : 0,
        "nRemoved" : 0,
        "upserted" : [ ]
})
  • 聚合表達(dá)式
    • 求和 $sum
      按學(xué)生分組求出學(xué)生的總分
    > db.score.aggregate([{$group:{
      _id:"$name",
      total:{$sum:"$score"}
     }}])
{ "_id" : "壬申", "total" : 264 }
{ "_id" : "癸酉", "total" : 265 }
{ "_id" : "辛未", "total" : 271 }
{ "_id" : "己巳", "total" : 254 }
{ "_id" : "戊辰", "total" : 281 }
{ "_id" : "乙丑", "total" : 264 }
{ "_id" : "丙寅", "total" : 222 }
{ "_id" : "甲子", "total" : 267 }
{ "_id" : "庚午", "total" : 272 }
{ "_id" : "丁卯", "total" : 244 }
    • 求平均值 $avg
      求出每個學(xué)生的平均分
    > db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}}])
    • 取最低分 $min
      求出學(xué)生科目最低分
    > db.score.aggregate([{$group:{_id:"$name",min:{$min:"$score"}}}])
    • 取最高分 $max
      求出學(xué)生科目最高分
    > db.score.aggregate([{$group:{_id:"$name",max:{$max:"$score"}}}])
    • 在結(jié)果文檔中插入一個數(shù)組 $push
      學(xué)生名和對應(yīng)的科目信息
    > db.score.aggregate([{$group:{_id:"$name",scores:{$push: {score:"$score",subjectId:"$subjectId"}}}}]).pretty()

{
"_id" : "壬申",
"scores" : [
{
"score" : 88,
"subjectId" : 1
},
{
"score" : 82,
"subjectId" : 2
},
{
"score" : 94,
"subjectId" : 3
}
]
}
{
"_id" : "癸酉",
"scores" : [
{
"score" : 88,
"subjectId" : 1
},
{
"score" : 91,
"subjectId" : 2
},
{
"score" : 86,
"subjectId" : 3
}
]
}
<省略部分結(jié)果>

    - 使用$push可以任意拼裝自己想要的結(jié)果集
    - $push時指定"$$ROOT"將會把當(dāng)前document加入數(shù)組
    ```
    db.score.aggregate([
    {$group:{
        _id:"$name",
        doms:{$push:"$$ROOT"}
    }}
])
    ```

  - 往結(jié)果中插入一個數(shù)組 $addToSet
    - 功能為$push一樣，唯一的不同是當(dāng)數(shù)組原始相同時，$push會加入重復(fù)元素，而$addToSet不會
  學(xué)生的成績數(shù)組

db.score.aggregate([{$group:{_id:"$name",scores:{$push: "$score"}}}])
{ "_id" : "壬申", "scores" : [ 88, 88, 94 ] }
{ "_id" : "癸酉", "scores" : [ 88, 91, 86 ] }
{ "_id" : "辛未", "scores" : [ 97, 96, 78 ] }
{ "_id" : "己巳", "scores" : [ 88, 88, 78 ] }
{ "_id" : "戊辰", "scores" : [ 85, 98, 98 ] }
db.score.aggregate([{$group:{_id:"$name",scores:{$addToSet: "$score"}}}])
{ "_id" : "壬申", "scores" : [ 94, 88 ] }
{ "_id" : "癸酉", "scores" : [ 91, 86, 88 ] }
{ "_id" : "辛未", "scores" : [ 78, 96, 97 ] }
{ "_id" : "己巳", "scores" : [ 78, 88 ] }
{ "_id" : "戊辰", "scores" : [ 98, 85 ] }

- 獲取第一個document $first

db.score.aggregate([{$group:{_id:"$name",firstSubject:{$first: "$subjectId"}}}])

  - 獲取最后一個document $last

db.score.aggregate([{$group:{_id:"$name",lastSubject:{$last: "$subjectId"}}}])

- pipeline 管道
- $project 修改輸入文檔的結(jié)構(gòu)
類似于SQL中的as和子查詢，可以定義結(jié)果集

db.score.aggregate({$project:{_id:0}})
{ "name" : "甲子", "subjectId" : 1, "score" : 88 }
{ "name" : "甲子", "subjectId" : 2, "score" : 81 }
{ "name" : "甲子", "subjectId" : 3, "score" : 98 }
db.score.find({},{_id:0})
{ "name" : "甲子", "subjectId" : 1, "score" : 88 }
{ "name" : "甲子", "subjectId" : 2, "score" : 81 }
{ "name" : "甲子", "subjectId" : 3, "score" : 98 }

兩個命令執(zhí)行結(jié)果一致
  - $match 用于過濾數(shù)據(jù)
  放在group前相當(dāng)于where使用,放在group后面相當(dāng)于having使用
  例：統(tǒng)計各科大于90分的人數(shù)

db.score.aggregate([
... {$match:{
... score:{$gt:90}
... }},
... {$group:{
... _id:"$subjectId",
... count:{$sum:1}
... }}
... ])
{ "_id" : 1, "count" : 2 }
{ "_id" : 2, "count" : 5 }
{ "_id" : 3, "count" : 4 }

  例：統(tǒng)計平均分大于90的學(xué)生

db.score.aggregate([
... {$group:{

... _id:"$name",
... avg:{$avg:"$score"}
... }},
... {$match:{
... avg:{$gt:90}
... }}
... ])
{ "_id" : "辛未", "avg" : 90.33333333333333 }
{ "_id" : "戊辰", "avg" : 93.66666666666667 }
{ "_id" : "庚午", "avg" : 90.66666666666667 }

- $limit $skip 取指定數(shù)據(jù)

db.score.aggregate([{$skip:6},{$group:{_id:"$name",avg:{$avg:"$score"}}}])
{ "_id" : "壬申", "avg" : 90 }
{ "_id" : "癸酉", "avg" : 88.33333333333333 }
{ "_id" : "辛未", "avg" : 90.33333333333333 }
{ "_id" : "戊辰", "avg" : 93.66666666666667 }
{ "_id" : "己巳", "avg" : 84.66666666666667 }
{ "_id" : "庚午", "avg" : 90.66666666666667 }
{ "_id" : "丁卯", "avg" : 81.33333333333333 }
{ "_id" : "丙寅", "avg" : 74 }
db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}},{$skip:5}])
{ "_id" : "乙丑", "avg" : 88 }
{ "_id" : "丙寅", "avg" : 74 }
{ "_id" : "甲子", "avg" : 89 }
{ "_id" : "庚午", "avg" : 90.66666666666667 }
{ "_id" : "丁卯", "avg" : 81.33333333333333 }
db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}},{$skip:5},{$limit:2}])
{ "_id" : "乙丑", "avg" : 88 }
{ "_id" : "丙寅", "avg" : 74 }

  - $unwind 拆分文檔中的數(shù)組形成多個文檔
  例：將各科目前三名拆分成多條

db.score.aggregate([
... {$sort:{
... score:-1
... }},
... {$group:{
... _id:"$subjectId",
... maxScores:{$push:{student:"$name",score:"$score"}}
... }},
... {$project:{
... maxScores : {
... $slice : ["$maxScores",0,3]
... }
... }},
... {$unwind:"$maxScores"}
... ])
{ "_id" : 1, "maxScores" : { "student" : "辛未", "score" : 97 } }
{ "_id" : 1, "maxScores" : { "student" : "庚午", "score" : 93 } }
{ "_id" : 1, "maxScores" : { "student" : "丁卯", "score" : 89 } }
{ "_id" : 2, "maxScores" : { "student" : "戊辰", "score" : 98 } }
{ "_id" : 2, "maxScores" : { "student" : "辛未", "score" : 96 } }
{ "_id" : 2, "maxScores" : { "student" : "乙丑", "score" : 91 } }
{ "_id" : 3, "maxScores" : { "student" : "甲子", "score" : 98 } }
{ "_id" : 3, "maxScores" : { "student" : "戊辰", "score" : 98 } }
{ "_id" : 3, "maxScores" : { "student" : "乙丑", "score" : 95 } }

  - $month $dayOfMonth $year
  取日期的年、月、日
  - $sort 排序
  例： 按學(xué)生平均分排序

db.score.aggregate([
... {$group:{
... _id:"$name",
... avg:{$avg:"$score"}
... }},
... {$sort:{
... avg:-1
... }}
... ])
{ "_id" : "戊辰", "avg" : 93.66666666666667 }
{ "_id" : "庚午", "avg" : 90.66666666666667 }
{ "_id" : "辛未", "avg" : 90.33333333333333 }
{ "_id" : "壬申", "avg" : 90 }
{ "_id" : "甲子", "avg" : 89 }
{ "_id" : "癸酉", "avg" : 88.33333333333333 }
{ "_id" : "乙丑", "avg" : 88 }
{ "_id" : "己巳", "avg" : 84.66666666666667 }
{ "_id" : "丁卯", "avg" : 81.33333333333333 }
{ "_id" : "丙寅", "avg" : 74 }

  - geoNear
  按地理位置獲取文檔
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容