CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-framework
Michael Kitchell
Michael Kitchell

Reputation: 129

MongoDB get results where date is equal to max date

Assume I have the following document:

[
  {
    "callId": "17dac51e-125e-499e-9064-f20bd3b1a9d8",
    "caller": {
      "firstName": "Test",
      "lastName": "Testing",
      "phoneNumber": "1231231234"
    },
    "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"
      }
    ]
  }
]

I want to get results where 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"
    },
    "routeHistory": [
      {
        "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
        "routeDate": "2020-01-03T06:00:00.000Z",
        "status": "ended"
      }
    ]
  }
]

Is there a clean way to do this in a single aggregate, so that additional $match criteria can be applied?

Upvotes: 1

Views: 2263

Answers (1)

mickl
mickl

Reputation: 49985

You can use $let to define temporary variable being $max date and the use $filter along with $arrayElemAt to get first matching element:

db.collection.aggregate([
    {
        $addFields: {
            routeHistory: {
                $let: {
                    vars: {
                        maxDate: { $max: "$routeHistory.routeDate" }
                    },
                    in: {
                        $arrayElemAt: [
                            { $filter: { input: "$routeHistory", cond: { $eq: [ "$$maxDate", "$$this.routeDate" ] } } },
                            0
                        ]
                    }
                }
            }
        }
    }
])

Mongo Playground

EDIT:

version without $let:

db.collection.aggregate([
    {
        $addFields: {
            maxDate: {
                $max: "$routeHistory.routeDate"
            }
        }
    },
    {
        $addFields: {
            routeHistory: {
                $arrayElemAt: [
                    {
                        $filter: { input: "$routeHistory", cond: { $eq: [ "$$maxDate", "$$this.routeDate" ] } }
                    },
                    0
                    ]
                }
            }
        }
    }
])

Upvotes: 2

Related Questions