CODEWITHSUNDEEP
mongodbmongodb-query
stevo81989
stevo81989

Reputation: 170

Find where field is older than 3 days

I have a mongodb database with a data structure like this:

{ 
"_id" : "123456":, 
"Dates" : ["2014-02-05 09:00:15 PM", "2014-02-06 09:00:15 PM", "2014-02-07 09:00:15 PM"],
}

There are other stuff too but what I would like to do is pull data where Dates{$slice: -1} is older than 3 days. I have a query like this but it doesnt work:

db.posts.find({}, {RecoveryPoints:{$slice: -1} $lt: ISODate("2014-02-09 00:00:00 AM")})

I am beginning to think is not possible but I figured I would come here first. My guess is that I'll have to do this logic within my code.

Edit: I wanted to mention too that my goal is to return the entire record and not just the date that mathes it

Upvotes: 3

Views: 5212

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

Your query has all the elements on the projection side and not the query side. Since you do not want to change the appearance of the document, then thee is no need to project.

This is actually a simple comparison as if you are always looking at the first element then you can use the dot "." notation form to look for it:

> db.dates.find({ "Dates.0": {$lt: ISODate("2014-02-09 00:00:00 AM") } }).pretty()
{
    "_id" : "123456",
    "Dates" : [
            ISODate("2014-02-05T09:00:15Z"),
            ISODate("2014-02-06T09:00:15Z"),
            ISODate("2014-02-07T09:00:15Z")
    ]
}
> db.dates.find({ "Dates.0": {$lt: ISODate("2014-02-05 00:00:00 AM") } }).pretty()
>

So the dot "." notation works at index value in the case of arrays, and to get other elements just change the position:

> db.dates.find({ "Dates.1": {$lt: ISODate("2014-02-07 00:00:00 AM") } }).pretty()
{
    "_id" : "123456",
    "Dates" : [
            ISODate("2014-02-05T09:00:15Z"),
            ISODate("2014-02-06T09:00:15Z"),
            ISODate("2014-02-07T09:00:15Z")
    ]
}
> db.dates.find({ "Dates.3": {$lt: ISODate("2014-02-07 00:00:00 AM") } }).pretty()
>
> db.dates.find({ "Dates.2": {$lt: ISODate("2014-02-08 00:00:00 AM") } }).pretty()
{
    "_id" : "123456",
    "Dates" : [
            ISODate("2014-02-05T09:00:15Z"),
            ISODate("2014-02-06T09:00:15Z"),
            ISODate("2014-02-07T09:00:15Z")
    ]
}

Note that this only makes sense if you are sorting the array on changes and can otherwise be sure that the first (or position you are querying ) element is going to be the one you want to compare. If you need to otherwise test against all elements, then use the $elemMatch operator in the query instead.

The only reason you wouldn't get the result you want with this query is where your dates are strings. If so, change them, and change whatever code is causing them to be saved that way.

Upvotes: 3

Related Questions