Search code examples
mongodbmongodb-querymongodb-indexes

Create a conditional TTL in mongo

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.

Solution

  • 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/