skip to Main Content

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.

2

Answers


  1. 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 !

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search