My Schema looks something like this:
{
id: {
type: String,
required: true,
unique: true,
},
names: [
{
value: { type: String, required: true },
changed_at: { type: Date, index: true, default: Date.now },
},
],
}
With aggregation I want find 3 documents of my collection with the most recent date of names -> changed_at.
I tried something like this:
[
{ $unwind: '$names' },
{ $sort: { 'names.changed_at': -1 } },
{
$group: {
_id: '$_id',
names: { $push: { value: '$names.value', changed_at: '$names.changed_at' } },
id: { $first: '$id' },
},
},
{ $limit: 3 },
{
$lookup: {
from: 'users',
localField: 'id',
foreignField: 'id',
as: 'user',
},
},
{ $unwind: '$user' },
{
$project: {
_id: 0,
old_name: { $first: '$names' },
user: { avatar_url: 1, current_tag: 1 },
},
},
]
But this does not return the 3 most recent updated documents. How can I make up my query?
Query
"names.changed_at"names.changed_at) by -1, its like sorting based on the max member of the arraydb.collection.aggregate([
{
"$sort": {
"names.changed_at": -1
}
},
{
"$limit": 3
}
])
Query2 (alternative solution, like do manually what the above does auto)
=> you have the 3 documents that have the latests dates
(query doesn't return 3 latest global dates, those could be all in 1 document)
I used numbers for simplicity, dates are the same.
db.collection.aggregate([
{
"$set": {
"max-date": {
"$max": "$names.changed_at"
}
}
},
{
"$sort": {
"max-date": -1
}
},
{
"$limit": 3
}
])
If you can send some feedback on perfomance, if you have big collection. Comparing those 2 ways.
Second query can be super fast if you add to your schema, the field "max-date"(with index on it) and update it when you add dates on names (this will make very small index also, and will be fast for sure)