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' : 'xyz@gmail.com'
'from_email' : 'abc@gmail.com'
'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.

please share the schema type for scheduledDate. I tried to create type: Date, and default: new Date(). So if entry is zero then it will be deleted under 5 minutes and if entry have date then it won't be delete. But after inserting i check that instead of 0 time format save with ISO (00:00:00-000z) like this. I want to initial store 0 (number format) and then when condition arise it will save current ISO date timestamp. So that after specific minute only 0 oen will remove — Aks, Jan 27 '20 at 07:39

score 27 · Accepted Answer · answered May 10 '18 at 12:04

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/

score 14 · Answer 2 · answered Feb 03 '16 at 14:01

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.

So you would add the index needed to expire it like this [(Docs)](https://docs.mongodb.com/manual/tutorial/expire-data/#expire-documents-at-a-specific-clock-time)? `db.emails.createIndex( { "expires": 1 }, { expireAfterSeconds: 0 } )` — luckydonald, Nov 03 '16 at 14:02
That looks correct to me: In your case, the document will be deleted when the current date is the value of the `expires` field (give or take a minute). — RickN, Nov 04 '16 at 11:38

Alex · Answer 3 · 2016-02-03T13:39:23.560

2

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

edited Feb 03 '16 at 13:39

answered Feb 03 '16 at 13:36

Alex

21,273
10
61
73

but if there is another TTL index set for 'createdDate', then also it wont get expired? – kadamb Feb 03 '16 at 13:39
You only want to add a TTL index on `scheduledDate` with the idea that you only should remove records after they have been sent successfully, otherwise have a look at RikkusRikkus solution, – Alex Feb 03 '16 at 14:11

score -4 · Answer 4 · answered Jan 21 '19 at 17:49

const mongoose = require("mongoose");
const Schema = mongoose.Schema;

let PassWordReSate = new Schema(
  {
    hex: String,
    email: String,
  },
  {
    collection: "PassWordReSate",
    timestamps: true,
  }
);
PassWordReSate.path("createdAt").index({ expires: 6 });
module.exports = mongoose.model("PassWordReSate", PassWordReSate);

Create a conditional TTL in mongo

4 Answers4

A default value

A different expire date for your scheduled tasks

No expiry at all

Linked