Including an identical field within the parameters of a MongoDB search query

Question

Including an identical field within the parameters of a MongoDB search query

In my mongodb collection testdata, there is a field named insertTime. Our goal is to remove data older than 60 days. Previously, to accomplish this, I would use the logic of 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}})

Now, I want to delete records older than each record's alive time. The alive time is calculated as the sum of insertTime and endTime (60 days). Documents older than this alive time minus 60 days should be deleted. Any ideas on how to achieve this in mongodb find command query?

This is what I have come up with so far, but I am not sure if the syntax is correct:

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

Any insights on how to accomplish this task using the MongoDB find command would be greatly appreciated.

Additional details and goals have been included in the post.

EDIT: Currently using AWS documentDB 4.0.0

javascript mongodb mongodb-query aws-documentdb

Answer 1

Answer №1

Take a look at this $expr for a possible solution:

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]
    }
});

Update: Here's a solution that is compatible 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
    }
);

Update 2: The previous solution was not functioning correctly.

This one is a bit complex, but it gets the job done

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 } }
  ]
});

Answer 2

Take a look at this $expr for a possible solution:

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]
    }
});

Update: Here's a solution that is compatible 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
    }
);

Update 2: The previous solution was not functioning correctly.

This one is a bit complex, but it gets the job done

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 } }
  ]
});

Answer 3

Answer №2

One way to calculate the alive date in MongoDB (v5.0+) is by using the function $dateAdd and then comparing it to $$NOW.

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

Check out the Mongo Playground for this code

For MongoDB / AWS DocumentDB (v4.0), you can achieve the same result by adding 60 days worth of milliseconds (5184000000) to the insert time and then comparing it to $$NOW.

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

Check out the Mongo Playground for this code

Answer 4

One way to calculate the alive date in MongoDB (v5.0+) is by using the function $dateAdd and then comparing it to $$NOW.

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

Check out the Mongo Playground for this code

For MongoDB / AWS DocumentDB (v4.0), you can achieve the same result by adding 60 days worth of milliseconds (5184000000) to the insert time and then comparing it to $$NOW.

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

Check out the Mongo Playground for this code

Including an identical field within the parameters of a MongoDB search query

Answer №1

Answer №2

Similar questions

Tips for resolving issues with mat-autocomplete during scrolling

Determining intersecting date ranges within sub-documents using MongoDB

Looking to implement client-side JavaScript validation prior to utilizing jQuery AJAX validation

Prevent JavaScript from sending a POST request to a specific URL

Fixed Positioning Div to Stay at the Top while Scrolling

The IE9 confirmation dialog fails to pause for user response, resulting in automatic postback before user input is received

Contrasting characteristics of class members in JavaScript versus TypeScript

Generate a collection of LatLng coordinates to represent a worldwide grid

What is the best way to achieve a precision of 6 decimal places in JavaScript when working with decimals?

Can a plugin be executed as a test?

Tips for making an ajax call in Angular 6

Storing JavaScript code in a PHP variable fails to function

What is the best way to have a form open upwards when hovered over or clicked on?

The useParams() function is returning undefined, even though the parameter does exist in the destination URL

Utilizing Redux-Form to Retrieve Input Values

Angular throws an error when trying to parse undefined data outside of an async function

Exploring jQuery AJAX and how to effectively manage various data types

Retrieve the designated element from an array of JSON data in SPLUNK

Take action upon window.open

Saving data from Material UI forms in TypeScript