MongoDB - Group and Find Top N with condition

Question

考虑这个测试集合，其中机场由AirportID标识:

[
{ AirportID:"1001", delayMinutes :"15.0" },
{ AirportID:"1004", delayMinutes :"3.0" },
{ AirportID:"1001", delayMinutes :"20.0" },
{ AirportID:"1002", delayMinutes :"6.0" },
{ AirportID:"1002", delayMinutes :"25.0" },
{ AirportID:"1004", delayMinutes :"55.0" },
]

我想把它们组合在一起，列出列表中的前2个，条件是$delayMinutes大于"10.0"。我想把它们组合在一起，列出列表中的前2个。

我试过的代码:

db.test.aggregate([
{
$group: {
_id: "$AirportID",
delayMinutes: {
$sum: {
"$toDouble": "$delayMinutes"
}
}
}
},
{
$sort: {
delayMinutes: -1
}
},
{
$limit: 2
}
])

Answer 1

随着需求的明确

首先需要过滤文档delayMinutes场大于($gt) 10.

请确保在比较之前将delayMinutes转换为double。

db.collection.aggregate([
{
$match: {
$expr: {
$gt: [
{
"$toDouble": "$delayMinutes"
},
10
]
}
}
},
{
$group: {
_id: "$AirportID",
delayMinutes: {
$sum: {
"$toDouble": "$delayMinutes"
}
}
}
},
{
$sort: {
delayMinutes: -1
}
},
{
$limit: 2
}
])

Mongo Playground示例

---

您可以将delayMinutes字段更新为$set阶段的double。因为在$match和$group阶段是冗余的。

db.collection.aggregate([
{
$set: {
delayMinutes: {
"$toDouble": "$delayMinutes"
}
}
},
{
$match: {
$expr: {
$gt: [
"$delayMinutes",
10
]
}
}
},
{
$group: {
_id: "$AirportID",
delayMinutes: {
$sum: "$delayMinutes"
}
}
},
{
$sort: {
delayMinutes: -1
}
},
{
$limit: 2
}
])

Mongo Playground示例(含$set)