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

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

Adding the same field in the condition of mongodb find query

问题

我有一个名为testdata的MongoDB集合，其中包含一个名为insertTime的字段。我们有一个要求，需要删除早于60天的数据。因此，以前要删除集合中所有早于60天的文档的旧数据，我会使用以下逻辑首先找到删除日期，然后与updateTime进行比较：

  1. var date = new Date();
  2. var daysToDeletion = 60;
  3. var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  4. deletionDate = deletionDate.toISOString()
  5. printjson(insertDate);
  7. db.testdata.find({"insertTime": { $lt: deletionDate }})

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

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

  1. 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:

  1. var date = new Date();
  2. var daysToDeletion = 60;
  3. var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  4. deletionDate = deletionDate.toISOString()
  5. printjson(insertDate);
  7. 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:

  1. 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:

  1. var date = new Date();
  2. var daysToDeletion = 60;
  3. var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  5. db.testdata.deleteMany({
  6.     $expr: {
  7.         $lt: [{ $add: ["$insertTime", "$endTime"] }, deletionDate]
  8.     }
  9. });

Edit:
With a compatible solution for DocumentDB:

  1. var date = new Date();
  2. var daysToDeletion = 60;
  3. var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  5. db.testdata.find({
  6.     $lt: {
  7.         $add: [
  8.             "$insertTime",
  9.             { $multiply: [daysToDeletion, 24 * 60 * 60 * 1000] }
  10.         ],
  11.     },
  12.     deletionDate
  13. });

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

This one is a little bit tricky, but it works:

  1. const date = new Date();
  2. const daysToDeletion = 60;
  3. const deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  4. const aliveTime = { $add: ["$insertTime", "$endTime"] };
  6. db.testdata.deleteMany({
  7.   $and: [
  8.     { aliveTime: { $lt: deletionDate } },
  9.     { insertTime: { $lt: deletionDate } }
  10.   ]
  11. });
英文:

I think this $expr can help you:

  1. var date = new Date();
  2. var daysToDeletion = 60;
  3. var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  5. db.testdata.deleteMany({
  6.     $expr: {
  7.         $lt: [{ $add: ["$insertTime", "$endTime"] }, deletionDate]
  8.     }
  9. });

Edit:
With compatible solution with documentdb:

  1. var date = new Date();
  2. var daysToDeletion = 60;
  3. var deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  5. db.testdata.find(
  6.     {
  7.         $lt: {
  8.             $add: [
  9.                 "$insertTime",
  10.                 { $multiply: [daysToDeletion, 24 * 60 * 60 * 1000] }
  11.             ]
  12.         },
  13.         deletionDate
  14.     }
  15. );

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

This one a little bit tricky but it works

  1. const date = new Date();
  2. const daysToDeletion = 60;
  3. const deletionDate = new Date(date.setDate(date.getDate() - daysToDeletion));
  4. const aliveTime = { $add: ["$insertTime", "$endTime"] };
  6. db.testdata.deleteMany({
  7.   $and: [
  8.     { aliveTime: { $lt: deletionDate } },
  9.     { insertTime: { $lt: deletionDate } }
  10.   ]
  11. });

答案2

得分: 0

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

MongoDB版本的示例代码：

  1. db.collection.find({
  2.   $expr: {
  3.     $lt: [
  4.       {
  5.         "$dateAdd": {
  6.           "startDate": "$insertTime",
  7.           "unit": "day",
  8.           "amount": 60
  9.         }
  10.       },
  11.       "$$NOW"
  12.     ]
  13.   }
  14. })

AWS DocumentDB（v4.0）版本的示例代码：

  1. db.collection.aggregate([
  2.   {
  3.     "$addFields": {
  4.       flag: {
  5.         $lt: [
  6.           {
  7.             $add: [
  8.               "$insertTime",
  9.               5184000000
  10.             ]
  11.           },
  12.           "$$NOW"
  13.         ]
  14.       }
  15.     }
  16.   },
  17.   {
  18.     "$match": {
  19.       flag: true
  20.     }
  21.   },
  22.   {
  23.     "$unset": "flag"
  24.   }
  25. ])

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

  1. db.collection.find({
  2.   $expr: {
  3.     $lt: [
  4.       {
  5.         "$dateAdd": {
  6.           "startDate": "$insertTime",
  7.           "unit": "day",
  8.           "amount": 60
  9.         }
  10.       },
  11.       "$$NOW"
  12.     ]
  13.   }
  14. })

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.

  1. db.collection.aggregate([
  2.   {
  3.     "$addFields": {
  4.       flag: {
  5.         $lt: [
  6.           {
  7.             $add: [
  8.               "$insertTime",
  9.               5184000000
  10.             ]
  11.           },
  12.           "$$NOW"
  13.         ]
  14.       }
  15.     }
  16.   },
  17.   {
  18.     "$match": {
  19.       flag: true
  20.     }
  21.   },
  22.   {
  23.     "$unset": "flag"
  24.   }
  25. ])

Mongo Playground

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

发表评论

匿名网友

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

确定