文章目录
- mongo shell (javascript 客户端)
- 库和集合
- 新增
- 可选参数 安全级别 writeConcern
- 可选参数 是否顺序写入 ordered
- 数据 单独主键 _id
- 数据 复合主键
- 测试
- 查询
- 全量查询
- 条件查询
- 精准等值查询
- 返回指定字段
- Mongo查询条件和SQL查询对照表
- $exists 字段匹配查询 匹配包含查询字段的文档
- 逻辑操作符匹配
- 文档游标
- 文档投影
- $slice 返回数组中的部分元素
- $elemMatch 数组元素匹配
- 更新
- 删除
- explain
- 聚合操作(阶段(stage))
- $addFields
- $match
- $project
- $literal
- $group
- $max, $min, $sum, $avg
- $last, $first
- $addToSet
- $push
- $skip, $limit
- $unwind
- includeArrayIndex
- preserveNullAndEmptyArrays
- sort
- $lookup
- $out
- 管道优化
mongo shell (javascript 客户端)
连接进来之后,就是一个命令行的窗体, 这也是JavaScript 语言的运行环境,所以可以在上面用javascript 进行脚本编写,执行,操作,管理数据库。
库和集合
# 显示当前所在的数据库, 默认使用 test 数据库
db
# 切换数据库, 首次添加数据时自动创建
use mrathena
# 查看数据库, 有数据的数据库才会显示
show dbs
# 删除当前数据库
db.dropDatabase()
# 查看集合
show collections
# 创建集合
# name: 要创建的集合名称
# options: 可选参数, 指定有关内存大小及索引的选项
# capped: 布尔: (可选)如果为 true,则创建固定集合。固定集合是指有着固定大小的集合,当达到最大值时,它会自动覆盖最早的文档。当该值为 true 时,必须指定 size 参数。
# autoIndexId: 布尔: (可选)如为 true,自动在 _id 字段创建索引。默认为 false。3.2 之后不再支持该参数。
# size: 数值: (可选)为固定集合指定一个最大值,即字节数。如果 capped 为 true,也需要指定该字段。
# max: 数值: (可选)指定固定集合中包含文档的最大数量。
# 在插入文档时,MongoDB 首先检查固定集合的 size 字段,然后检查 max 字段。
db.createCollection(name, options)
# 删除集合
# 如果成功删除选定集合,则 drop() 方法返回 true,否则返回 false。
# 不但删除集合内的所有文档,且删除集合的索引
# db.collection.remove 也可以删除所有文档, 但是效率比较低, 可以使用drop删除集合, 再重新创建集合以及索引。
db.collection.drop()
新增
MongoDB以集合(collection)的形式组织数据,文档的数据结构和 JSON 基本一样。所有存储在集合中的数据都是 BSON 格式。BSON 是一种类似 JSON 的二进制形式的存储格式,是 Binary JSON 的简称。collection 相当于关系型数据库中的表,如果collection不存在,当对其进行操作时,将会自动创建一个collection
# 将一个或多个文档插入集合中
db.collection.insert(
<document or array of documents>,
{
writeConcern: <document>,
ordered: <boolean>
}
)
# 添加单个文档, 可选参数
db.collection.insertOne(
<document>,
{
writeConcern: <document>
}
)
# 批量添加文档, 可选参数
db.collection.insertMany(
[ <document 1> , <document 2>, ... ],
{
writeConcern: <document>,
ordered: <boolean>
}
)
insertOne 和 insertMany 命令不支持 explain 命令. inser t支持 explain 命令
可选参数 安全级别 writeConcern
writeConcern 定义了本次文档创建操作的安全写级别简单来说, 安全写级别用来判断一次数据库写入操作是否成功,安全写级别越高,丢失数据的风险就越低,然而写入操作的延迟也可能更高。
writeConcern 决定一个写操作落到多少个节点上才算成功。 writeConcern的取值包括
- 0: 发起写操作,不关心是否成功
- 1- n(集群中最大数据节点数): 写操作需要被复制到指定节点数才算成功
- majority: 写操作需要被复制到大多数节点上才算成功
发起写操作的程序将阻塞到写操作到达指定的节点数为止
db.collection.insertOne(doc, {writeConcern: 安全级别})
可选参数 是否顺序写入 ordered
ordered: 是否按顺序进行写入
- true: 顺序写入时,一旦遇到错误,便会退出,剩余的文档无论正确与否,都不会写入, 默认为 true
- false: 乱序写入,则只要文档可以正确写入就会正确写入,不管前面的文档是否是错误的文档
db.collection.insertMany([{doc}, {doc}, ...], {writeConcern: doc, ordered: true/false})
数据 单独主键 _id
插入文档时,如果没有显示指定主键,MongoDB将默认创建一个主键,字段固定为_id, ObjectId() 可以快速生成的12字节id 作为主键,ObjectId 前四个字节代表了主键生成的时间,精确到秒。主键ID在客户端驱动生成,一定程度上代表了顺序性,但不保证顺序性, 可以通过ObjectId(“id值”).getTimestamp() 获取创建时间。
ObjectId("5fe0ef13ac05741b758b3ced").getTimestamp();
# ISODate("2020-12-26T09:25:29Z")
数据 复合主键
可以使用文档作为复合主键, 注意: 复合主键,字段顺序换了,会当做不同的对象被创建,即使内容完全一致
db.collection.insert({_id: {pk1:1, pk2:1}, username:"test", createdAt: new Date()})
测试
db.customer.insert({username:"mrathena"})
db.customer.insertMany([{username:"nihao"},{username:"nishishui"}])
db.customer.insert({username:"mrathena2"}, {writeConcern:0})
db.customer.insert({_id:1,username:"id"})
db.customer.insert({_id: {pk1:1, pk2:1}, username:"test", createdAt: new Date()})
db.customer.insert({_id: {pk1:1, pk2:2}, username:"test", createdAt: new Date()})
db.customer.insert({_id: {pk1:2, pk2:1}, username:"test", createdAt: new Date()})
db.customer.insert({_id: {pk2:2, pk1:1}, username:"test", createdAt: new Date()})
# 以下是主键冲突失败的
db.customer.insert({_id: {pk1:1, pk2:2}, username:"test", createdAt: new Date()})
# 以下是未知异常的, 猜测和单机模式下的安全级别有关
db.customer.insert({username:"mrathena3"}, {writeConcern:1})
db.customer.insert({username:"mrathena4"}, {writeConcern:"majority"})
> db.customer.find()
{ "_id" : ObjectId("5fe7001a961cf555d496fd9d"), "username" : "mrathena" }
{ "_id" : ObjectId("5fe70091961cf555d496fd9e"), "username" : "nihao" }
{ "_id" : ObjectId("5fe70091961cf555d496fd9f"), "username" : "nishishui" }
{ "_id" : ObjectId("5fe70189961cf555d496fda1"), "username" : "mrathena2" }
{ "_id" : 1, "username" : "id" }
{ "_id" : { "pk1" : 1, "pk2" : 1 }, "username" : "test", "createdAt" : ISODate("2020-12-26T10:20:20.900Z") }
{ "_id" : { "pk1" : 1, "pk2" : 2 }, "username" : "test", "createdAt" : ISODate("2020-12-26T10:20:35.932Z") }
{ "_id" : { "pk1" : 2, "pk2" : 1 }, "username" : "test", "createdAt" : ISODate("2020-12-26T10:20:50.204Z") }
{ "_id" : { "pk2" : 2, "pk1" : 1 }, "username" : "test", "createdAt" : ISODate("2020-12-26T10:21:08.733Z") }
>
查询
以下面数据为例演示
db.inventory.insertMany([
{ item: "journal", qty: 25, status: "A", size: { h: 14, w: 21, uom: "cm" }, tags: [ "blank", "red" ] },
{ item: "notebook", qty: 50, status: "A", size: { h: 8.5, w: 11, uom: "in" }, tags: [ "red", "blank" ] },
{ item: "paper", qty: 10, status: "D", size: { h: 8.5, w: 11, uom: "in" }, tags: [ "red", "blank", "plain" ] },
{ item: "planner", qty: 0, status: "D", size: { h: 22.85, w: 30, uom: "cm" }, tags: [ "blank", "red" ] },
{ item: "postcard", qty: 45, status: "A", size: { h: 10, w: 15.25, uom: "cm" }, tags: [ "blue" ] }
]);
db.collection.find(查询条件, 投影设置)
db.inventory.find({qty:0},{item:1});
全量查询
# 查询整个集合中的所有文档
db.inventory.find({})
# 查询整个集合中的所有文档并格式化展示
db.inventory.find({}).pretty()
db.inventory.find( { }, { item: 1, status: 1 } );
db.inventory.find( { }, { _id: 0, item: 1, status: 1 } );
条件查询
精准等值查询
db.inventory.find( { status: "D" } );
db.inventory.find( { qty: 0 } );
# 嵌套对象精准查询
db.inventory.find( { "size.uom": "in" } );
# 多字段精准等值查询
db.inventory.find( { qty: 0, status: "D" } );
返回指定字段
# 默认会返回_id 字段, 同样可以通过指定 _id:0 ,不返回_id 字段
db.inventory.find( { }, { item: 1, status: 1 } );
db.inventory.find( { }, { _id: 0, item: 1, status: 1 } );
Mongo查询条件和SQL查询对照表
SQL | MQL |
a<>1 或者 a!=1 | { a : {$ne: 1}} |
a>1 | { a: {$gt:1}} |
a>=1 | { a: {$gte:1}} |
a<1 | { a: {$lt:1}} |
a<=1 | { a: { $lte:1}} |
in | { a: { $in:[ x, y, z]}} |
not in | { a: { $nin:[ x, y, z]}} |
a is null | { a: { $exists: false }} |
db.inventory.find({qty:{$ne:10}})
db.inventory.find({qty:{$gt:10}})
db.inventory.find({qty:{$gte:10}})
db.inventory.find({qty:{$lt:10}})
db.inventory.find({qty:{$lte:10}})
db.inventory.find({qty:{$in:[25,45]}})
db.inventory.find({qty:{$nin:[25,45]}})
$exists 字段匹配查询 匹配包含查询字段的文档
db.inventory.find({qty:{$exists:true}})
db.inventory.find({qty:{$exists:false}})
逻辑操作符匹配
- $not : 匹配筛选条件不成立的文档, 注意: $not 会把不包含该字段的数据也检索出来
- $and : 匹配多个筛选条件同时满足的文档, 支持多个条件, 作用在不同字段上时可以省略
- $or : 匹配至少一个筛选条件成立的文档, 支持多个条件
- $nor : 匹配多个筛选条件全部不满足的文档, 支持多个条件
db.inventory.insert({status:"E"})
db.inventory.find({qty:{$not:{$gt:10}}})
db.inventory.find({$and:[{qty:0},{status:"A"}]})
db.inventory.find({$or:[{qty:0},{qty:10},{status:"A"}]})
db.inventory.find({$and:[{qty:{$gt:0}},{qty:{$lt:50}}]})
db.inventory.find({qty:{$gt:0,$lt:50}})
db.inventory.find({$nor:[{qty:1},{status:"E"}]})
文档游标
# skip和limit就类似mysql里面的 limit a,b 中的 a 和 b 这两个值
cursor.skip(1)
cursor.limit(1)
cursor.count(是否响应 skip 和 limit)
# 默认情况下 , 这里的count不会考虑 skip 和 limit的效果,如果希望考虑 limit 和 skip ,需要设置为 true。 分布式环境下,count 不保证数据的绝对正确
cursor.sort({field1:1,field2:1,field3:-1})
# 1 表示由小到大, -1 表示逆向排序. 当同时应用 sort,skip,limit 时, 应用的顺序为 sort > skip > limit
db.inventory.find()
db.inventory.find().skip(1)
db.inventory.find().skip(2)
db.inventory.find().skip(2).limit(1)
db.inventory.find().skip(2).limit(2)
db.inventory.find().skip(2).limit(2).count()
db.inventory.find().skip(2).limit(2).count(true)
db.inventory.find().sort({qty:1})
db.inventory.find().sort({status:1})
db.inventory.find().sort({status:1,qty:1})
db.inventory.find().sort({status:1,qty:-1})
db.inventory.find().sort({status:1,qty:-1}).skip(1).limit(1)
文档投影
db.collection.find(查询条件,投影设置)
文档投影: 可以将原始字段投影成指定名称, 也可以剔除不需要的字段(1:只展示显式指定为1的字段, 0:只隐藏显式指定为0的字段, 只能为 0/1, 非主键字段, 不能同时混选 0和1, _id 字段除外, 默认会返回 _id 字段), 能灵活控制输出文档的格式
$slice 返回数组中的部分元素
db.inventory.find({},{item:1,tags:{$slice:1}})
db.inventory.find({},{item:1,tags:{$slice:-1}})
slice: 值
- 1:数组第一个元素
- -1:最后一个元素
- -2:最后两个元素
- [1, 2]:skip, limit 对应的关系
> db.inventory.find()
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "qty" : 25, "status" : "A", "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "tags" : [ "blank", "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "qty" : 50, "status" : "A", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "tags" : [ "red", "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "qty" : 10, "status" : "D", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "tags" : [ "red", "blank", "plain" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "qty" : 0, "status" : "D", "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "tags" : [ "blank", "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "qty" : 45, "status" : "A", "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "tags" : [ "blue" ] }
>
> db.inventory.find({},{item:1,tags:{$slice:1}})
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "tags" : [ "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "tags" : [ "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "tags" : [ "blue" ] }
>
> db.inventory.find({},{item:1,tags:{$slice:-1}})
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "tags" : [ "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "tags" : [ "plain" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "tags" : [ "blue" ] }
>
> db.inventory.find({},{item:1,tags:{$slice:2}})
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "tags" : [ "blank", "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "tags" : [ "red", "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "tags" : [ "red", "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "tags" : [ "blank", "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "tags" : [ "blue" ] }
>
> db.inventory.find({},{item:1,tags:{$slice:[1,1]}})
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "tags" : [ "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "tags" : [ "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "tags" : [ ] }
$elemMatch 数组元素匹配
db.inventory.find({},{item:1,tags:{$elemMatch:{$eq:"red"}}});
db.inventory.find({},{item:1,tags:{$elemMatch:{$eq:"blue"}}});
> db.inventory.find({},{item:1,tags:1});
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "tags" : [ "blank", "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "tags" : [ "red", "blank" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "tags" : [ "red", "blank", "plain" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "tags" : [ "blank", "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "tags" : [ "blue" ] }
>
> db.inventory.find({},{item:1,tags:{$elemMatch:{$eq:"red"}}});
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner", "tags" : [ "red" ] }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard" }
>
> db.inventory.find({},{item:1,tags:{$elemMatch:{$eq:"blue"}}});
{ "_id" : ObjectId("5fe703a6961cf555d496fda3"), "item" : "journal" }
{ "_id" : ObjectId("5fe703a6961cf555d496fda4"), "item" : "notebook" }
{ "_id" : ObjectId("5fe703a6961cf555d496fda5"), "item" : "paper" }
{ "_id" : ObjectId("5fe703a6961cf555d496fda6"), "item" : "planner" }
{ "_id" : ObjectId("5fe703a6961cf555d496fda7"), "item" : "postcard", "tags" : [ "blue" ] }
更新
# 修改现有文档或集合中的文档。根据更新参数,该方法可以修改一个或多个现有文档的特定字段或完全替换现有文档
# query : update的查询条件,类似sql update查询内where后面的。
# update : update的对象和一些更新的操作符(如$,$inc...)等,也可以理解为sql update查询内set后面的
# upsert : 可选的。如果设置为true,则在没有文档符合查询条件时创建一个新文档。默认值为 false,如果找不到匹配项,则不插入新文档。默认false
# multi : 可选的。如果设置为true,则更新满足query条件的多个文档。如果设置为false,则只更新一个文档。默认值为false
# writeConcern :可选,抛出异常的级别。
db.collection.update(
<query>,
<update>,
{
upsert: <boolean>,
multi: <boolean>,
writeConcern: <document>,
collation: <document>,
arrayFilters: [ <filterdocument1>, ... ],
hint: <document|string> // Available starting in MongoDB 4.2
}
)
# filter: 更新的选择标准。提供与find()方法中相同的查询选择器。指定一个空文档 {} 以更新集合中的所有文档。
# 根据过滤器更新集合中的单个文档。
db.collection.updateOne(
<filter>,
<update>,
{
upsert: <boolean>,
writeConcern: <document>,
collation: <document>,
arrayFilters: [ <filterdocument1>, ... ],
hint: <document|string> // Available starting in MongoDB 4.2.1
}
)
# 更新与集合的指定过滤器匹配的所有文档。
db.collection.updateMany(
<filter>,
<update>,
{
upsert: <boolean>,
writeConcern: <document>,
collation: <document>,
arrayFilters: [ <filterdocument1>, ... ],
hint: <document|string> // Available starting in MongoDB 4.2.1
}
)
updateOne/updateMany 方法要求更新条件部分必须具有以下之一,否则将报错
$set 给符合条件的文档新增一个字段,有该字段则修改其值
$unset 给符合条件的文档,删除一个字段
$push: 增加一个对象到数组底部
$pop:从数组底部删除一个对象
$pull:如果匹配指定的值,从数组中删除相应的对象
$pullAll:如果匹配任意的值,从数据中删除相应的对象
$addToSet:如果不存在则增加一个值到数组
$rename 重命名字段
$inc 加减字段值
$mul 相乘字段值
$min 采用最小值
$max 次用最大值
更新文档操作只会作用在与检索条件 匹配的文档上, 如果 不包含任何更新操作符,则会直接使用 update 文档替换集合中符合文档筛选条件的文档
以下面数据为测试数据
db.customer.insertMany([
{item:"a",score:65,tag:["a","b","c"]},
{item:"b",tag:["c","d"]}
])
给 tag 中有 c 的全部文档添加或修改 value 字段
> db.customer.updateMany({tag:"c"},{$set:{value:100}})
{ "acknowledged" : true, "matchedCount" : 2, "modifiedCount" : 2 }
> db.customer.find()
{ "_id" : ObjectId("5fe7357d7257ce93c28ff7a1"), "item" : "a", "score" : 65, "tag" : [ "a", "b", "c" ], "value" : 100 }
{ "_id" : ObjectId("5fe7357d7257ce93c28ff7a2"), "item" : "b", "tag" : [ "c", "d" ], "value" : 100 }
> db.customer.updateMany({tag:"c"},{$set:{value:101}})
{ "acknowledged" : true, "matchedCount" : 2, "modifiedCount" : 2 }
> db.customer.find()
{ "_id" : ObjectId("5fe7357d7257ce93c28ff7a1"), "item" : "a", "score" : 65, "tag" : [ "a", "b", "c" ], "value" : 101 }
{ "_id" : ObjectId("5fe7357d7257ce93c28ff7a2"), "item" : "b", "tag" : [ "c", "d" ], "value" : 101 }
给 tag 中有 c 的第一个文档添加或修改 value 字段
> db.customer.updateOne({tag:"c"},{$set:{value:102}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.customer.find()
{ "_id" : ObjectId("5fe7357d7257ce93c28ff7a1"), "item" : "a", "score" : 65, "tag" : [ "a", "b", "c" ], "value" : 102 }
{ "_id" : ObjectId("5fe7357d7257ce93c28ff7a2"), "item" : "b", "tag" : [ "c", "d" ], "value" : 101 }
删除
# 默认情况下,会删除所有满足条件的文档, 可以设定参数 { justOne:true},只会删除满足添加的第一条文档
db.collection.remove(<query>,<options>)
explain
查看执行计划, 在任何语句的集合后面添加 .explain() 函数, 主要看结果的 winningPlan 部分
db.record.explain().find()
聚合操作(阶段(stage))
按一定规则分阶段对数据集进行筛选,清洗
# 语法格式
db.collection.aggregate({阶段})
db.collection.aggregate([{阶段}])
db.collection.aggregate([{阶段1},{阶段2},...{阶段N}])
获取字段信息
$ : 用 $ 指示字段路径, 如 $price
$. : 使用 $ 和 . 来指示内嵌文档的路径, 如 $orderLines.price
常量表达式
$literal: : 指示常量
系统变量表达式
$addFields
db.orders.insertMany([
{orderLines:[{price:6000,cost:5599},{price:7000,cost:6599}]},
{orderLines:[{price:3599,cost:3599},{price:4988,cost:9999}]}
])
# 将每个订单项的总标价金额和总实际金额汇总成totalPrice和totalCost两个字段, 并添加到每一个订单中
db.orders.aggregate([{
$addFields:{
totalPrice:{$sum:"$orderLines.price"},
totalCost:{$sum:"$orderLines.cost"}
}
}]);
# 添加新字段后, 再按照totalPrice倒序排序
# 第一个阶段, 添加字段, 第二个阶段, 排序
# 聚合操作 aggregate 接收数组, 每个项都是一个阶段, 按顺序执行每个阶段的操作
db.orders.aggregate([{$addFields:{
totalPrice:{$sum:"$orderLines.price"},
totalCost:{$sum:"$orderLines.cost"}
}},{$sort:{totalPrice:-1}}]);
$match
# 初始数据
db.record.insertMany([
{name:"张三",tag:["带妹吃鸡","伏地魔","老阴逼"],date:"20201228",kill:18},
{name:"张三",tag:["带妹吃鸡","描边枪法","手雷达人"],date:"20201229",kill:20},
{name:"李四",tag:["略有小成","水准缺失"],date:"20201226",kill:5},
{name:"李四",tag:["吃鸡能手","吃鸡狂人","吃鸡大神"],date:"20201227",kill:35},
{name:"张三",tag:["躺赢","厚颜无耻"],date:"20201228",kill:1},
{name:"王五",tag:["吃鸡能手","带妹吃鸡","无耻人妖"],date:"20201228",kill:25}
])
对输入文档进行筛选, 和 find() 的第一个参数相同
> db.record.aggregate({$match:{name:{$eq:"张三"}}})
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e06"), "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e09"), "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1 }
$project
文档投影, 功能等同于find()的第二个参数, 可以将原始字段投影成指定名称, 也可以剔除不需要的字段(1:只展示显式指定为1的字段, 0:只隐藏显式指定为0的字段, 只能为 0/1, 非主键字段, 不能同时混选 0和1, _id 字段除外, 默认会返回 _id 字段), 能灵活控制输出文档的格式
> db.record.aggregate({$project:{name:1}})
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三" }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e06"), "name" : "张三" }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e07"), "name" : "李四" }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e08"), "name" : "李四" }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e09"), "name" : "张三" }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e0a"), "name" : "王五" }
>
> db.record.aggregate({$project:{name:0}})
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e06"), "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e07"), "tag" : [ "略有小成", "水准缺失" ], "date" : "20201226", "kill" : 5 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e08"), "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "date" : "20201227", "kill" : 35 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e09"), "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e0a"), "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ], "date" : "20201228", "kill" : 25 }
>
> db.record.aggregate({$project:{name:1,tag:0}})
uncaught exception: Error: command failed: {
"ok" : 0,
"errmsg" : "Invalid $project :: caused by :: Cannot do exclusion on field tag in inclusion projection",
"code" : 31254,
"codeName" : "Location31254"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1058:12
@(shell):1:1
>
> db.record.aggregate({$project:{name:1,tag:1}})
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ] }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e06"), "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ] }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e07"), "name" : "李四", "tag" : [ "略有小成", "水准缺失" ] }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e08"), "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ] }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e09"), "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ] }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e0a"), "name" : "王五", "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ] }
>
> db.record.aggregate({$project:{_id:0,name:1,tag:1}})
{ "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ] }
{ "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ] }
{ "name" : "李四", "tag" : [ "略有小成", "水准缺失" ] }
{ "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ] }
{ "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ] }
{ "name" : "王五", "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ] }
>
# 第一阶段, 查出 name 是 李四 的文档, 第二阶段, 只显示文档的指定字段
> db.record.aggregate([{$match:{name:{$eq:"李四"}}},{$project:{_id:0,name:1,tag:1,date:1,kill:1}}])
{ "name" : "李四", "tag" : [ "略有小成", "水准缺失" ], "date" : "20201226", "kill" : 5 }
{ "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "date" : "20201227", "kill" : 35 }
>
# 添加不存在的 hello 字段, 值是常量字符串 world
> db.record.aggregate([{$project:{_id:0,name:1,tag:1,date:1,kill:1,hello:"我是不存在的字段"}}])
{ "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18, "hello" : "我是不存在的字段" }
{ "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20, "hello" : "我是不存在的字段" }
{ "name" : "李四", "tag" : [ "略有小成", "水准缺失" ], "date" : "20201226", "kill" : 5, "hello" : "我是不存在的字段" }
{ "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "date" : "20201227", "kill" : 35, "hello" : "我是不存在的字段" }
{ "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1, "hello" : "我是不存在的字段" }
{ "name" : "王五", "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ], "date" : "20201228", "kill" : 25, "hello" : "我是不存在的字段" }
>
# 添加不存在的 hello 字段, 值和 name 字段一致
> db.record.aggregate([{$project:{_id:0,name:1,tag:1,date:1,kill:1,hello:"$name"}}])
{ "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18, "hello" : "张三" }
{ "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20, "hello" : "张三" }
{ "name" : "李四", "tag" : [ "略有小成", "水准缺失" ], "date" : "20201226", "kill" : 5, "hello" : "李四" }
{ "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "date" : "20201227", "kill" : 35, "hello" : "李四" }
{ "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1, "hello" : "张三" }
{ "name" : "王五", "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ], "date" : "20201228", "kill" : 25, "hello" : "王五" }
>
# 修改现有字段 name
> db.record.aggregate([{$project:{_id:0,name:1,tag:1,date:1,kill:1,name:'我是不正确的名字'}}])
{ "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18, "name" : "我是不正确的名字" }
{ "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20, "name" : "我是不正确的名字" }
{ "tag" : [ "略有小成", "水准缺失" ], "date" : "20201226", "kill" : 5, "name" : "我是不正确的名字" }
{ "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "date" : "20201227", "kill" : 35, "name" : "我是不正确的名字" }
{ "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1, "name" : "我是不正确的名字" }
{ "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ], "date" : "20201228", "kill" : 25, "name" : "我是不正确的名字" }
>
$literal
常量表达式, 指定一个常量, 可以使用特殊符号
# 希望输出 $ 字符, 但是会有问题
> db.record.aggregate([{$match:{name:{$eq:"李四"}}},{$project:{_id:0,name:1,tag:1,field:"$a"}}])
{ "name" : "李四", "tag" : [ "略有小成", "水准缺失" ] }
{ "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ] }
# 使用 $literal 即可指定一个常量
> db.record.aggregate([{$match:{name:{$eq:"李四"}}},{$project:{_id:0,name:1,tag:1,field:{$literal:"$a"}}}])
{ "name" : "李四", "tag" : [ "略有小成", "水准缺失" ], "field" : "$a" }
{ "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "field" : "$a" }
>
$group
分组, 自带去重功能, 注意必须使用 “_id” 来指定用哪个字段分组, 这里的 _id 不是按照 _id 字段分组的意思
group 阶段有 100m内存的使用限制, 默认情况下,如果超过这个限制会直接返回 error,可以通过设置 allowDiskUse 为 true 来避免异常, allowDiskUse 为 true 将利用临时文件来辅助实现group操作。
> db.record.aggregate({$group:{_id:"$name"}})
{ "_id" : "张三" }
{ "_id" : "李四" }
{ "_id" : "王五" }
$max, $min, $sum, $avg
> db.record.aggregate({$group:{_id:"$name",max:{$max:"$kill"},min:{$min:"$kill"},sum:{$sum:"$kill"},avg:{$avg:"$kill"}}})
{ "_id" : "张三", "max" : 20, "min" : 1, "sum" : 39, "avg" : 13 }
{ "_id" : "李四", "max" : 35, "min" : 5, "sum" : 40, "avg" : 20 }
{ "_id" : "王五", "max" : 25, "min" : 25, "sum" : 25, "avg" : 25 }
$last, $first
> db.record.find({},{_id:0,name:1,date:1,kill:1})
{ "name" : "张三", "date" : "20201228", "kill" : 18 }
{ "name" : "张三", "date" : "20201229", "kill" : 20 }
{ "name" : "李四", "date" : "20201226", "kill" : 5 }
{ "name" : "李四", "date" : "20201227", "kill" : 35 }
{ "name" : "张三", "date" : "20201228", "kill" : 1 }
{ "name" : "王五", "date" : "20201228", "kill" : 25 }
# 获取每个人的最后一条数据中的date字段
> db.record.aggregate({$group:{_id:"$name",date:{$last:"$date"}}})
{ "_id" : "张三", "date" : "20201228" }
{ "_id" : "李四", "date" : "20201227" }
{ "_id" : "王五", "date" : "20201228" }
# 获取每个最后一条数据的date字段和每个人最先一条数据的kill字段,然后拼成新的文档
> db.record.aggregate({$group:{_id:"$name",date:{$last:"$date"},kill:{$first:"$kill"}}})
{ "_id" : "张三", "date" : "20201228", "kill" : 18 }
{ "_id" : "李四", "date" : "20201227", "kill" : 5 }
{ "_id" : "王五", "date" : "20201228", "kill" : 25 }
$addToSet
将分组中的元素添加到一个数组中,并且自动去重
> db.record.aggregate({$group:{_id:"$name",dates:{$addToSet:"$date"}}})
{ "_id" : "张三", "dates" : [ "20201228", "20201229" ] }
{ "_id" : "赵六", "dates" : [ "20201225" ] }
{ "_id" : "李四", "dates" : [ "20201226", "20201227" ] }
{ "_id" : "王五", "dates" : [ "20201228" ] }
$push
创建新的数组,将值添加进去, 不去重
> db.record.aggregate({$group:{_id:"$name",dates:{$push:"$date"}}})
{ "_id" : "张三", "dates" : [ "20201228", "20201229", "20201228" ] }
{ "_id" : "赵六", "dates" : [ "20201225" ] }
{ "_id" : "李四", "dates" : [ "20201226", "20201227" ] }
{ "_id" : "王五", "dates" : [ "20201228" ] }
$skip, $limit
> db.record.aggregate([{$match:{name:{$eq:"张三"}}},{$skip:1},{$limit:1}])
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e06"), "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20 }
$unwind
把一条包含数组的记录拆分为很多条记录,每条记录拥有数组中的一个元素,一次只能拆开一个数组,要拆开多个数组就要经历这个阶段多次,是对数组操作!
> db.record.aggregate([{$match:{name:{$eq:"张三"}}},{$limit:1},{$unwind:"$tag"}])
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "带妹吃鸡", "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "伏地魔", "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "老阴逼", "date" : "20201228", "kill" : 18 }
# 也可以加 path
> db.record.aggregate([{$match:{name:{$eq:"张三"}}},{$limit:1},{$unwind:{path:"$tag"}}])
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "带妹吃鸡", "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "伏地魔", "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "老阴逼", "date" : "20201228", "kill" : 18 }
includeArrayIndex
加上数组元素的索引值, 赋值给后面指定的字段, 必须加path
> db.record.aggregate([{$match:{name:{$eq:"张三"}}},{$limit:1},{$unwind:{path:"$tag",includeArrayIndex:"i"}}])
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "带妹吃鸡", "date" : "20201228", "kill" : 18, "i" : NumberLong(0) }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "伏地魔", "date" : "20201228", "kill" : 18, "i" : NumberLong(1) }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : "老阴逼", "date" : "20201228", "kill" : 18, "i" : NumberLong(2) }
preserveNullAndEmptyArrays
# 添加一条没有tag字段的数据
db.record.insertOne({{name:"赵六",date:"20201225",kill:7}})
# 直接查没有结果
> db.record.aggregate([{$match:{name:{$eq:"赵六"}}},{$limit:1},{$unwind:{path:"$tag"}}])
>
# 加上preserveNullAndEmptyArrays就有了
> db.record.aggregate([{$match:{name:{$eq:"赵六"}}},{$limit:1},{$unwind:{path:"$tag",preserveNullAndEmptyArrays:true}}])
{ "_id" : ObjectId("5fe8bec00450129e398d0e0b"), "name" : "赵六", "date" : "20201225", "kill" : 7 }
sort
排序, 1正序, -1倒序
> db.record.aggregate({$sort:{date:1}})
{ "_id" : ObjectId("5fe8bec00450129e398d0e0b"), "name" : "赵六", "date" : "20201225", "kill" : 7 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e07"), "name" : "李四", "tag" : [ "略有小成", "水准缺失" ], "date" : "20201226", "kill" : 5 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e08"), "name" : "李四", "tag" : [ "吃鸡能手", "吃鸡狂人", "吃鸡大神" ], "date" : "20201227", "kill" : 35 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e05"), "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e09"), "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e0a"), "name" : "王五", "tag" : [ "吃鸡能手", "带妹吃鸡", "无耻人妖" ], "date" : "20201228", "kill" : 25 }
{ "_id" : ObjectId("5fe8b6bc0450129e398d0e06"), "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20 }
$lookup
联合查询,从两个不同集合中, 根据某个关键字查询,新的字段是数组,因为可能有多条记录满足
# 语法
db.collection.aggregate([{'$lookup':{
'from':'其他集合名',
'localField':'本集合字段',
'foreignField':'其他集合字段',
'as':'保存查询结果的字段名'}
}])
# 新增号码集合
db.mobile.insertMany([
{name:"张三",mobile:"18234089811"},
{name:"张三",mobile:"18234089812"},
{name:"李四",mobile:"15347739874"},
{name:"王五",mobile:"18695175324"}
])
# 查record集合, 使用name字段关联mobile集合中的手机号码
> db.record.aggregate([{$project:{_id:0}},{$match:{name:{$eq:"张三"}}},{$lookup:{from:"mobile",localField:"name",foreignField:"name",as:"mobile"}}])
{ "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18, "mobile" : [ { "_id" : ObjectId("5fe8c2960450129e398d0e17"), "name" : "张三", "mobile" : "18234089811" }, { "_id" : ObjectId("5fe8c2960450129e398d0e18"), "name" : "张三", "mobile" : "18234089812" } ] }
{ "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20, "mobile" : [ { "_id" : ObjectId("5fe8c2960450129e398d0e17"), "name" : "张三", "mobile" : "18234089811" }, { "_id" : ObjectId("5fe8c2960450129e398d0e18"), "name" : "张三", "mobile" : "18234089812" } ] }
{ "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1, "mobile" : [ { "_id" : ObjectId("5fe8c2960450129e398d0e17"), "name" : "张三", "mobile" : "18234089811" }, { "_id" : ObjectId("5fe8c2960450129e398d0e18"), "name" : "张三", "mobile" : "18234089812" } ] }
# 优化一下
> db.record.aggregate([{$match:{name:{$eq:"张三"}}},{$lookup:{from:"mobile",localField:"name",foreignField:"name",as:"mobile"}},{$project:{_id:0,name:1,tag:1,date:1,kill:1,mobile:"$mobile.mobile"}}])
{ "name" : "张三", "tag" : [ "带妹吃鸡", "伏地魔", "老阴逼" ], "date" : "20201228", "kill" : 18, "mobile" : [ "18234089811", "18234089812" ] }
{ "name" : "张三", "tag" : [ "带妹吃鸡", "描边枪法", "手雷达人" ], "date" : "20201229", "kill" : 20, "mobile" : [ "18234089811", "18234089812" ] }
{ "name" : "张三", "tag" : [ "躺赢", "厚颜无耻" ], "date" : "20201228", "kill" : 1, "mobile" : [ "18234089811", "18234089812" ] }
$out
将聚合结果写入另一个集合
> show collections
mobile
record
>
> db.record.aggregate([{$group:{_id:"$name",dates:{$push:"$date"}}},{$out:"output"}])
>
> show collections
mobile
output
record
> db.output.find()
{ "_id" : "李四", "dates" : [ "20201226", "20201227" ] }
{ "_id" : "张三", "dates" : [ "20201228", "20201229", "20201228" ] }
{ "_id" : "赵六", "dates" : [ "20201225" ] }
{ "_id" : "王五", "dates" : [ "20201228" ] }
>
管道优化
- 投影优化: 聚合管道可以确定它是否仅需要文档中的字段的子集来获得结果。如果是这样,管道将只使用那些必需的字段,减少通过管道的数据量。
- 管道符号执行顺序优化: 对于包含投影阶段(project或unset或addFields或set)后跟match阶段的聚合管道,MongoDB 将match阶段中不需要在投影阶段计算的值的任何过滤器移动到投影前的新match阶段
- sort + match: 如果序列中带有sort后跟match,则match会移动到sort 之前,以最大程度的减少要排序的对象的数量
- project/unset + skip序列优化: 当有一个project或unset之后跟有skip序列时, skip 会移至project之前。
- limit+ limit合并: 当limit紧接着另一个时limit,两个阶段可以合并为一个阶段limit,其中限制量为两个初始限制量中的较小者。
- skip+ skip 合并: 当skip紧跟另一个skip,这两个阶段可合并成一个单一的skip,其中跳过量为总和的两个初始跳过量。
- match+ match合并: 当一个match紧随另一个紧随其后时match,这两个阶段可以合并为一个单独match的条件and
{$match:{year:2014}},{$match:{status:"A"}}
{$match:{$and:[{"year":2014},{"status":"A"}]}}