I'm trying to find many records in the same query, the records are of this type
{
"key": 2,
time: 3
},
{
"key": 2,
time: 5
},
{
"key": 3,
time: 4
}
I'd like to retrive for example the records with key inside [2,3] and with the relative maximum amount of time, so in this case recrods with { "key": 2, time: 5 } and { "key": 3, time: 4 } should be returned.
So far i got only how to retrive the records with key inside in a proveded array but i'm not understanding how to get for each key in the array the record with the latest time, i'd like to not use the where operator.
Here is a playground with the provided scenario https://mongoplayground.net/p/5sWnn8_HnqK
You can use aggregate function to find max element for each key. Example: https://mongoplayground.net/p/P9JSuvTHdZa
db.collection.aggregate([
{
$match: {
key: {
$in: [
2,
3
]
}
}
},
{
$group: {
_id: "$key",
time: {
$max: "$time"
}
}
}
])
Edited: as in comment suggested
The easiest way to achieve this is by matching the documents, sorting them descendingly and then choose the "first" document for each key, like so:
db.collection.aggregate([
{
$match: {
key: {
$in: [
1,
2,
3
]
}
}
},
{
$sort: {
time: -1
}
},
{
$group: {
_id: "$key",
first: {
$first: "$$ROOT"
}
}
}
])