Question 115501 in Mongodb Question posted in
The official documentation can be found here.

Adding the same field in the condition of mongodb find query

I have a mongodb collection testdata which contains a field called insertTime. We have a requirement to delete data older than 60 days. So, previously to delete older data from the collections for all documents which are older than 60 days -> I would use the following logic of first finding the deletion date and then comparing it against the updateTime:

var date = new Date();
var daysToDeletion = 60;
var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
deletionDate = deletionDate.toISOString()
printjson(insertDate);

db.testdata.find({"insertTime":{ $lt: deletionDate}})

However now, I would like to delete the data which is older than the alive time of the record. Alive time would be calculated as the insertTime + endTime(60 days). Now the documents older than this alive time – 60 days should be deleted. Can someone help me achieve this?

All i can think of is something like this but i don’t think the command is right:

db.testdata.find({"insertTime"+endTime:{ $lt: deletionDate}})

How do i achieve this in mongodb find command query? Please can insights be provided on this.
Thanks a ton.

I have added all the details above and what i would like to achieve.

EDIT: using AWS documentDB 4.0.0

Tags:

2

Answers


  1. 0

    You can use $dateAdd(available from MongoDB v5.0+) to compute the alive date and compare to $$NOW

    db.collection.find({
  $expr: {
    $lt: [
      {
        "$dateAdd": {
          "startDate": "$insertTime",
          "unit": "day",
          "amount": 60
        }
      },
      "$$NOW"
    ]
  }
})

    Mongo Playground

    Here is a version for MongoDB / AWS DocumentDB(v4.0) that OP is using. The idea is to compute 60 days late by adding 60 day * 24 hours * 60 min * 60 sec * 1000 ms = 5184000000.

    db.collection.aggregate([
  {
    "$addFields": {
      flag: {
        $lt: [
          {
            $add: [
              "$insertTime",
              5184000000
            ]
          },
          "$$NOW"
        ]
      }
    }
  },
  {
    "$match": {
      flag: true
    }
  },
  {
    "$unset": "flag"
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    I think this $expr can help you:

    var date = new Date();
var daysToDeletion = 60;
var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));

db.testdata.deleteMany({
    $expr: {
        $lt: [{ $add: ["$insertTime", "$endTime"] }, deletionDate]
    }
});

    Edit:
    With compatible solution with documentdb:

    var date = new Date();
var daysToDeletion = 60;
var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));

db.testdata.find(
    {
        $lt: {
            $add: [
                "$insertTime",
                { $multiply: [daysToDeletion, 24 * 60 * 60 * 1000] }
            ]
        },
        deletionDate
    }
);

    Edit 2: The solution above wasn’t working properly.

    This one a little bit tricky but it works

    const date = new Date();
const daysToDeletion = 60;
const deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
const aliveTime = { $add: ["$insertTime", "$endTime"] };

db.testdata.deleteMany({
  $and: [
    { aliveTime: { $lt: deletionDate } },
    { insertTime: { $lt: deletionDate } }
  ]
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search