mongo学习（二）——常用查询技巧

2017-07-19 本文已影响0人小明滚出去_

翻页查询

翻页查询需要使用两个函数，读取指定行数，实现翻页功能
db.col.find().skip(number)跳过指定行数
db.col.find().limit(number)读取指定条数数据

> 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中传入每页条数
注意：skip、limit的调用顺序不影响查询结果，执行的时候按照sort - > skip -> limit顺序执行

排序

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> )

数据准备

> 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" : [ ]
})

聚合表达式

求和 $sum
按学生分组求出学生的总分

> 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
求出每个学生的平均分

> db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}}])

取最低分 $min
求出学生科目最低分

> db.score.aggregate([{$group:{_id:"$name",min:{$min:"$score"}}}])

取最高分 $max
求出学生科目最高分

> db.score.aggregate([{$group:{_id:"$name",max:{$max:"$score"}}}])

在结果文档中插入一个数组 $push
学生名和对应的科目信息

> 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
}
]
}
<省略部分结果>

    - 使用$push可以任意拼装自己想要的结果集
    - $push时指定"$$ROOT"将会把当前document加入数组
    ```
    db.score.aggregate([
    {$group:{
        _id:"$name",
        doms:{$push:"$$ROOT"}
    }}
])
    ```

  - 往结果中插入一个数组 $addToSet
    - 功能为$push一样，唯一的不同是当数组原始相同时，$push会加入重复元素，而$addToSet不会
  学生的成绩数组

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 修改输入文档的结构
类似于SQL中的as和子查询，可以定义结果集

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 }

两个命令执行结果一致
  - $match 用于过滤数据
  放在group前相当于where使用,放在group后面相当于having使用
  例：统计各科大于90分的人数

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 }

  例：统计平均分大于90的学生

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 取指定数据

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 拆分文档中的数组形成多个文档
  例：将各科目前三名拆分成多条

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 排序
  例： 按学生平均分排序

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
  按地理位置获取文档

mongo学习（二）——常用查询技巧

翻页查询

排序

聚合

猜你喜欢

热点阅读