CODEWITHSUNDEEP
c#mongodb
selams
selams

Reputation: 105

MongoDB Delete records with date field less than days from another field

I have a collection which stores application logs in documents:

{
"_id" : ObjectId("5d92d5d01518a620ccaf015c"),
"MessageType" : "ApplocationLog",
"FireAndForget" : true,
"CreatedTimestamp" : ISODate("2019-10-01T06:28:00.198+01:00"),
"OriginReference" : "OriginReference",
"OriginName" : "OriginName",
"LogMessage" : "The log message",
"RetentionDays" : 1,
"LogSeverity" : "Error",
"ApplicationUserContextId" : "User1",
"ApplicationUserContextName" : "User1Name",
"Exception" : null,
"ErrorRelatedObjects" : null
}

I need to run a query to delete all records from the collection where the Current System Date minus 'RetentionDays' is greater than the 'CreatedTimestamp' of the document, i.e. remove expired log documents.

I am using MongoDB C# driver in my application and trying to figure the best way to do this. I have considered adding an expiry date when inserting the document which in hindsight might make things easier? But I still need to deal with the existing records using a query.

I have not got very far but started trying to work out the criteria for finding all records that have expired:

db.ApplicationLog.find({
"CreatedTimestamp": {
    $gte: 
        new Date(new Date().setDate(new Date().getDate()-1))
}

})

If I could subtract the 'RetentionDays' instead of the hardcoded 1 in the above maybe I could then add this to the criteria of a delete query? I'm new to MongoDB so struggling a bit with this.

Upvotes: 1

Views: 701

Answers (2)

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

One solution is to aggregate on ApplicationLog and filter the documents which need to be retained. Finally, replace the existing data with the output of aggregation.

For example:

db.ApplicationLog.aggregate([
    {
        $match:{
            $expr:{
                $gt:[
                    "$CreatedTimestamp",
                    {
                        $toDate:{
                            $subtract:[
                                new Date(),
                                {
                                    $multiply:[
                                        "$RetentionDays",
                                        86400000
                                    ]
                                }
                            ]
                        }
                    }
                ]
            }
        }
    },
    {
        $out:"ApplicationLog"
    }
])

Upvotes: 0

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

The Bad News is you cant really do that in one query.

You will have to first "calculate" the new field and then use the documents that matched and call a remove operator, this can be done fairly easily using an aggregation by subtracting from the date and matching documents with a negative result.

However if i may suggest a solution i personally find better: Use TTL index's this way each document will expire when its time for it to do so instead of you having to maintain a cron'd deletion query.

Here is an example for a "dynamic" TTL index that you can use as reference.

Upvotes: 1

Related Questions