Query for documents in a mongodb collection where the last element in a nested array has a certain value

Question

I am trying to find a way to query a mongodb collection: all documents whose state value of 'running' is also the latest entry inside the 'history' array should be returned.

I was able to do this in C# with .Max() and .OrderBy() using LINQ, but not in nodeJS.

C# without aggregate:

var filter = Filter.Where(w => w.history.OrderBy(o => o.createdAt).Last().status == "running")

Here are example documents:

{
  _id: 1,
  name: 'name 1',
  history: [
    {
     createdAt: '2024-01-01',
     state: 'initialized'
    },
    {
     createdAt: '2024-01-15',
     state: 'running'
    },
    {
     createdAt: '2024-02-02',
     state: 'stopped'
    }
  ]
},
{
  _id: 2,
  name: 'name 2',
  history: [
    {
     createdAt: '2024-01-01',
     state: 'initialized'
    },
    {
     createdAt: '2024-01-20 01:15:66',
     state: 'running'
    },
    {
     createdAt: '2024-01-21 00:55:59', // this is the LATEST entry, and state=running --> return document
     state: 'running'
    },
    {
     createdAt: '2024-01-21 00:55:58',
     state: 'stopped'
    }
  ]
}

A state can be there more than once, so the history array could contain 'running' 3 times. In that case I would need to return the latest entry (using createdAt). It being the last entry in the array does not mean its createdAt date is the latest date.

Answer 1

since mongoDB version 5.2, one option is to use $sortArray:

db.collection.aggregate([
  {$match: {
      $expr: {$eq: [
          {$getField: {
              input: {$first: {$sortArray: {input: "$history", sortBy: {createdAt: -1}}}},
              field: "state"
          }},
          "running"
      ]}
  }}
])

See How it works on the mongoDB playground

Answer 2

You should make a group and use $last operator.

Try running this aggragate query :

[
  {
    $match: {
      "history.state": "running"
    }
  },
  {
    $unwind: "$history"
  },
  {
    $match: {
      "history.state": "running"
    }
  },
  {
    $sort: {
      "history.createdAt": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      mostRecentRunning: { $last: "$history" }
    }
  }]

That should do the trick !