db.collection.aggregate(pipeline, options)
文档定义了操作中使用的聚合管道阶段和聚合操作符
文档声明了一些和聚合操作的参数字段路径表达式
$
: 使用 $ 来表示路径
$
使用 $ 和 . 来指示内嵌文档字段路径
系统变量表达式
$$
: 使用 $$ 指示系统变量
$$CURRENT
: 指示管道中当前操作的文档, $$CURRENT.
和 $
是等效的
常量表达式
$literal:
: 指示常量
$literal: "$name"
: 指示常量字符串 "\(name"(这里的\)被当做常量处理,而不是字段路径表达式)
$project
: 对输入文档进行再次投影
可以用了灵活的的控制输出文档的格式
也可用了剔除不相关的字段,以优化聚合管道操作的性能
> db.accounts.find()
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20 }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20 }
>
// 对账户文档进行重新映射
> db.accounts.aggregate([
... {
... $project: {
... _id: 0,
... balance: 1,
... clientName: "$name.firstName"
... }
... }
... ])
{ "balance" : 20, "clientName" : "alice" }
{ "balance" : 20, "clientName" : "bob" }
> db.accounts.aggregate([
... {
... $project: {
... _id: 0,
... balance: 1,
... nameArrage: ["$name.firstName", "$name.middleName", "$name.lastName"]
... }
... }
... ])
{ "balance" : 20, "nameArrage" : [ "alice", null, "wong" ] }
{ "balance" : 20, "nameArrage" : [ "bob", null, "yang" ] }
$match
: 对输入文档进行筛选 $match
中使用的文档筛选语法,和读取文档时的筛选语法相同
应该尽量在聚合管道开始的时候使用 $match
这样可以减少后续阶段中需要处理的文档数量,优化聚合操作的性能
> db.accounts.aggregate([
... {
... $match: {
... $or: [
... {balance: {$gt: 10, $lt: 30}},
... {"name.lastName": "yang"}
... ]
... }
... },
... {
... $project: {
... _id: 0
... }
... }
... ])
{ "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20 }
{ "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20 }
$limit
: 筛选出管道中前 N 篇文档 $skip
: 跳过管道内前 N 篇文档 > db.accounts.aggregate([
... {
... $limit: 1
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20 }
>
>
> db.accounts.aggregate([
... {
... $skip: 1
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20 }
>
$unwind
: 展开输入文档中的数组字段> db.accounts.find()
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
>
>
> db.accounts.aggregate([
... {
... $unwind: {
... path: "$currency"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "CNY" }
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "USD" }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
>
// 展开数组时添加元素的位置
> db.accounts.aggregate([
... {
... $unwind: {
... path: "$currency",
... includeArrayIndex: "ccyIndex"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "CNY", "ccyIndex" : NumberLong(0) }
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "USD", "ccyIndex" : NumberLong(1) }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP", "ccyIndex" : null }
>
// 默认展开文档时,如果文档中指定数组字段不存在,或者为null, 或者为空[], 将会被过滤掉
> db.accounts.find()
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40 }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null }
>
> db.accounts.aggregate([
... {
... $unwind: {
... path: "$currency"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "CNY" }
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "USD" }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
// 如果展示数组时保留空数组,或者不存在数组字段的文档, 同(preserveNullAndEmptyArrays: true)实现
> db.accounts.aggregate([
... {
... $unwind: {
... path: "$currency",
... preserveNullAndEmptyArrays: true
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "CNY" }
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "USD" }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40 }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60 }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null }
$sort
: 对输入文档进行排序 > db.accounts.aggregate([
... {
... $sort: {balance: -1, "name.LastName": 1}
... }
... ])
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40 }
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
$lookup
: 类似于join操作,对其他 collection 进行查询,结果写入管道文档中 官方文档地址
语法1:
{
$lookup:
{
from:
localField:
foreignField:
as:
语法2:
],{
$lookup:
{
from:
let: {
pipeline: [
as:
示例1:
> db.forex.find()
{ "_id" : ObjectId("634d73585799d43c905c719a"), "ccy" : "USD", "rate" : 6.91, "data" : ISODate("2018-12-12T00:00:00Z") }
{ "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") }
{ "_id" : ObjectId("634d73585799d43c905c719c"), "ccy" : "CNY", "rate" : 1, "data" : ISODate("2018-12-12T00:00:00Z") }
>
> db.account.find()
> db.accounts.find()
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40 }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null }
>
> db.accounts.aggregate([
... {
... $lookup: {
... from: "forex",
... localField: "currency",
... foreignField: "ccy",
... as: "forexData"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ], "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719a"), "ccy" : "USD", "rate" : 6.91, "data" : ISODate("2018-12-12T00:00:00Z") }, { "_id" : ObjectId("634d73585799d43c905c719c"), "ccy" : "CNY", "rate" : 1, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP", "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40, "forexData" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ], "forexData" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null, "forexData" : [ ] }
> db.accounts.aggregate([
... {
... $unwind: {
... path: "$currency"
... }
... },
... {
... $lookup: {
... from: "forex",
... localField: "currency",
... foreignField: "ccy",
... as: "forexData"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "CNY", "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719c"), "ccy" : "CNY", "rate" : 1, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : "USD", "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719a"), "ccy" : "USD", "rate" : 6.91, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP", "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
示例2:
# 将特定日期外汇汇率写入银行账户文档 (不相关查询 3.6版本开始支持)
db.forex.find()
{ "_id" : ObjectId("634d73585799d43c905c719a"), "ccy" : "USD", "rate" : 6.91, "data" : ISODate("2018-12-13T00:00:00Z") }
{ "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") }
{ "_id" : ObjectId("634d73585799d43c905c719c"), "ccy" : "CNY", "rate" : 1, "data" : ISODate("2018-12-13T00:00:00Z") }
>
> db.accounts.find()
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP" }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40 }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null }
>
> db.accounts.aggregate([
... {
... $lookup: {
... from: "forex",
... pipeline: [
... {
... $match: {
... data: new Date("2018-12-12")
... }
... }
... ],
... as: "forexData"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ], "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP", "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40, "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ], "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null, "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
>
# 将特定日期的外汇汇率写入余额大于50的银行账户
> db.accounts.aggregate([
... {
... $lookup: {
... from: "forex",
... let: {bal: "$balance"},
... pipeline: [
... { $match:
... { $expr:
... { $and:
... [
... {$eq: ["$data", new Date("2018-12-12")]},
... {$gt: ["$$bal", 50]}
... ]
...
... }
...
... }
... }
... ],
... as: "forexData"
... }
... }
... ])
{ "_id" : ObjectId("634d622b5799d43c905c7195"), "name" : { "firstName" : "alice", "lastName" : "wong" }, "balance" : 20, "currency" : [ "CNY", "USD" ], "forexData" : [ ] }
{ "_id" : ObjectId("634d622b5799d43c905c7196"), "name" : { "firstName" : "bob", "lastName" : "yang" }, "balance" : 20, "currency" : "GBP", "forexData" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7197"), "name" : { "firstName" : "charlie", "lastName" : "gordon" }, "balance" : 40, "forexData" : [ ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7198"), "name" : { "firstName" : "david", "lastName" : "wu" }, "balance" : 60, "currency" : [ ], "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
{ "_id" : ObjectId("634d6ec45799d43c905c7199"), "name" : { "firstName" : "eddie", "lastName" : "kim" }, "balance" : 100, "currency" : null, "forexData" : [ { "_id" : ObjectId("634d73585799d43c905c719b"), "ccy" : "GBP", "rate" : 8.72, "data" : ISODate("2018-12-12T00:00:00Z") } ] }
$group
: 对输入文档进行分组 官方文档地址
语法:
{
$group:
{
_id:
...
}
}
_id
: 定义分组规则, 就是通过那个字段进行分组
: 可以使用聚合操作符来定义新字段(求和,平均 ...)$group
: 可以返回文档中某一字段的所有(不重复)值_id: null
), 相当于只分了一组# 按照交易货币来分组交易记录(可以返回文档中某一字段的所有(不重复)值)
> db.transactions.find()
{ "_id" : ObjectId("635d46341b8b77383133f015"), "symbol" : "600519", "qty" : 100, "price" : 567.4, "currency" : "CNY" }
{ "_id" : ObjectId("635d46341b8b77383133f016"), "symbol" : "AMZN", "qty" : 1, "price" : 1377.5, "currency" : "USD" }
{ "_id" : ObjectId("635d46341b8b77383133f017"), "symbol" : "AAPL", "qty" : 2, "price" : 150.7, "currency" : "USD" }
>
> db.transactions.aggregate([
... {
... $group: {
... _id: "$currency"
... }
... }
... ])
{ "_id" : "USD" }
{ "_id" : "CNY" }
# 使用聚合操作符计算分组聚合值
> db.transactions.aggregate([
... {
... $group: {
... _id: "$currency",
... totalQty: {$sum: "$qty"},
... totalNotional: {$sum: { $multiply: ["$price", "$qty"]}},
... avgPrice: {$avg: "$price"},
... count: {$sum: 1},
... maxNotional: {$max: {$multiply: ["$price", "$qty"]}},
... minNotional: {$min: {$multiply: ["$price", "$qty"]}}
... }
... }
... ])
{ "_id" : "USD", "totalQty" : 3, "totalNotional" : 1678.9, "avgPrice" : 764.1, "count" : 2, "maxNotional" : 1377.5, "minNotional" : 301.4 }
{ "_id" : "CNY", "totalQty" : 100, "totalNotional" : 56740, "avgPrice" : 567.4, "count" : 1, "maxNotional" : 56740, "minNotional" : 56740 }
# 使用聚合操作符计算所有文档的聚合值(`_id: null`), 相当于只分了一个组
> db.transactions.aggregate([
... {
... $group: {
... _id: null,
... totalQty: {$sum: "$qty"},
... totalNotional: {$sum: { $multiply: ["$price", "$qty"]}},
... avgPrice: {$avg: "$price"},
... count: {$sum: 1},
... maxNotional: {$max: {$multiply: ["$price", "$qty"]}},
... minNotional: {$min: {$multiply: ["$price", "$qty"]}}
... }
... }
... ])
{ "_id" : null, "totalQty" : 103, "totalNotional" : 58418.9, "avgPrice" : 698.5333333333333, "count" : 3, "maxNotional" : 56740, "minNotional" : 301.4 }
# 使用聚合操作符创建数组字段,并将指定字段,push到数组中
> db.transactions.aggregate([
... {
... $group: {
... _id: "$currency",
... "symbols": {$push: "$symbol"}
... }
... }
... ])
{ "_id" : "USD", "symbols" : [ "AMZN", "AAPL" ] }
{ "_id" : "CNY", "symbols" : [ "600519" ] }
$out
: 获取聚合管道返回的文档并将它们写入指定的集合。从 MongoDB 4.4 开始,您可以指定输出数据库 语法:
{ $out: { db: "
# 将聚合管道中的文档写入一个新集合中
> db.transactions.aggregate([
... {
... $group: {
... _id: "$currency",
... "symbols": {$push: "$symbol"}
... }
... },
... {$out: "output"}
... ])
>
> db.output.find()
{ "_id" : "USD", "symbols" : [ "AMZN", "AAPL" ] }
{ "_id" : "CNY", "symbols" : [ "600519" ] }
# 将聚合管道中的文档写入一个已经存在的集合中,已存在的集合会被清空,然后写入的值,原集合中的数据会丢失,非常危险
> db.transactions.aggregate([
... {
... $group: {
... _id: "$symbol",
... totalNotional: {$sum: {$multiply: ["$price", "$qty"]}}
... }
... },
... {$out: "output"}
... ])
> db.output.find()
{ "_id" : "AMZN", "totalNotional" : 1377.5 }
{ "_id" : "AAPL", "totalNotional" : 301.4 }
{ "_id" : "600519", "totalNotional" : 56740 }
>
db..aggregate(, )
文档定义了操作中使用的聚合管道阶段和聚合操作符
文档声明了一些和聚合操作的参数allowDiskUse:
> db.transactions.aggregate([
... {
... $group: {
... _id: "$currency",
... "symbols": {$push: "$symbol"}
... }
... }
... ],
... {allowDiskUse: true}
... )
{ "_id" : "CNY", "symbols" : [ "600519" ] }
{ "_id" : "USD", "symbols" : [ "AMZN", "AAPL" ] }
聚合阶段顺序的优化
$project + $match
, $match 阶段会在 $project 阶段前运行
db.transactions.aggregate([
{
$project: {
_id: 0, symbol: 1, currency: 1,
notional: {$multiply: ["$price", "$qty"]}
}
},
{
$match: {
currency: "USD",
notional: {$gt: 1000}
}
}
])
# 实际执行时,会优化成如下
db.transactions.aggregate([
{
$match: {
currency: "USD",
}
},
{
$project: {
_id: 0, symbol: 1, currency: 1,
notional: {$multiply: ["$price", "$qty"]}
}
},
{
$match: {
notional: {$gt: 1000}
}
}
])
$sort + $match
, $match 阶段会在 $sort 阶段之前运行$project + $skip
, $skip 阶段会在 $project 阶段之前运行聚合阶段合并的优化
$sort + $limit
, 如果两者之间没有夹杂会改变文档数量的聚合阶段, $sort 和 $limit 阶段可以合并db.transactions.aggregate([
{
$sort: {price: 1}
},
{
$project: {
_id: 0, symbol: 1, currency: 1,
notional: {$multiply: ["$price", "$qty"]}
}
},
{
$limit: 2
}
])
# 实际执行
db.transactions.aggregate([
{
$sort: {price: 1} + $limit: 2 # 伪代码
},
{
$project: {
_id: 0, symbol: 1, currency: 1,
notional: {$multiply: ["$price", "$qty"]}
}
}
])
$limit + $limit
$skip + $skip
$match + $match
: 连续 \(limit,\)skip,$match 阶段排列在一起时,可以合并为一个阶段{ $limit: 10} + { $limit: 5} = { $limit: 5}
{ $skip: 10} + { $skip: 5} = { $skip: 15}
# $match 合并
{ $match: {currency: "USD"}}
{ $match: {qty: 1}
{
$match: {
$and: [
{"currency": "USD"},
{"qty": 1}
]
}
}
$lookup + $unwind
, 连续排列在一起的 $lookup 和 $unwind 阶段,如果 $unwind 应用在 $lookup 阶段创建的 as 字段上,则两者可以合并db.accounts.aggregate([
{
$lookup: {
from: "forex",
localField: "currency",
foreignField: "ccy",
as: "forexData"
}
},
{
$unwind: "$forexData"
}
])
db.accounts.aggregate([
{
$lookup: {
from: "forex",
localField: "currency",
foreignField: "ccy",
as: "forexData"
}
}
+ // 伪码,合并后可以节省临时性文档的创建和更改
{
$unwind: "$forexData"
}
])