在MongoDB查找查询的条件中添加相同的字段。

huangapple go评论73阅读模式
英文:

Adding the same field in the condition of mongodb find query

问题

我有一个名为testdata的MongoDB集合,其中包含一个名为insertTime的字段。我们有一个要求,需要删除早于60天的数据。因此,以前要删除集合中所有早于60天的文档的旧数据,我会使用以下逻辑首先找到删除日期,然后与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 }})

然而,现在我想要删除早于记录的alive时间的数据。Alive时间将被计算为insertTime + endTime(60天)。现在,早于这个alive时间减去60天的文档应该被删除。有人能帮助我实现这个吗?

我能想到的是像这样的内容,但我认为命令不正确:

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

在MongoDB的find命令查询中,如何实现这一点?请提供关于这个问题的见解。非常感谢。

我已经添加了上面的所有细节和我想要实现的内容。

编辑:使用AWS DocumentDB 4.0.0

英文:

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

答案1

得分: 1

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 a compatible solution for 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 is 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 } }
  ]
});
英文:

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

答案2

得分: 0

你可以使用$dateAdd(从MongoDB v5.0+可用)来计算活动日期并与$$NOW进行比较。

MongoDB版本的示例代码:

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

AWS DocumentDB(v4.0)版本的示例代码:

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

Mongo Playground(MongoDB版本)

Mongo Playground(AWS DocumentDB版本)

英文:

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

huangapple
  • 本文由 发表于 2023年2月14日 22:42:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/75449426.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定