MongoDB添加从datetime计算的新字段并转换为int数 [英] MongoDB Add new field calculated from datetime and converted to int number
问题描述
我是一个MongoDB集合(在 MongoDB v3.2.12 上),其中包含 10亿条记录。
该集合具有日期时间键。我需要在日期时间中添加一个新的密钥,例如值yyyymmdd,并将其保存为整数。
I'va a MongoDB collection (on MongoDB v3.2.12) with about 1 Billion records. The collection has a datetime key. I need to add a new key to the collection, derived from the datetime one, such as the value is "yyyymmdd" and save it as an integer.
现在使用以下命令,我可以使用新密钥作为字符串创建一个重复的集合。
For now with the following command I was able to create a duplicated collection with the new key as a string.
db.log.aggregate(
[
{ "$addFields": {
"date_ref": { $dateToString: { format: "%Y%m%d", date: "$log_date" } }
}},
{ "$out": "log_test" }
]
)
尝试添加 NumberInt()
或 parseInt()
如下,不起作用。
Trying to add NumberInt()
or parseInt()
as following, didn't work.
db.log.aggregate(
[
{ "$addFields": {
"date_ref": NumberInt( { $dateToString: { format: "%Y%m%d", date: "$log_date" } } )
}},
{ "$out": "log_test" }
]
)
使用 NumberInt()
I g et all值为0.
使用 parseInt()
我将所有值都设置为'NAN'。
Using NumberInt()
I get all values to 0.
Using parseInt()
I get all values to 'NAN'.
我知道我可以使用新密钥和 foreach
语句,如下所示:
I know I can use a new key and a foreach
statement like this:
db.log_test.find().forEach( function (l) {
l.date_ref_int = parseInt(l.date_ref);
db.log_test.save(l);
});
但这种方法在时间上非常非常昂贵。
那么,是否可以在汇总内进行转换?
But this approach is very, very expensive in terms of time. So, is it possible to do the conversion within the aggregate?
更新
我尝试的另一种方法是:
Another approach I tried was this:
db.log.aggregate(
[
{ "$addFields": {
"date_ref_string": { $dateToString: { format: "%Y%m%d", date: "$log_date" } },
"date_ref": NumberInt("date_ref_string")
}},
{ "$out": "log_test" }
]
)
我没有错误,但date_ref
始终为0。
I get no errors but the "date_ref"
is always 0.
更新2
关注@Veeram建议我制作了这个剧本:
Following @Veeram suggestion I made this script:
db.log.aggregate(
[
{ $addFields:
{ "date_ref":
{ $divide:
[
{ $add:
[
{ $multiply: [{"$year" : "$log_date"}, 10000000]},
{ $multiply: [{"$month" : "$log_date"}, 100000]},
{ $multiply: [{"$dayOfMonth" : "$log_date"}, 1000]}
]
}, 1000
]
}
}
},
{ "$out": "log_test" }
]
);
它可以工作,但我得到的值是 double
。我更喜欢一个整数,因为我需要在新的计算密钥上建立一个索引,如果密钥是整数
类型,它会更小。
It works, but I get back values as double
. I prefer an integer, because I need to build an index on that new calculated key and it would be smaller if the key is integer
type.
推荐答案
您可以使用以下聚合查询。
You can use below aggregation query.
db.log.aggregate([
{"$addFields":{
"date_ref":{
"$trunc":{
"$divide":[
{"$add":[
{"$multiply":[{"$year":"$log_date"},10000000]},
{"$multiply":[{"$month":"$log_date"},100000]},
{"$multiply":[{"$dayOfMonth":"$log_date"},1000]}
]},
1000
]
}
}
}}
])
这篇关于MongoDB添加从datetime计算的新字段并转换为int数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!