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 the name and date_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 desired name(s).

  • $sort by date_time in ascending order.
  • $group by the name 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.