MongoDB在双嵌套数组中获取最大日期 [英] MongoDB get max date inside double nested array

问题描述

假设我有以下文档:

[
  {
    "callId": "17dac51e-125e-499e-9064-f20bd3b1a9d8",
    "caller": {
      "firstName": "Test",
      "lastName": "Testing",
      "phoneNumber": "1231231234"
    },
    "inquiries": [
      {
        "inquiryId": "b0d14381-ce75-49aa-a66a-c36ae20b72a8",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-01T06:00:00.000Z",
            "status": "routed"
          },
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "ended"
          }
        ]
      },
      {
        "inquiryId": "9d743be9-7613-46d7-8f9b-a04b4b899b56",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-01T06:00:00.000Z",
            "status": "routed"
          },
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "ended"
          }
        ]
      }
    ]
  }
]

我想获得inquiries.routeHistory.routeDate等于routeHistory中$ max routeDate值的结果.我希望我的结果如下所示:

I want to get results where inquiries.routeHistory.routeDate is equal to the $max routeDate value in routeHistory. I would expect my results to look like the following:

[
  {
    "callId": "17dac51e-125e-499e-9064-f20bd3b1a9d8",
    "caller": {
      "firstName": "Test",
      "lastName": "Testing",
      "phoneNumber": "1231231234"
    },
    "inquiries": [
      {
        "inquiryId": "b0d14381-ce75-49aa-a66a-c36ae20b72a8",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "ended"
          }
        ]
      },
      {
        "inquiryId": "9d743be9-7613-46d7-8f9b-a04b4b899b56",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "ended"
          }
        ]
      }
    ]
  }
]

是否有一个简单的方法可以在单个聚合中执行此操作，以便可以应用其他$ match条件?一个警告是我只能使用DocumentDB支持的运算符: https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html

Is there a clean way to do this in a single aggregate, so that additional $match criteria can be applied? One caveat is that I can only use operators supported by DocumentDB: https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html

我尝试了以下代码，但无济于事:

I have tried the following code, but to no avail:

{
    $addFields: {
      maxDate: {
        $max: '$inquiries.routeHistory.routeDate',
    },
  },
},
{
    $addFields: {
      routeHistory: [
      {
          $arrayElemAt: [
          {
              $filter: {
                input: '$inquiries.routeHistory',
                cond: {
                  $eq: ['$maxDate', '$$this.routeDate'
                ],
              },
            },
          },
          0,
        ],
      },
    ],
  },
}

推荐答案

您必须使用进行比较$ max 日期:

You have to use $map to scan outer array and to $filter to compare inner array's elements against $max date:

db.collection.aggregate([
    {
        $addFields: {
            inquiries: {
                $map: {
                    input: "$inquiries",
                    as: "inquiry",
                    in: {
                        inquiryId: "$$inquiry.inquiryId",
                        routeHistory: {
                            $filter: {
                                input: "$$inquiry.routeHistory",
                                cond: {
                                    $eq: [ { $max: "$$inquiry.routeHistory.routeDate" }, "$$this.routeDate" ]
                                }
                            }
                        }
                    }
                }
            }
        }
    }
])

蒙戈游乐场

通过查看您的链接，我注意到不支持 $ map ，您可以使用以下组合作为解决方法:

by looking at your link I've noticed that $map is not supported, you can use below combination as a workaround:

db.collection.aggregate([
    {
        $unwind: "$inquiries"
    },
    {
        $addFields: {
            "inquiries.routeHistory": {
                $filter: {
                    input: "$inquiries.routeHistory",
                    cond: {
                        $eq: [ { $max: "$inquiries.routeHistory.routeDate" }, "$$this.routeDate" ]
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: "$_id",
            callId: { $first: "$callId" },
            caller: { $first: "$caller" },
            inquiries: { $push: "$inquiries" }
        }
    }
])

蒙戈游乐场(2)

这篇关于MongoDB在双嵌套数组中获取最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！