按数组中与filter匹配的最后一个元素中的值对文档排序.Mongodb

Question

我有一个evaluationGroups的集合使用以下文档结构:

{
"_id" : ObjectId("60073749694fd4d81e4d677d"),
"AlertSettingId" : ObjectId("5ffddaaa0b1d2c30b191599a"),
"CurrentStatus" : "success",
"Evaluations" : [ 
{
"EvaluatedAt" : ISODate("2021-01-19T19:47:18.850Z"),
"ReferenceValue" : 1.0,
"Status" : "success"
}, 
{
"EvaluatedAt" : ISODate("2021-01-19T19:52:16.423Z"),
"ReferenceValue" : 1.0,
"Status" : "triggered"
}, 
{
"EvaluatedAt" : ISODate("2021-01-19T21:47:16.400Z"),
"ReferenceValue" : 1.0,
"Status" : "success"
}
]
}
{
"_id" : ObjectId("60085ec60a264ce3829a6335"),
"AlertSettingId" : ObjectId("5ffddaaa0b1d2c30b191599a"),
"CurrentStatus" : "triggered",
"Evaluations" : [ 
{
"EvaluatedAt" : ISODate("2021-01-20T18:03:01.040Z"),
"ReferenceValue" : 1.0,
"Status" : "noDataFound"
}, 
{
"EvaluatedAt" : ISODate("2021-01-20T22:04:43.983Z"),
"ReferenceValue" : 1.0,
"Status" : "triggered"
}, 
{
"EvaluatedAt" : ISODate("2021-01-20T22:39:43.978Z"),
"ReferenceValue" : 1.0,
"Status" : "triggered"
}, 
]
}
{
"_id" : ObjectId("60099092f7386972de3e8a05"),
"AlertSettingId" : ObjectId("5ffddaaa0b1d2c30b191599a"),
"CurrentStatus" : "success",
"Evaluations" : [ 
{
"EvaluatedAt" : ISODate("2021-01-21T14:32:48.697Z"),
"ReferenceValue" : 1.0,
"Status" : "noDataFound"
}, 
{
"EvaluatedAt" : ISODate("2021-01-21T14:37:44.929Z"),
"ReferenceValue" : 1.0,
"Status" : "triggered"
}, 
{
"EvaluatedAt" : ISODate("2021-01-21T14:42:44.928Z"),
"ReferenceValue" : 1.0,
"Status" : "triggered"
}, 
{
"EvaluatedAt" : ISODate("2021-01-21T15:17:46.052Z"),
"ReferenceValue" : 1.0,
"Status" : "success"
}
]
}

我需要做的是对排序所有评估组由最新的评估中的评估(使用EvaluatedAt属性作为排序)，但评估也有状态触发.

所以，总结一下，我必须按最近触发的评估日期对组进行排序.

我正在看的问题:Mongodb:按值排序文档在数组的最后一个元素

我喜欢这个如何按最后一项排序的响应，(因为在我的情况下，最近的评估是在数组的末尾):

db.collection.aggregate([
{
$addFields: {
lastSent: {
$let: {
vars: {
last: {
$arrayElemAt: [ "$messages", -1 ]
}
},
in: "$$last.commData.sent.dateTime"
}
}
}
},
{ $sort: { lastSent: 1 } },
{ $project: { lastSent: 0 } }
])

但是我还需要根据状态"触发"过滤评估。

如何使用MongoDBaggregate实现此目标?查询?

Answer 1

您可以使用$filter运算符，

• $filter在Status
基础上对Evaluations阵列进行滤波
• $max获取最新的EvaluatedAt表单过滤结果

db.collection.aggregate([
{
$addFields: {
lastSent: {
$let: {
vars: {
filtered: {
$filter: {
input: "$Evaluations",
cond: { $eq: ["$$this.Status", "triggered"] }
}
}
},
in: { $max: "$$filtered.EvaluatedAt" }
}
}
}
},
{ $sort: { lastSent: 1 } },
{ $project: { lastSent: 0 } }
])

游乐场