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
You should make a group and use $last operator.
Try running this aggragate query :
That should do the trick !
since mongoDB version 5.2, one option is to use
$sortArray
:See How it works on the mongoDB playground