按与过滤器匹配的数组的最后一个元素中的值对文档进行排序.Mongodb [英] Sort documents by value in the last element of an array that matches filter. Mongodb

问题描述

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

I have a collection of evaluationGroups with the following documents structure:

{
    "_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 属性作为排序)，但评估的状态也已触发.

What I need to do is to sort all evaluation groups by the latest evaluation inside Evaluations (using EvaluatedAt property as the sort), but that the evaluation has also status triggered.

因此，总而言之，我必须按最新触发的评估日期对组进行排序.

So, to sum up, I have to sort the groups, by the latest triggered Evaluation date.

我正在查看一个问题:

I was looking at the question: Mongodb: sort documents by value in the last element of an array

我喜欢这种关于如何按最后一项排序的响应，(因为在我的情况下，最新的评估结果位于数组的末尾):

And I liked this response of how to sort by last item, (because latest evaluations are at the end of the array in my case):

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

但我还需要按触发"状态过滤评估.在获得最新版本之前.

But I would need to also filter evaluations by status "triggered" before getting the latest one.

如何使用MongoDB 汇总查询实现这一目标?

How can achieve this using MongoDB aggregate query?

推荐答案

您可以使用 $ filter 运算符

• $ filter 用于在状态
的基础上过滤 Evaluations 数组
• $ max 以获得最新的 EvaluatedAt 表单过滤结果

• $filter to filter Evaluations array on the base of Status
• $max to get latest EvaluatedAt form filtered result

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 } }
])

游乐场

这篇关于按与过滤器匹配的数组的最后一个元素中的值对文档进行排序.Mongodb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！