文章目录
Mongodb使用学习笔记(二)之查询篇
- 参考:菜鸟教程
1. MongoDB命令学习
(1) MongoDB 查询文档
1.1 全部查询
- db.collection.find() 方法,查询全部文档,查询结果不会进行json格式化。
- 不改变参数和全部文档内容情况下,每次查询回来的结果顺序是一样的。
- db.collection.find().pretty()方法,查询全部文档,查询结果会进行json格式化。
- db.collection.findOne(), 返回db.collection.find() 查询数组的第一个文档。
1.2 单条件查询
- 单条件精确比较匹配:filterKey是文档中的属性,filterValue是要过滤的条件值(精确匹配)。
db.collection.find({"filterKey":"filterValue"})
- 单条件范围比较匹配:filterKey是文档中的属性,filterValue是要比较的条件值)。
- 注意事项:filterValue的类型,如果是数字,那就不用加引号,比较的是数字大小。如果是加了引号,就是字符串,比较的是字典序。
- 比较符号:
- 小于: lt
- 小于等于: lte
- 大于:gt
- 大于等于:gte
- 不等于:ne
db.collection.find( {<filterKey>:{$lt:<filterValue>}})
- 操作记录:
> show dbs; admin 0.000GB cmdb 0.000GB config 0.000GB local 0.000GB > use cmdb switched to db cmdb > show db.collections; uncaught exception: Error: don't know how to show [db.collections] : shellHelper.show@src/mongo/shell/utils.js:1211:11 shellHelper@src/mongo/shell/utils.js:838:15 @(shellhelp2):1:1 > show collections; hardware > db.ha db.hardware db.hasOwnProperty > db.hardware.insert({"name":"一个大丑逼","age":"20","hobbies":["eat"]}) WriteResult({ "nInserted" : 1 }) > db.hardware.find(); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } > db.hardware.insert({"name":"一个大帅逼","age":"18","hobbies":["piano","cooking","reading","game"]}); WriteResult({ "nInserted" : 1 }) > db.hardware.find(); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } > db.hardware.find().pretty(); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } > db.hardware.find("name", "一个大丑逼"); Error: error: { "ok" : 0, "errmsg" : "Executor error during find command :: caused by :: ReferenceError: name is not defined :\n@:1:15\n", "code" : 139, "codeName" : "JSInterpreterFailure" } > db.hardware.find("name":"一个大丑逼"); uncaught exception: SyntaxError: missing ) after argument list : @(shell):1:23 > db.hardware.find({"name":"一个大丑逼"}); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } > db.hardware.find({"age":{$lt:20}}); > db.hardware.find({"age":{$1t:20}}); Error: error: { "ok" : 0, "errmsg" : "unknown operator: $1t", "code" : 2, "codeName" : "BadValue" } > db.hardware.find({"age":{$lt:25}}); > db.hardware.find(); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } > db.hardware.find({"age":{$lt:25}}); > db.hardware.find({"age":{$lt:"25"}}); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } > db.hardware.find({"age":{$lt:"WSSB"}}); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } > db.hardware.find({"age":{$lt:"19"}}); { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } > db.hardware.find({"age":{$lt:"190"}}); { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } >
1.3 多条件查询
1.3.1 AND拼接多条件查询
- 多个条件用逗号隔开就行了,样式如:db.collection.find({key1:value1, key2:value2})
- 操作记录
> use cmdb; switched to db cmdb > db.hardware.insert({"name":"hardware1","serial_number":"0000001","type":"route r","city":"gz"}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardware2","serial_number":"0000002","type":"route r","city":"gz"}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardware3","serial_number":"0000003","type":"route r","city":"gz"}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardware4","serial_number":"0000004","type":"switc h","city":"gz"}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardware5","serial_number":"0000005","type":"switc h","city":"hn"}) WriteResult({ "nInserted" : 1 }) > db.hardware.find(); { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } { "_id" : ObjectId("61a4a27bdf090cc7b1fddfc0"), "name" : "hardware5", "serial_number" : "0000005", "type" : "switch", "city" : "hn" } > db.hardware.find({"name":"hardware1","city":"gz"}).pretty(); { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } > db.hardware.find({"city":"gz"}).pretty(); { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } >
1.3.1 OR拼接多条件查询
- 使用了关键字 $or。中括号里面是并列条件的列表
- 语法格式:
>db.collection.find( { $or: [ {key1: value1}, {key2:value2} ] } )
1.3.1 AND和or混用拼接多条件查询
- 例一:查询 “city"为"gz"或者"hn”,并且"name"为"hardware1"或者"hardware2"的数据
- 伪代码: (data.get(“city”).equals(“gz”) || data.get(“city”).equals(“hn”) ) && (data.get(“name”).equals(“hardware1”) || data.get(“name”).equals(“hardware2”))-
> db.hardware.find({$and:[{$or:[{"name":"hardware1"},{"name":"hardware2"}]},{$or:[{"city":"gz"},{"city":"hn"}]}]}).pretty(); { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } >
- 例二:查询 “name"为"hardware1"且"type"为"router” 或者 “city"为"gz"且"type"为"switch”
- 伪代码:(data.get(“name”).equals(“hardware1”) && data.get(“type”).equals(“router”) ) || (data.get(“city”).equals(“gz”) && data.get(“type”).equals(“switch”))
> db.hardware.find({$or:[{$and:[{"name":"hardware1"},{"type":"router"}]},{$and:[{"city":"gz"},{"type":" switch"}]}]}).pretty(); { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } >
- 注意事项:
- 1.$and和$or后面跟的都是中括号[]
- 2.子条件条件要用{}括起来(相当于where中的小括号())
1.4 模糊查询
-
模糊查询可以使用正则表达式
-
MongoDB 使用 $regex 操作符来设置匹配字符串的正则表达式。
- {$regex:“value”}表示对value进行模糊匹配。注意,{$regex:“value”}还可以对数组中的元素进行模糊匹配。
db.collection.find({ key:{$regex:"value"} })
-
MongoDB使用PCRE (Perl Compatible Regular Expression) 作为正则表达式语言。
-
两条正斜杠:正斜杠之间包含着要匹配的规则,本身实际上没啥意义。
-
下面的方法表示对value进行迷糊匹配,和db.collection.find({ key:{$regex:“value”} })等价
db.collection.find({ key: /value/ })
-
value可以替换为相应的正则表达式,表示对符合该正则表达式的值进行匹配。
-
注意事项:
- 1.正则表达式是对字符串进行匹配,对数字不行
- 2.key带不带双引号不影响结果,但是两种形式中的value的双引号要注意 ,会影响匹配的结果。
-
操作记录:
- 3./^d/表示字符串以d开头,所以有"def"和"defg"符合。/^g/ 表示g开头,没有符合的。
- 4.Mongodb正则表达式还可以忽略大小写进行匹配:db.collection.find({ key:{$regex:“value”,$options:"$i"} })
- 正则表达式中使用变量。一定要使用eval将组合的字符串进行转换,不能直接将字符串拼接后传入给表达式。否则没有报错信息,只是结果为空!
> db.hardware.find(); { "_id" : ObjectId("61a8674e83e24c27ab4ce164"), "name" : "hardware6asdg", "serial_number" : "abcdef", "type" : "switch", "city" : "hn" } { "_id" : ObjectId("61a8677d83e24c27ab4ce165"), "name" : "hardware7scd", "serial_number" : "ab123bc", "type" : "switch", "city" : "bj" } { "_id" : ObjectId("61a867fd83e24c27ab4ce166"), "name" : "hardwareqwer", "serial_number" : "aswd5678", "type" : "hostmachine", "city" : "js" } { "_id" : ObjectId("61a8698ddec81e3abc4b7d13"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ 12, 34, 567 ]} > db.hardware.find({name:{$regex:"hardware"}}); { "_id" : ObjectId("61a8674e83e24c27ab4ce164"), "name" : "hardware6asdg", "serial_number" : "abcdef", "type" : "switch", "city" : "hn" } { "_id" : ObjectId("61a8677d83e24c27ab4ce165"), "name" : "hardware7scd", "serial_number" : "ab123bc", "type" : "switch", "city" : "bj" } { "_id" : ObjectId("61a867fd83e24c27ab4ce166"), "name" : "hardwareqwer", "serial_number" : "aswd5678", "type" : "hostmachine", "city" : "js" } { "_id" : ObjectId("61a8698ddec81e3abc4b7d13"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ 12, 34, 567]} > db.hardware.find({name:{$regex:"hardware6"}}); { "_id" : ObjectId("61a8674e83e24c27ab4ce164"), "name" : "hardware6asdg", "serial_number" : "abcdef", "type" : "switch", "city" : "hn" } > db.hardware.find({name:/hardware6/}); { "_id" : ObjectId("61a8674e83e24c27ab4ce164"), "name" : "hardware6asdg", "serial_number" : "abcdef", "type" : "switch", "city" : "hn" } > db.hardware.find({"name":/hardware6/}); { "_id" : ObjectId("61a8674e83e24c27ab4ce164"), "name" : "hardware6asdg", "serial_number" : "abcdef", "type" : "switch", "city" : "hn" } > db.hardware.find({"name":/"hardware6"/}); > db.hardware.find({"name":{$regex:"hardware6"}}); { "_id" : ObjectId("61a8674e83e24c27ab4ce164"), "name" : "hardware6asdg", "serial_number" : "abcdef", "type" : "switch", "city" : "hn" } > db.hardware.find({"name":{$regex:hardware6}}); uncaught exception: ReferenceError: hardware6 is not defined : @(shell):1:27 > db.hardware.find({"port":{$regex:"567"}}); > db.hardware.find({"port":{$regex:567}}); Error: error: { "ok" : 0, "errmsg" : "$regex has to be a string", "code" : 2, "codeName" : "BadValue" } > db.hardware.insert({"name":"hardwarecvbn","serial_number":"5201314","type":"switch","city":"hn","port":["abc","def"]}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardwarecvbn","serial_number":"5201314","type":"switch","city":"hn","port":["ab","defg"]}) WriteResult({ "nInserted" : 1 }) > db.hardware.find({"port":{$regex:"abc"}}); { "_id" : ObjectId("61a86bf0dec81e3abc4b7d14"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } > db.hardware.find({"port":{$regex:"bc"}}); { "_id" : ObjectId("61a86bf0dec81e3abc4b7d14"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } > db.hardware.find({"port":{$regex:"ab"}}); { "_id" : ObjectId("61a86bf0dec81e3abc4b7d14"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } { "_id" : ObjectId("61a86c01dec81e3abc4b7d15"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "ab", "defg" ] } > db.hardware.find({"port":{$regex:"g"}}); { "_id" : ObjectId("61a86c01dec81e3abc4b7d15"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "ab", "defg" ] } > db.hardware.find({"port":/^d/}); { "_id" : ObjectId("61a86bf0dec81e3abc4b7d14"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } { "_id" : ObjectId("61a86c01dec81e3abc4b7d15"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "ab", "defg" ] } > db.hardware.find({"port":/^g/}); >
1.5 排序查询
- 使用sort()进行排序。两个参数,第一个指明排序的字段,第二个参数为1或者-1。1是升序,-1是降序。
- 语法:db.collection.find().sort({key:-1})
- 注意事项:
- 1.正序时,“不包含排序字段的数据”要排在“包含排序字段的数据”前面
- 2.“不包含排序字段的数据”在sort()之后也会排序,而且是通过"_id"来排序
- 3.如果要对多个字段进行排序:格式是:db.collection.find().sort({key1:1,key2:1})
- 4.多个字段进行排序时,优先排序前面的key,以前面key的排序结果为主。(看下列serial_number和index的排序)
> db.hardware.find() { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } { "_id" : ObjectId("61a4a27bdf090cc7b1fddfc0"), "name" : "hardware5", "serial_number" : "0000005", "type" : "switch", "city" : "hn" } > db.hardware.find().sort({"age":1}) { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } { "_id" : ObjectId("61a4a27bdf090cc7b1fddfc0"), "name" : "hardware5", "serial_number" : "0000005", "type" : "switch", "city" : "hn" } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } > db.hardware.insert({"name":"hardwarecvbn","serial_number":"5201314","type":"switch","city":"hn","port":["abc","def"]}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardwarecvbn","serial_number":"5200000","type":"switch","city":"hn","port":["abc","def"],"index":1}) WriteResult({ "nInserted" : 1 }) > db.hardware.insert({"name":"hardwarecvbn","serial_number":"5200001","type":"switch","city":"hn","port":["abc","def"],"index":0}) WriteResult({ "nInserted" : 1 }) > db.hardware.find().sort({"serial_number":1}) { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } { "_id" : ObjectId("61a4a27bdf090cc7b1fddfc0"), "name" : "hardware5", "serial_number" : "0000005", "type" : "switch", "city" : "hn" } { "_id" : ObjectId("61a88a4ce890d78678783ccc"), "name" : "hardwarecvbn", "serial_number" : "5200000", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ], "index" : 1 } { "_id" : ObjectId("61a88a5ae890d78678783ccd"), "name" : "hardwarecvbn", "serial_number" : "5200001", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ], "index" : 0 } { "_id" : ObjectId("61a88a27e890d78678783ccb"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } > db.hardware.find().sort({"serial_number":1,"index":1}) { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } { "_id" : ObjectId("61a4a27bdf090cc7b1fddfc0"), "name" : "hardware5", "serial_number" : "0000005", "type" : "switch", "city" : "hn" } { "_id" : ObjectId("61a88a4ce890d78678783ccc"), "name" : "hardwarecvbn", "serial_number" : "5200000", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ], "index" : 1 } { "_id" : ObjectId("61a88a5ae890d78678783ccd"), "name" : "hardwarecvbn", "serial_number" : "5200001", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ], "index" : 0 } { "_id" : ObjectId("61a88a27e890d78678783ccb"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } > db.hardware.find().sort({"index":1,"serial_number":1}) { "_id" : ObjectId("619f2b73cec1a85ec6147ad3"), "name" : "一个大丑逼", "age" : "20", "hobbies" : [ "eat" ] } { "_id" : ObjectId("619f2c3ccec1a85ec6147ad4"), "name" : "一个大帅逼", "age" : "18", "hobbies" : [ "piano", "cooking", "reading", "game" ] } { "_id" : ObjectId("61a4a21adf090cc7b1fddfbc"), "name" : "hardware1", "serial_number" : "0000001", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a243df090cc7b1fddfbd"), "name" : "hardware2", "serial_number" : "0000002", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a24ddf090cc7b1fddfbe"), "name" : "hardware3", "serial_number" : "0000003", "type" : "router", "city" : "gz" } { "_id" : ObjectId("61a4a267df090cc7b1fddfbf"), "name" : "hardware4", "serial_number" : "0000004", "type" : "switch", "city" : "gz" } { "_id" : ObjectId("61a4a27bdf090cc7b1fddfc0"), "name" : "hardware5", "serial_number" : "0000005", "type" : "switch", "city" : "hn" } { "_id" : ObjectId("61a88a27e890d78678783ccb"), "name" : "hardwarecvbn", "serial_number" : "5201314", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ] } { "_id" : ObjectId("61a88a5ae890d78678783ccd"), "name" : "hardwarecvbn", "serial_number" : "5200001", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ], "index" : 0 } { "_id" : ObjectId("61a88a4ce890d78678783ccc"), "name" : "hardwarecvbn", "serial_number" : "5200000", "type" : "switch", "city" : "hn", "port" : [ "abc", "def" ], "index" : 1 } >
1.6 聚合查询
- MongoDB 聚合
- 使用aggregate()方法
- 格式:
- value1是聚合的分组参数,可以是常量也可以是变量,如果是常量那么会把全部的文档数据分在常量这个组,如果是文档中的字段的话则会按照这个字段分组。
- name1是自定义的名称,聚合结果的列名。
- $symbol1是聚合表达式,主要有:$sum, $avg,$min等,具体可以查看MongoDB 聚合
- field1是要聚合的参数,可以是常量也可以是变量。如果是常量,那么聚合结果就是这个常量。如果是文档中的字段,那么会对这个字段进行聚合。
db.collection.aggregate([{ $group:{ "_id":value1,name1:{$symbol1:$field1} } }])
- 注意事项:
- 1.聚合函数的参数由中括号[]括起来的
- 2.每一个聚合的条件是在一个 {$group:{}}结构里面,$group是固定的
- 3.分组的参数的key一定要是"_id",不然会报错:“The field ‘xxx’ must be an accumulator object”
- 4.想要对全部文档做聚合,可以对常量分组
- 操作记录:
> db.people.aggregate([{$group:{"id_":"$age",sum_:{$sum:1}}}]) uncaught exception: Error: command failed: { "ok" : 0, "errmsg" : "The field 'id_' must be an accumulator object", "code" : 40234, "codeName" : "Location40234" } with original command request: { "aggregate" : "people", "pipeline" : [ { "$group" : { "id_" : "$age", "sum_" : { "$sum" : 1 } } } ], "cursor" : { }, "lsid" : { "id" : UUID("20ef3a49-89bd-4377-821d-300f9ca9be6b") } } on connection: connection to 127.0.0.1:27017 : aggregate failed : _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:18:14 _assertCommandWorked@src/mongo/shell/assert.js:737:17 assert.commandWorked@src/mongo/shell/assert.js:829:16 DB.prototype._runAggregate@src/mongo/shell/db.js:276:5 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1058:12 @(shell):1:1 > db.people.aggregate([{$group:{"_id":"$age",sum_:{$sum:1}}}]) { "_id" : 22, "sum_" : 1 } { "_id" : 26, "sum_" : 1 } { "_id" : 23, "sum_" : 1 } > db.people.aggregate([{$group:{"_id":"$age",sum_:{$sum:"$salary"}}}]) { "_id" : 23, "sum_" : 7000 } { "_id" : 26, "sum_" : 10000 } { "_id" : 22, "sum_" : 6000 } > db.people.insert({"id":"004","age":26,"salary":12000}) WriteResult({ "nInserted" : 1 }) > db.people.aggregate([{$group:{"_id":"$age",sum_:{$avg:"$salary"}}}]) { "_id" : 22, "sum_" : 6000 } { "_id" : 26, "sum_" : 11000 } { "_id" : 23, "sum_" : 7000 } > db.people.aggregate([{$group:{"_id":"$age",sum_:{$min:"$salary"}}}]) { "_id" : 26, "sum_" : 10000 } { "_id" : 22, "sum_" : 6000 } { "_id" : 23, "sum_" : 7000 } > db.people.aggregate([{$group:{"_id":1,sum_:{$min:"$salary"}}}]) { "_id" : 1, "sum_" : 6000 } > db.people.aggregate([{$group:{"_id":1,sum_:{$max:"$salary"}}}]) { "_id" : 1, "sum_" : 12000 } > db.people.aggregate([{$group:{"_id":"$age",sum_:{$min:1}}}]) { "_id" : 26, "sum_" : 1 } { "_id" : 22, "sum_" : 1 } { "_id" : 23, "sum_" : 1 } > db.people.find() { "_id" : ObjectId("61a896540a49d92010a7f90c"), "id" : "001", "age" : 22, "salary" : 6000 } { "_id" : ObjectId("61a896640a49d92010a7f90d"), "id" : "002", "age" : 23, "salary" : 7000 } { "_id" : ObjectId("61a896740a49d92010a7f90e"), "id" : "003", "age" : 26, "salary" : 10000 } { "_id" : ObjectId("61a974dc09bc2e834dc429f3"), "id" : "004", "age" : 26, "salary" : 12000 } > db.people.aggregate([{$group:{"_id":"我是丑逼",sum_:{$min:"$salary"}}}]) { "_id" : "我是丑逼", "sum_" : 6000 } > db.people.aggregate([{$group:{"_id":"我是帅逼",sum_:{$max:"$salary"}}}]) { "_id" : "我是帅逼", "sum_" : 12000 } > db.people.aggregate([{$group:{"_id":"我是帅逼",sum_:{$first:"$salary"}}}]) { "_id" : "我是帅逼", "sum_" : 6000 }