Get item value with largest date from array

user_15234297 0
Viewed 15

In my database I'm having datasets of the form

{
    "_id" : ObjectId("60994d48b223f7553c082ab2"),
    "name" : "name",
    "stateHistory" : [
        {
            "state" : "scanning",
            "date" : {
                "$date" : 1620659528239
            }
        }
    ],
    "__v" : 0
}

When returning the list of datasets I want to add a computed field maxState that displays the value of the oldest state entry from the stateHistory.

I successfully managed to get the oldest date entry from the stateHistory by using something like:

db.datasets.aggregate( [
   {
     $addFields: {
            maxState: {
            $arrayElemAt: [
                "$stateHistory",
                {
                    $indexOfArray: [
                        "$stateHistory.date",
                        { $max: "$stateHistory.date" },
                    ],
                },
            ],
        }
    }
   }
])

However, this would return the whole object with the largest age

{
    "state" : "scanning",
    "date" : {
        "$date" : 1620659528239
    }
}

instead of its state value (i.e. "scanning")

I have searched the whole mongodb documentation, but I can't find any method to access a specific value from an object key.

What would be the best approach to solve my issue?

aggregation-framework mongodb-query mongodb
1 Answers

Just need to tell $arrayElemAt that you want "$stateHistory.state"'s specific element,

db.datasets.aggregate([
  {
    $addFields: {
      maxState: {
        $arrayElemAt: [
          "$stateHistory.state", // change here
          {
            $indexOfArray: [
              "$stateHistory.date",
              {
                $max: "$stateHistory.date"
              }
            ]
          }
        ]
      }
    }
  }
])

Playground

user_89871280
Related