英文:
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"
]
}
})
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"
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论