Auto-Delete Certain type of Document in Mongodb in collection

Question

I have a collection called PeopleDocument. This collection contains three different types of files: IDCardCopy, taxCopy, PermitCopy. Users can upload any of these files. I want to autodelete IDCardCopy one year after it was uploaded. I am looking at MongoDB TTL, however I have some questions:

db.PeopleDocument.createIndex( { "lastModifiedDate": 1 }, { expireAfterSeconds: 31622400} )

If I create an index like the one above, I think it will delete all files in PeopleDocument after 1 year, is it possible to only delete IDCardCopy?

More detail:

This is a C# code I use to insert Document:

var collInternDocuments = _database.GetCollection<BsonDocument>("PPLDocuments");



var doc = new BsonDocument{
                             {"Documentid", strDocumentID},
                             { "FileType", typeOfDocument},                                        
                             { "FileContent", file.ContentType},
                             { "FileName", file.FileName},
                             { "UploadTime", DateTime.Now},
                             { "UploadedBy", uploadedBy},
                             { "GridFSFileID", strGridFSFileID}
                           };
    

collInternDocuments.UpdateOne( Builders<BsonDocument>.Filter.Eq("_id", internUserID), Builders<BsonDocument>.Update.AddToSet("Documents", doc));

This is how I created Index:

db.PeopleDocument.createIndex( {UploadTime:1}, 
                               {
                                  expireAfterSeconds:900,
                                  partialFilterExpression:{
                                                            FileType:{$eq:"IDCardCopy"}
                                                            }
                                })

This is the results for db.PeopleDocument.getIndexes():

 {
    "v":2,
    "key":{
           "_id":1
           },
     "name" :"_id_",
      "ns" : "people.PeopleDocument"
},
{ 

 "v":2,
    "key":{
           "UploadTime":1
           },
     "name" :"UploadTime_1",
      "ns" : "people.PeopleDocument",
      "expireAfterSeconds":900,
      "partialFilterExpression":{
               "FileType": { "$eq":"IDCardCopy"
                 }
           }
 }

This didn't delete the file after 900 sec, could this be a date issue?

`db.PeopleDocument.createIndex( { "fileType": "IDCard" }, { expireAfterSeconds: 31622400} ) ` Do you mean like that? — Suji, Dec 06 '20 at 04:09
you could try ttl together with partial index: https://stackoverflow.com/a/50272567/4368485 — Dĵ ΝιΓΞΗΛψΚ, Dec 06 '20 at 04:13
@ĐĵΝιΓΞΗΛψΚ `db.PeopleDocument.createIndex({"uploadTime":1}, {exprieAfterSeconds:900, partialFilterExpression:{"FileType":{$eq:"IDCardCopy"}}})` That's what I did to create the Index. Just for testing I put 900s (15 Min), I can still see IDCardCopy that were uploaded before 15min. Am I doing something wrong? — Suji, Dec 06 '20 at 06:09

score 1 · Answer 1 · answered Dec 06 '20 at 13:50

1

this works for me. after 10secods (not exactly due to the deletion thread running every 60seconds), the IDCardCopy document is gone.

db.PeopleDocument.createIndex(
    { "uploadTime": 1 },
    {
        expireAfterSeconds: 10,
        partialFilterExpression: { "FileType": { $eq: "IDCardCopy" } }
    }
)

db.PeopleDocument.insertMany(
    [
        {
            uploadTime: new ISODate(),
            FileType: "IDCardCopy"
        },
        {
            uploadTime: new ISODate(),
            FileType: "taxCopy"
        },
    ]
)

make sure you're setting the uploadTime field to the correct UTC now time in your application.

answered Dec 06 '20 at 13:50

Dĵ ΝιΓΞΗΛψΚ

5,068
3
13
26

I added more details to my question, I did exactly as you did. Is there an issue with my Date for `UploadTime`? – Suji Dec 06 '20 at 14:39
i think your uploadTime may be stored in the db as a string. and the ttl index is looking for an ISODate. i think you need to set the date in your bsondocument with something like: `{ "UploadTime", DateTime.UtcNow.ToString("O")}` – Dĵ ΝιΓΞΗΛψΚ Dec 06 '20 at 14:57
I'm using Mongo Management Studio to view the documents. For UploadTime it has the following: `"UploadTime":"ISODate(2020-12-06T05:41:32.698Z)"`. I think its in the correct format – Suji Dec 06 '20 at 15:38
try doing DateTime.UtcNow. it might be the timezone offset not causing it to get deleted. also can you try adding a sample document to the collection with `new ISODate()` using mongo management studio. see if that document gets deleted correctly. is so, then it's definitely something wrong with how you insert documents with bsondocument. – Dĵ ΝιΓΞΗΛψΚ Dec 06 '20 at 15:40
I tried that, it didn't work. I am using Mongo3.4, I don't think that's an issue. – Suji Dec 06 '20 at 16:44

score 0 · Answer 2 · answered Dec 05 '20 at 22:38

0

I think you should add in PeopleDocument the column expiresAt and fill this field on creating concrete record depended on your file type. And then simply create index like that:

db.PeopleDocument.createIndex( { "expiresAt": 1 }, { expireAfterSeconds: 0} )

answered Dec 05 '20 at 22:38

godot

3,422
6
25
42

This didn't work. In `PeopleDocument`, only IDCardCopy has `expiresAt`, `taxCopy` and `PermitCopy` don't have it, I think this is causing an error. – Suji Dec 06 '20 at 04:08

score 0 · Accepted Answer · answered Dec 11 '20 at 03:08

0

I solved this by creating a C# console app that will delete documents created one year ago, I created a Task Scheduler that will run everyday to run this app.

answered Dec 11 '20 at 03:08

Suji

767
3
12
28

Auto-Delete Certain type of Document in Mongodb in collection

3 Answers3