CODEWITHSUNDEEP
mongodbdatetimeaggregation-frameworkdatetime-format
user3799658
user3799658

Reputation: 389

Find oldest/youngest post in mongodb collection

I have a mongodb collection with many fields. One field is 'date_time', which is in an ISO datetime format, Ex: ISODate("2014-06-11T19:16:46Z"), and another field is 'name'.

Given a name, how do I find out the oldest/youngest post in the collection?

Ex: If there are two posts in the collection 'data' : 

[{'name' : 'John', 'date_time' : ISODate("2014-06-11T19:16:46Z")},
 {'name' : 'John', 'date_time' : ISODate("2015-06-11T19:16:46Z")}]

Given the name 'John' how do I find out the oldest post in the collection i.e., the one with ISODate("2014-06-11T19:16:46Z")? Similarly for the youngest post.

Upvotes: 28

Views: 26186

Answers (3)

user11668832
user11668832

Reputation: 29

db.t.find().sort({ "date_time" : 1 }).limit(1).pretty()

Upvotes: 1

BatScream
BatScream

Reputation: 19700

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")
        }
}

Upvotes: 6

wdberkeley
wdberkeley

Reputation: 11671

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.

Upvotes: 47

Related Questions