Find oldest/youngest post in mongodb collection
db.t.find().sort({ "date_time" : 1 }).limit(1).pretty()
You could aggregate it as below:
Create an
index
on thename
anddate_time
fields, so that the$match
and$sort
stage operations may use it.db.t.ensureIndex({"name":1,"date_time":1})
$match
all the records for the desiredname(s)
.$sort
bydate_time
in ascending order.$group
by thename
field. Use the $first operator to get the first record of the group, which will also be the oldest. Use the $last operator to get the last record in the group, which will also be the newest.- To get the entire record use the $$ROOT system variable.
Code:
db.t.aggregate([
{$match:{"name":"John"}},
{$sort:{"date_time":1}},
{$group:{"_id":"$name","oldest":{$first:"$$ROOT"},
"youngest":{$last:"$$ROOT"}}}
])
o/p:
{
"_id" : "John",
"oldest" : {
"_id" : ObjectId("54da62dc7f9ac597d99c182d"),
"name" : "John",
"date_time" : ISODate("2014-06-11T19:16:46Z")
},
"youngest" : {
"_id" : ObjectId("54da62dc7f9ac597d99c182e"),
"name" : "John",
"date_time" : ISODate("2015-06-11T19:16:46Z")
}
}
Oldest:
db.posts.find({ "name" : "John" }).sort({ "date_time" : 1 }).limit(1)
Newest:
db.posts.find({ "name" : "John" }).sort({ "date_time" : -1 }).limit(1)
Index on { "name" : 1, "date_time" : 1 }
to make the queries efficient.