Create a conditional TTL in mongo

Question

There is a particular task I want to accomplish, but I am not finding any particular way to do that. Let's say I have an app that is used to send mails. I keep a record of these emails in a collection in mongo. And using this app I can send mail right now or can schedule emails for future. The structure of documents in collection is like :

{
'_id' : 123456789,
'to_email' : '[email protected]'
'from_email' : '[email protected]'
'subject': 'some subject'
'type' : '<1 if normal and 2 if scheduled>',
'createdDate' '<date when mail was sent or the request was created>',
'scheduledDate' : '<time for which mail is scheduled>'
.. and many more key-value pairs
}

The scheduledDate field can be zero or any date, depending on if it's scheduled or not. I don't want to keep data which is older than 2 days, so I created a TTL index on 'createdDate', for 2 days. But I also don't want to delete rows or requests which are scheduled for the future. I was looking for some kind of conditional TTL, but couldn't find any such solution.

Is there anything available like conditional TTL or any other way to do it in MongoDB.

I want to create a TTL which work like :

if(requestType!=2 and createdDate < -2days)
delete row;

or is there a way where I can make changes to certain documents using any language so that they don't get expired.

EDIT: I solved this, by using the same values for scheduledDate and createdDate in case of scheduled emails.

Answer 1

As of MongoDB 3.2, it is also possible to add a partial TTL index using a specified filter expression. In case if you need to remove only normal not scheduled emails, you could use the following:

db.email.createIndex( {createdDate: 1}, {
    expireAfterSeconds: 172800, // 2 days
    partialFilterExpression: {
        scheduledDate: 0
    }
});

Note that partialFilterExpression has restrictions on possible filter conditions: https://docs.mongodb.com/manual/core/index-partial/

Answer 2

I would just add another field. It's not that much data:

{
'_id' : 123456789,
'createdDate' '<date when mail was sent or the request was created>',
'scheduledDate' : '<time for which mail is scheduled>'
'expires': '<Date or NULL>'
}

Add a TTL index of 0 seconds to the expires field. Don't add a TTL to the other dates.

The following examples are in Mongoose (an ORM for Node) but the ideas should carry over to whatever framework you're using:

A default value

You could add a default value to the expires field with the value of 'created date + 2 days'.

{
  type: Date,
  default: function() { 
    return new Date(Date.now() + 1000*60*60*24*2);
  }
}

A different expire date for your scheduled tasks

Just set the date explicitly:

myNewDocument.expires = new Date( scheduled + ... );

Or change the function that sets the default value:

function() {
    if(this.get("type") === 2) {
      return scheduled_date_plus_2_days;
    } else {
      return created_date_plus_2_days;
    }
}

No expiry at all

Set the field to NULL:

myNewDocument.expires = null;

---

This way, you have the ability to choose a different expiry for normal emails, important emails, scheduled ones, etc. If you're on time, you can even set the expires field to NULL to cancel the automatic deletion.
The only caveat is that if you change the scheduled time, you'll need to update the expires field.

Answer 3

You can just create a TTL index on scheduledDate, if the value is zero it will not be deleted. According to the documentation, if the indexed field in a document is not a date or an array that holds a date value(s), the document will not expire.

Using Mongo 3.2 I have verified this is indeed the case using this test collection:

db.data.drop()
db.data.save({scheduledDate: 0})
db.data.save({scheduledDate: new Date()})
db.data.save({scheduledDate: new Date()})
db.data.createIndex( { "scheduledDate": 1 }, { expireAfterSeconds:1} )

When using this collection all new Date() entries get removed , whereas the first record with the zero value remains