MongoDB系列4——查询操作符

查询和计划操作符

一、评价查询操作符

方法名	描述
$mod	取模计算
$regex	模糊查询，查询该字段包含某个字符串
$text	对有text索引的字段进行模糊查询
$where	使用js代码查询数据

1、使用$mod进行模运算查询

c1的数据集如下：

> db.c1.find()
{ "_id" : ObjectId("5f97bb1a0436fe0f6dbb0526"), "age" : 1, "length" : 30 }
{ "_id" : ObjectId("5f97bb200436fe0f6dbb0527"), "age" : 7, "length" : 30 }
{ "_id" : ObjectId("5f97bb260436fe0f6dbb0528"), "age" : 8, "length" : 30 }
{ "_id" : ObjectId("5f97bb860436fe0f6dbb052a"), "age" : 13, "length" : 30 }

取年龄模6结果为1的数据

> db.c1.find({age:{$mod:[6,1]}})
{ "_id" : ObjectId("5f97bb1a0436fe0f6dbb0526"), "age" : 1, "length" : 30 }
{ "_id" : ObjectId("5f97bb200436fe0f6dbb0527"), "age" : 7, "length" : 30 }
{ "_id" : ObjectId("5f97bb860436fe0f6dbb052a"), "age" : 13, "length" : 30 }

2、使用$regex进行模糊查询

c2的数据集如下：

> db.c2.find() 
{ "_id" : ObjectId("5f97bc460436fe0f6dbb052b"), "name" : "小明", "age" : 20 }
{ "_id" : ObjectId("5f97bc4c0436fe0f6dbb052c"), "name" : "小刚", "age" : 20 }
{ "_id" : ObjectId("5f97bc570436fe0f6dbb052d"), "name" : "小明明", "age" : 20 }
{ "_id" : ObjectId("5f97bc5f0436fe0f6dbb052e"), "name" : "大明", "age" : 20 }

查name中包含“明”的数据：

> db.c2.find({name:{"$regex":"明"}}))
{ "_id" : ObjectId("5f97bc460436fe0f6dbb052b"), "name" : "小明", "age" : 20 }
{ "_id" : ObjectId("5f97bc570436fe0f6dbb052d"), "name" : "小明明", "age" : 20 }
{ "_id" : ObjectId("5f97bc5f0436fe0f6dbb052e"), "name" : "大明", "age" : 20 }

3、使用$text对有text索引的字段进行模糊查询

先对c4添加索引：

> db.c4.createIndex( { subject: "text" } )

c4的数据集如下：

> db.c4.find( )
{ "_id" : 1, "subject" : "hello world", "title" : "hello" }
{ "_id" : 2, "subject" : "hello python", "title" : "python" }
{ "_id" : 3, "subject" : "hello java", "title" : "java" }
{ "_id" : 4, "subject" : "hello mongodb", "title" : "mongodb" }
{ "_id" : 5, "subject" : "中文", "title" : "mongodb" }

使用"$text"进行查询

> db.c4.find({"$text":{"$search":"world"}})
{ "_id" : 1, "subject" : "hello world", "title" : "hello" }

4、使用$where进行查询

c5的数据集如下：

> db.c5.find()
{ "_id" : ObjectId("5f98df8e4262ff84adb202d5"), "a" : { "a" : 1 }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98df9c4262ff84adb202d6"), "a" : { "a" : 1, "b" : "1" }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98df9f4262ff84adb202d7"), "a" : { "a" : 1, "b" : "2" }, "b" : 2, "c" : 3 }

查询某条内嵌文档中a的值跟b的值相等的数据：

> db.c5.find({$where:"this.a.a==this.a.b"})
{ "_id" : ObjectId("5f98df9c4262ff84adb202d6"), "a" : { "a" : 1, "b" : "1" }, "b" : 2, "c" : 3 }

二、逻辑查询操作符

方法	描述
$and	并且，两个条件同时成立
$nor	两个条件同时不成立
$not	不是某个值
$or	或者，两个条件只成立一个

c6的数据集如下：

> db.c6.find()
{ "_id" : ObjectId("5f98e4217c6aa7043a43d2f8"), "subject" : "hello world", "title" : "hello" }
{ "_id" : ObjectId("5f98e4297c6aa7043a43d2f9"), "subject" : "hello python", "title" : "hello" }
{ "_id" : ObjectId("5f98e42e7c6aa7043a43d2fa"), "subject" : "hello java", "title" : "hello" }

1、使用$and进行查询

查询subject为hello java,title为hello的文档

> db.c6.find({$and:[{"subject":"hello java"},{"title":"hello"}]})
{ "_id" : ObjectId("5f98e42e7c6aa7043a43d2fa"), "subject" : "hello java", "title" : "hello" }

2、使用$nor进行查询

查询subject不为hello java并且title不为hel的文档

> db.c6.find({$nor:[{"subject":"hello java"},{"title":"hel"}]})
{ "_id" : ObjectId("5f98e4217c6aa7043a43d2f8"), "subject" : "hello world", "title" : "hello" }
{ "_id" : ObjectId("5f98e4297c6aa7043a43d2f9"), "subject" : "hello python", "title" : "hello" }

3、使用$not进行查询

查询b不小于2的值

> db.c5.find({b:{$not:{$lt:2}}})
{ "_id" : ObjectId("5f98df8e4262ff84adb202d5"), "a" : { "a" : 1 }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98df9c4262ff84adb202d6"), "a" : { "a" : 1, "b" : "1" }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98df9f4262ff84adb202d7"), "a" : { "a" : 1, "b" : "2" }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98e0674262ff84adb202d8"), "a" : { "a" : 1, "b" : 1 }, "b" : 2, "c" : 3 }

4、使用$or进行查询

查询b为2或者c为4的值

> db.c5.find({$or:[{b:2},{c:4}]})
{ "_id" : ObjectId("5f98df8e4262ff84adb202d5"), "a" : { "a" : 1 }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98df9c4262ff84adb202d6"), "a" : { "a" : 1, "b" : "1" }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98df9f4262ff84adb202d7"), "a" : { "a" : 1, "b" : "2" }, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("5f98e0674262ff84adb202d8"), "a" : { "a" : 1, "b" : 1 }, "b" : 2, "c" : 3 }

三、比较查询操作符

方法	描述
$gt	大于
$gte	大于等于
$lt	小于
$lte	小于等于
$in	在…之中
$nin	不在…之中
$ne	不等于
$eq	等于

c7的数据集如下

> db.c7.find()
{ "_id" : 1, "age" : 20, "length" : 17 }
{ "_id" : 2, "age" : 23, "length" : 17 }
{ "_id" : 3, "age" : 24, "length" : 17 }
{ "_id" : 4, "age" : 19, "length" : 17 }

1、$gt查询年龄大于20的

> db.c7.find({age:{$gt:20}})
{ "_id" : 2, "age" : 23, "length" : 17 }
{ "_id" : 3, "age" : 24, "length" : 17 }

2、$gte查询年龄大于等于20的

> db.c7.find({age:{$gte:20}})
{ "_id" : 1, "age" : 20, "length" : 17 }
{ "_id" : 2, "age" : 23, "length" : 17 }
{ "_id" : 3, "age" : 24, "length" : 17 }

3、$lt查询年龄小于20的

> db.c7.find({age:{$lt:20}})
{ "_id" : 4, "age" : 19, "length" : 17 }

4、$lte查询年龄小于等于20的

> db.c7.find({age:{$lte:20}})
{ "_id" : 1, "age" : 20, "length" : 17 }
{ "_id" : 4, "age" : 19, "length" : 17 }

5、$in查询年龄在20,21,22之中的

> db.c7.find({age:{$in:[20,21,22]}})
{ "_id" : 1, "age" : 20, "length" : 17 }

6、$nin查询年龄不在20,21,22之中的

> db.c7.find({age:{$nin:[20,21,22]}})
{ "_id" : 2, "age" : 23, "length" : 17 }
{ "_id" : 3, "age" : 24, "length" : 17 }
{ "_id" : 4, "age" : 19, "length" : 17 }

7、$ne查询年龄不等于20的

> db.c7.find({age:{$ne:20}})
{ "_id" : 2, "age" : 23, "length" : 17 }
{ "_id" : 3, "age" : 24, "length" : 17 }
{ "_id" : 4, "age" : 19, "length" : 17 }

8、$eq查询年龄等于20的

> db.c7.find({age:{$eq:20}})
{ "_id" : 1, "age" : 20, "length" : 17 }

四、数组查询操作符

方法	描述
$all	匹配数组中包含某个数组的所有数据
$size	匹配数组长度为某个数的数据
$elemMatch	查询数组中至少有一个元素满足所有指定条件的文档
$slice	对数组返回的值切片

c8的数据集如下：

> db.c8.find()
{ "_id" : ObjectId("5f992b2b7c6aa7043a43d2fb"), "name" : "zs", "score" : [ 10, 80, 90 ] }
{ "_id" : ObjectId("5f992b347c6aa7043a43d2fc"), "name" : "yz", "score" : [ 70, 80, 90 ] }
{ "_id" : ObjectId("5f992b407c6aa7043a43d2fd"), "name" : "lh", "score" : [ 70, 85, 92 ] }
{ "_id" : ObjectId("5f992b497c6aa7043a43d2fe"), "name" : "llx", "score" : [ 79, 85, 92 ] }
{ "_id" : ObjectId("5f992e637c6aa7043a43d2ff"), "name" : "xm", "score" : [ 79, 85 ] }

1、$all查询score包含[70,80]的数据

> db.c8.find({score:{$all:[70,80]}})
{ "_id" : ObjectId("5f992b347c6aa7043a43d2fc"), "name" : "yz", "score" : [ 70, 80, 90 ] }

2、$size查询score长度为3数据

> db.c8.find({score:{$size:2}})
{ "_id" : ObjectId("5f992e637c6aa7043a43d2ff"), "name" : "xm", "score" : [ 79, 85 ] }

3、$elemMatch查询数组中至少有一个元素满足所有指定条件的文档

查询score中至少有一个介于80和60之间

> db.c8.find({score:{$elemMatch:{$lt:80,$gt:60}}})
{ "_id" : ObjectId("5f992b347c6aa7043a43d2fc"), "name" : "yz", "score" : [ 70, 80, 90 ] }
{ "_id" : ObjectId("5f992b407c6aa7043a43d2fd"), "name" : "lh", "score" : [ 70, 85, 92 ] }
{ "_id" : ObjectId("5f992b497c6aa7043a43d2fe"), "name" : "llx", "score" : [ 79, 85, 92 ] }
{ "_id" : ObjectId("5f992e637c6aa7043a43d2ff"), "name" : "xm", "score" : [ 79, 85 ] }

4、查询数组第一个值为70的数据

> db.c8.find({"score.0":70})
{ "_id" : ObjectId("5f992b347c6aa7043a43d2fc"), "name" : "yz", "score" : [ 70, 80, 90 ] }
{ "_id" : ObjectId("5f992b407c6aa7043a43d2fd"), "name" : "lh", "score" : [ 70, 85, 92 ] }

五、元查询操作符

方法名	描述
$type	数据类型操作符
$exists	判断字段是否存在，1为存在，0为不存在

1、$type查询数据为某个类型的数据

类型	数字	备注
Double	1
String	2
Object	3
Array	4
Binary data	5
Undefined	6	已废弃。
Object id	7
Boolean	8
Date	9
Null	10
Regular Expression	11
JavaScript	13
Symbol	14
JavaScript (with scope)	15
32-bit integer	16
Timestamp	17
64-bit integer	18
Min key	255	Query with -1。
Max key	127

c9的数据集为：

> db.c9.find()
{ "_id" : ObjectId("5f993d7b7c6aa7043a43d300"), "name" : "zs", "age" : 20 }
{ "_id" : ObjectId("5f993d847c6aa7043a43d301"), "name" : "ls", "age" : "20" }
{ "_id" : ObjectId("5f993e247c6aa7043a43d302"), "name" : "ww", "age" : 20, "score" : 95 }

查询age为字符串类型的数据

> db.c9.find({age:{"$type":2}})
{ "_id" : ObjectId("5f993d847c6aa7043a43d301"), "name" : "ls", "age" : "20" }

2、$exists查询某个字段存在的数据

> db.c9.find({score:{$exists:1}})
{ "_id" : ObjectId("5f993e247c6aa7043a43d302"), "name" : "ww", "age" : 20, "score" : 95 }
> db.c9.find({score:{$exists:0}})
{ "_id" : ObjectId("5f993d7b7c6aa7043a43d300"), "name" : "zs", "age" : 20 }
{ "_id" : ObjectId("5f993d847c6aa7043a43d301"), "name" : "ls", "age" : "20" }