1.在查询中使用累加器
(1). $min
返回最小值。 $min在以下阶段可用:
$group
$project
$addFields (从MongoDB 3.4开始提供)
$set (从MongoDB 4.2开始提供)
$replaceRoot (从MongoDB 3.4开始提供)
$replaceWith (从MongoDB 4.2开始提供)
$match stage that includes an $expr expression
注意:如果min操作的某些文档(但不是所有文档)的字段值为null或缺少该字段,min运算符只考虑字段的非null值和非缺失值。
如果min操作的所有文档的字段值为null或缺少该字段,min运算符将为最小值返回null。在group阶段中,如果表达式解析为数组,min不会遍历数组并将数组作为一个整体进行比较。
1).例子,在group中使用min
插入基础数据:
db.min_users15.insert([
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }
])
根据item字段对文档进行分组,下面的操作使用min累加器计算每个分组的最小数量。
db.min_users15.aggregate(
[
{
$group:
{
_id: "$item",
minQuantity: { $min: "$quantity" }
}
}
]
)
2).在 $project 阶段使用
插入基础数据:
db.min_users16.insert([
{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 },
{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 },
{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }
])
以下示例使用project阶段中的min计算最小测验分数、最低实验分数以及期末考试和期中考试的最低分数:
db.min_users16.aggregate([
{ $project: { quizMin: { $min: "$quizzes"}, labMin: { $min: "$labs" }, examMin: { $min: [ "$final", "$midterm" ] } } }
])
(2). $sum
计算并返回数值之和。sum忽略非数值。 $sum 可以在以下阶段使用:
$group
$project
$addFields (从MongoDB 3.4开始提供)
$set (从MongoDB 4.2开始提供)
$replaceRoot (从MongoDB 3.4开始提供)
$replaceWith (从MongoDB 4.2开始提供)
$match stage that includes an $expr expression
注意:如果在包含数值和非数值的字段上使用,sum将忽略非数值,并返回数值的总和。如果用于集合中任何文档中都不存在的字段,sum将为该字段返回0。如果所有操作数都不是数字,sum返回0。
插入基础数据:
db.sum_users17.insert([
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }
])
1).下面的操作使用sum累加器计算每组文档的总金额和计数
db.sum_users17.aggregate(
[
{
$group:
{
_id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
count: { $sum: 1 }
}
}
]
)
2).对不存在的字段使用sum将返回值0。以下操作尝试对数量进行sum:
db.sum_users17.aggregate(
[
{
$group:
{
_id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
totalAmount: { $sum: "$qty" },
count: { $sum: 1 }
}
}
]
)
3).在 $project 阶段使用
插入基础数据
db.sum_users18.insert([
{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 },
{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 },
{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }
])
以下示例使用project阶段中的sum计算测验总成绩、实验室总成绩以及期末考试和期中考试的总成绩:
db.sum_users18.aggregate([
{
$project: {
quizTotal: { $sum: "$quizzes"},
labTotal: { $sum: "$labs" },
examTotal: { $sum: [ "$final", "$midterm" ] }
}
}
])
(3). $first
1).aggregation accumulator(聚集累加器)
返回相同分组的,第一个文档
插入基础数据:
db.sales.insert([
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") },
{ "_id" : 6, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-15T12:05:10Z") },
{ "_id" : 7, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T14:12:12Z") }
])
将文档按项目字段分组时,以下操作使用first累加器计算每个项目的第一个销售日期:
db.sales.aggregate(
[
{ $sort: { item: 1, date: 1 } },
{
$group:
{
_id: "$item",
firstSalesDate: { $first: "$date" }
}
}
]
)
2).返回数组中的第一个元素(4.4版中的新增功能。)
注意:
如果操作数解析为非空数组,则$first返回数组中的第一个元素:
如果操作数解析为空数组[],则$first不返回值。
如果操作数为null或丢失,则$first返回null。
插入基础数据:
db.example1.insertMany([
{ "_id" : 1, "x" : [ 1, 2, 3 ] }, // Non-empty array
{ "_id" : 2, "x" : [ [ ] ] }, // Non-empty array
{ "_id" : 3, "x" : [ null ] }, // Non-empty array
{ "_id" : 4, "x" : [ ] }, // Empty array
{ "_id" : 5, "x" : null }, // Is null
{ "_id" : 6 } // Is Missing
])
返回数组中的第一个元素:
db.example1.aggregate([
{ $addFields: { firstElem: { $first: "$x" } } }
])
如果操作数不能解析为数组,为null或丢失,则整个聚合操作都会出错。例如,使用以下文档创建一个测试集合example2:
db.example2.insertMany([
{ "_id" : 1, "x" : [ 1, 2, 3 ] },
{ "_id" : 2, "x" : 2 }, // x is not an array/null or missing
])
由于{{_id“:2,” x“:2}文档,以下聚合操作将返回错误:
wang> db.example2.aggregate( { $addFields: { firstElem: { $first: "$x" } } } )
uncaught exception: Error: command failed: {
"ok" : 0,
"errmsg" : "$first's argument must be an array, but is double",
"code" : 28689,
"codeName" : "Location28689"
} : 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:1046:12
@(shell):1:1
wang>
使用以下文档创建样本收集运行日志:
db.runninglog.insertMany([
{ "_id" : 1, "team" : "Anteater", log: [ { run: 1, distance: 8 }, { run2: 2, distance: 7.5 }, { run: 3, distance: 9.2 } ] },
{ "_id" : 2, "team" : "Bears", log: [ { run: 1, distance: 18 }, { run2: 2, distance: 17 }, { run: 3, distance: 16 } ] },
{ "_id" : 3, "team" : "Cobras", log: [ { run: 1, distance: 2 } ] }
])
以下聚合使用日志数组上的first和last运算符检索第一次运行和最后一次运行的信息:
wang> db.runninglog.aggregate([ { $addFields: { firstrun: { $first: "$log" }, lastrun: { $last: "$log" } } } ]).pretty()
{
"_id" : 1,
"team" : "Anteater",
"log" : [
{
"run" : 1,
"distance" : 8
},
{
"run2" : 2,
"distance" : 7.5
},
{
"run" : 3,
"distance" : 9.2
}
],
"firstrun" : {
"run" : 1,
"distance" : 8
},
"lastrun" : {
"run" : 3,
"distance" : 9.2
}
}
{
"_id" : 2,
"team" : "Bears",
"log" : [
{
"run" : 1,
"distance" : 18
},
{
"run2" : 2,
"distance" : 17
},
{
"run" : 3,
"distance" : 16
}
],
"firstrun" : {
"run" : 1,
"distance" : 18
},
"lastrun" : {
"run" : 3,
"distance" : 16
}
}
{
"_id" : 3,
"team" : "Cobras",
"log" : [
{
"run" : 1,
"distance" : 2
}
],
"firstrun" : {
"run" : 1,
"distance" : 2
},
"lastrun" : {
"run" : 1,
"distance" : 2
}
}
wang>
要计算第一个距离和最后一个距离之间的变化,以下操作使用cond和size运算符来计算两个距离的差值(即subtract),如果日志数组中有两个或多个元素:
db.runninglog.aggregate([
{ $addFields: { firstrun: { $first: "$log" }, lastrun: { $last: "$log" } } },
{ $project: { team: 1, progress:
{
$cond: {
if: { $gt: [ { $size:"$log" }, 1 ] } ,
then: { $subtract: [ "$lastrun.distance", "$firstrun.distance"] },
else: "Not enough data." }
}
}}
])
(4). $addToSet
返回所有唯一值的数组,这些唯一是通过group得到的
插入基础数据:
db.addtoset_sales.insert([
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:12:00Z") }
])
以下操作按日期和年份分组,使用addToSet累加器来计算每个组出售的唯一商品的列表:
db.addtoset_sales.aggregate(
[
{
$group:
{
_id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
itemsSold: { $addToSet: "$item" }
}
}
]
)
(5). $push
返回该分组所有值的数组(不会去除重复记录)
插入基础数据:
db.push_sales.insert([
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") },
{ "_id" : 6, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-15T12:05:10Z") },
{ "_id" : 7, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T14:12:12Z") }
])
按日期字段的日期和年份分组文档,以下操作使用push累加器计算每个组的项目和销售数量列表:
db.push_sales.aggregate(
[
{
$group:
{
_id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
itemsSold: { $push: { item: "$item", quantity: "$quantity" } }
}
}
]
).pretty()
结果如下:
{
"_id" : {
"day" : 34,
"year" : 2014
},
"itemsSold" : [
{
"item" : "jkl",
"quantity" : 1
},
{
"item" : "xyz",
"quantity" : 5
}
]
}
{
"_id" : {
"day" : 1,
"year" : 2014
},
"itemsSold" : [
{
"item" : "abc",
"quantity" : 2
}
]
}
{
"_id" : {
"day" : 46,
"year" : 2014
},
"itemsSold" : [
{
"item" : "abc",
"quantity" : 10
},
{
"item" : "xyz",
"quantity" : 10
},
{
"item" : "xyz",
"quantity" : 5
},
{
"item" : "xyz",
"quantity" : 10
}
]
}
wang>