Get documents which expired before today in MongoDB

Question

I am using MongoDB 2.6 and I'm making a query that filter the documents by expired date. I want to know which documents expired before today. My persisted data represent publications.

I've made some queries but it doesn't return documents. I know there are many documents who satisfy this condition.

I tried two query but no one works:

1.

{
"domain.ApplicationCase.fields.ExpireDate": { $gte : {
 $currentDate: {
    lastModified: true,
    "cancellation.date": { $type: "timestamp" }
 }}}    
}

2.

{
"domain.ApplicationCase.fields.ExpireDate": { $gte : new Date()}    
}

Part of document records:

{
    "_id" : "1234546",
    "fields" : {
        "Orchestration" : "default",
        "Segmentation" : "PFI",
        "MatchKey" : "1",
        "UserID" : "001"
    },
    "domain" : {
        "ApplicationCase" : {
            "_id" : null,
            "fields" : {
                "ExpireDate" : "2015-11-13T13:47:26Z",
....

What's wrong?

Answer 1

If you want to get records which are expired before today then you should use $lte instead of $gte:

db.myCollection.find({
  "domain.ApplicationCase.fields.ExpireDate": { $lte : new Date()}    
})

Update:

So the core problem with your documents is that, the value of domain.ApplicationCase.fields.ExpireDate are not in the date format instead they are in simple String format.

So you first need to convert them to the date in order for the query to work since you are comparing an String with the Date.

Probably, you can use a code like this to convert the string to the date:

db.myCollection.find({
    "domain.ApplicationCase.fields.ExpireDate": {$exists: true}
}).snapshot().forEach(function(record) {
    var stringValue = record.domain.ApplicationCase.fields.ExpireDate;

    db.myCollection.update({_id: record._id}, {$set: {
          "domain.ApplicationCase.fields.ExpireDate": ISODate(stringValue)
    }});
})