mongodb 如何在mongodb中将字符串转换为数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29487351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to convert string to numerical values in mongodb
提问by Naftsen
I am trying to convert a string that contains a numerical value to its value in an aggregate query in MongoDB.
我正在尝试将包含数值的字符串转换为其在 MongoDB 中的聚合查询中的值。
Example of document
文件示例
{
"_id": ObjectId("5522XXXXXXXXXXXX"),
"Date": "2015-04-05",
"PartnerID": "123456",
"moop": "1234"
}
Example of the aggregate query I use
我使用的聚合查询示例
{
aggregate: 'my_collection',
pipeline: [
{$match: {
Date :
{$gt:'2015-04-01',
$lt: '2015-04-05'
}}
},
{$group:
{_id: "$PartnerID",
total:{$sum:'$moop'}
}}]}
where the results are
结果在哪里
{
"result": [
{
"_id": "123456",
"total": NumberInt(0)
}
}
How can you convert the string to its numerical value?
如何将字符串转换为其数值？
回答by Yogesh
MongoDB aggregation not allowed to change existing data type of given fields. In this case you should create some programming code to convert stringto int. Check below code
MongoDB 聚合不允许更改给定字段的现有数据类型。在这种情况下，您应该创建一些编程代码以转换string为int. 检查下面的代码
db.collectionName.find().forEach(function(data) {
db.collectionName.update({
"_id": data._id,
"moop": data.moop
}, {
"$set": {
"PartnerID": parseInt(data.PartnerID)
}
});
})
If your collections size more then above script will slow down the performance, for perfomace mongo provide mongo bulkoperations, using mongo bulk operations also updated data type
如果您的集合大小超过上述脚本会降低性能，对于 perfomace mongo 提供mongo 批量操作，使用 mongo 批量操作也会更新数据类型
var bulk = db.collectionName.initializeOrderedBulkOp();
var counter = 0;
db.collectionName.find().forEach(function(data) {
var updoc = {
"$set": {}
};
var myKey = "PartnerID";
updoc["$set"][myKey] = parseInt(data.PartnerID);
// queue the update
bulk.find({
"_id": data._id
}).update(updoc);
counter++;
// Drain and re-initialize every 1000 update statements
if (counter % 1000 == 0) {
bulk.execute();
bulk = db.collectionName.initializeOrderedBulkOp();
}
})
// Add the rest in the queue
if (counter % 1000 != 0) bulk.execute();
This basically reduces the amount of operations statements sent to the sever to only sending once every 1000 queued operations.
这基本上将发送到服务器的操作语句数量减少到每 1000 个排队操作仅发送一次。
回答by Amarendra Kumar
You can easily convert the string data type to numerical data type.
您可以轻松地将字符串数据类型转换为数值数据类型。
Don't forget to change collectionName & FieldName. for ex : CollectionNmae : Users & FieldName : Contactno.
不要忘记更改 collectionName 和 FieldName。例如：CollectionNmae：Users & FieldName：Contactno。
Try this query..
试试这个查询..
db.collectionName.find().forEach( function (x) {
x.FieldName = parseInt(x.FieldName);
db.collectionName.save(x);
});
回答by chridam
Using MongoDB 4.0 and newer
使用 MongoDB 4.0 及更新版本
You have two options i.e. $toIntor $convert. Using $toInt, follow the example below:
您有两个选择，即$toInt或$convert。使用$toInt，请按照以下示例操作：
filterDateStage = {
'$match': {
'Date': {
'$gt': '2015-04-01',
'$lt': '2015-04-05'
}
}
};
groupStage = {
'$group': {
'_id': '$PartnerID',
'total': { '$sum': { '$toInt': '$moop' } }
}
};
db.getCollection('my_collection').aggregate([
filterDateStage,
groupStage
])
If the conversion operation encounters an error, the aggregation operation stops and throws an error. To override this behavior, use $convertinstead.
如果转换操作遇到错误，聚合操作会停止并抛出错误。要覆盖此行为，请$convert改用。
Using $convert
使用 $convert
groupStage = {
'$group': {
'_id': '$PartnerID',
'total': {
'$sum': {
'$convert': { 'input': '$moop', 'to': 'int' }
}
}
}
};
Using Map/Reduce
使用 Map/Reduce
With map/reduce you can use javascript functions like parseInt()to do the conversion. As an example, you could define the map function to process each input document:
In the function, thisrefers to the document that the map-reduce operation is processing. The function maps the converted moopstring value to the PartnerIDfor each document and emits the PartnerIDand converted mooppair. This is where the javascript native function parseInt()can be applied:
使用 map/reduce，您可以使用 javascript 函数parseInt()来进行转换。例如，您可以定义 map 函数来处理每个输入文档：在函数中，this指的是 map-reduce 操作正在处理的文档。该函数将转换后的moop字符串值映射到PartnerID每个文档的 和 并发出PartnerID和 转换后的moop对。这是parseInt()可以应用javascript 本机函数的地方：
var mapper = function () {
var x = parseInt(this.moop);
emit(this.PartnerID, x);
};
Next, define the corresponding reduce function with two arguments keyCustIdand valuesMoop. valuesMoopis an array whose elements are the integer moopvalues emitted by the map function and grouped by keyPartnerID.
The function reduces the valuesMooparray to the sum of its elements.
接下来，使用两个参数keyCustId和定义相应的reduce 函数valuesMoop。valuesMoop是一个数组，其元素是moop由 map 函数发出并按 分组的整数值keyPartnerID。该函数将valuesMoop数组减少为其元素的总和。
var reducer = function(keyPartnerID, valuesMoop) {
return Array.sum(valuesMoop);
};
db.collection.mapReduce(
mapper,
reducer,
{
out : "example_results",
query: {
Date: {
$gt: "2015-04-01",
$lt: "2015-04-05"
}
}
}
);
db.example_results.find(function (err, docs) {
if(err) console.log(err);
console.log(JSON.stringify(docs));
});
For example, with the following sample collection of documents:
例如，使用以下示例文档集合：
/* 0 */
{
"_id" : ObjectId("550c00f81bcc15211016699b"),
"Date" : "2015-04-04",
"PartnerID" : "123456",
"moop" : "1234"
}
/* 1 */
{
"_id" : ObjectId("550c00f81bcc15211016699c"),
"Date" : "2015-04-03",
"PartnerID" : "123456",
"moop" : "24"
}
/* 2 */
{
"_id" : ObjectId("550c00f81bcc15211016699d"),
"Date" : "2015-04-02",
"PartnerID" : "123457",
"moop" : "21"
}
/* 3 */
{
"_id" : ObjectId("550c00f81bcc15211016699e"),
"Date" : "2015-04-02",
"PartnerID" : "123457",
"moop" : "8"
}
The above Map/Reduce operation will save the results to the example_resultscollection and the shell command db.example_results.find()will give:
上面的 Map/Reduce 操作将结果保存到example_results集合中，shell 命令db.example_results.find()将给出：
/* 0 */
{
"_id" : "123456",
"value" : 1258
}
/* 1 */
{
"_id" : "123457",
"value" : 29
}
回答by Naftsen
Eventually I used
最后我用
db.my_collection.find({moop: {$exists: true}}).forEach(function(obj) {
obj.moop = new NumberInt(obj.moop);
db.my_collection.save(obj);
});
to turn moopfrom string to integer in my_collection following the example in Simone's answer MongoDB: How to change the type of a field?.
把moop从字符串按照西蒙的回答的例子整数my_collection的MongoDB：如何改变一个字段的类型？.
回答by mickl
回答by dnickless
Here is a pure MongoDB based solution for this problem which I just wrote for fun. It's effectively a server-side string-to-number parser which supports positive and negative numbers as well as decimals:
这是针对这个问题的纯基于 MongoDB 的解决方案，我只是为了好玩而写的。它实际上是一个服务器端字符串到数字的解析器，支持正数和负数以及小数：
db.collection.aggregate({
$addFields: {
"moop": {
$reduce: {
"input": {
$map: { // split string into char array so we can loop over individual characters
"input": {
$range: [ 0, { $strLenCP: "$moop" } ] // using an array of all numbers from 0 to the length of the string
},
"in":{
$substrCP: [ "$moop", "$$this", 1 ] // return the nth character as the mapped value for the current index
}
}
},
"initialValue": { // initialize the parser with a 0 value
"n": 0, // the current number
"sign": 1, // used for positive/negative numbers
"div": null, // used for shifting on the right side of the decimal separator "."
"mult": 10 // used for shifting on the left side of the decimal separator "."
}, // start with a zero
"in": {
$let: {
"vars": {
"n": {
$switch: { // char-to-number mapping
branches: [
{ "case": { $eq: [ "$$this", "1" ] }, "then": 1 },
{ "case": { $eq: [ "$$this", "2" ] }, "then": 2 },
{ "case": { $eq: [ "$$this", "3" ] }, "then": 3 },
{ "case": { $eq: [ "$$this", "4" ] }, "then": 4 },
{ "case": { $eq: [ "$$this", "5" ] }, "then": 5 },
{ "case": { $eq: [ "$$this", "6" ] }, "then": 6 },
{ "case": { $eq: [ "$$this", "7" ] }, "then": 7 },
{ "case": { $eq: [ "$$this", "8" ] }, "then": 8 },
{ "case": { $eq: [ "$$this", "9" ] }, "then": 9 },
{ "case": { $eq: [ "$$this", "0" ] }, "then": 0 },
{ "case": { $and: [ { $eq: [ "$$this", "-" ] }, { $eq: [ "$$value.n", 0 ] } ] }, "then": "-" }, // we allow a minus sign at the start
{ "case": { $eq: [ "$$this", "." ] }, "then": "." }
],
default: null // marker to skip the current character
}
}
},
"in": {
$switch: {
"branches": [
{
"case": { $eq: [ "$$n", "-" ] },
"then": { // handle negative numbers
"sign": -1, // set sign to -1, the rest stays untouched
"n": "$$value.n",
"div": "$$value.div",
"mult": "$$value.mult",
},
},
{
"case": { $eq: [ "$$n", null ] }, // null is the "ignore this character" marker
"then": "$$value" // no change to current value
},
{
"case": { $eq: [ "$$n", "." ] },
"then": { // handle decimals
"n": "$$value.n",
"sign": "$$value.sign",
"div": 10, // from the decimal separator "." onwards, we start dividing new numbers by some divisor which starts at 10 initially
"mult": 1, // and we stop multiplying the current value by ten
},
},
],
"default": {
"n": {
$add: [
{ $multiply: [ "$$value.n", "$$value.mult" ] }, // multiply the already parsed number by 10 because we're moving one step to the right or by one once we're hitting the decimals section
{ $divide: [ "$$n", { $ifNull: [ "$$value.div", 1 ] } ] } // add the respective numerical value of what we look at currently, potentially divided by a divisor
]
},
"sign": "$$value.sign",
"div": { $multiply: [ "$$value.div" , 10 ] },
"mult": "$$value.mult"
}
}
}
}
}
}
}
}
}, {
$addFields: { // fix sign
"moop": { $multiply: [ "$moop.n", "$moop.sign" ] }
}
})
I am certainly not advertising this as the bee's knees or anything and it might have severe performance implications for larger datasets over a client based solutions but there might be cases where it comes in handy...
我当然不会把它宣传为蜜蜂的膝盖或任何东西，它可能会对基于客户端的解决方案的更大数据集产生严重的性能影响，但在某些情况下它可能会派上用场......
The above pipeline will transform the following documents:
上述管道将转换以下文件：
{ "moop": "12345" } --> { "moop": 12345 }
and
和
{ "moop": "123.45" } --> { "moop": 123.45 }
and
和
{ "moop": "-123.45" } --> { "moop": -123.45 }
and
和
{ "moop": "2018-01-03" } --> { "moop": 20180103.0 }
回答by Bing Wu
Three things need to care for:
需要注意三点：
- parseInt() will store double data type in mongodb. Please use new NumberInt(string).
- in Mongo shell command for bulk usage, yield won't work. Please DO NOT add 'yield'.
- If you already change string to double by parseInt(). It looks like you have no way to change the type to int directly. The solution is a little bit wired: change double to string first and then change back to int by new NumberInt().
- parseInt() 将在 mongodb 中存储双数据类型。请使用新的 NumberInt(string)。
- 在用于批量使用的 Mongo shell 命令中，yield 不起作用。请不要添加“产量”。
- 如果您已经通过 parseInt() 将 string 更改为 double。看起来您无法直接将类型更改为 int 。解决方案有点复杂：先将 double 更改为 string，然后通过 new NumberInt() 将其更改回 int。
回答by Reza
It should be saved. It should be like this :
它应该被保存。应该是这样的：
db. my_collection.find({}).forEach(function(theCollection) {
theCollection.moop = parseInt(theCollection.moop);
db.my_collection.save(theCollection);
});
回答by Dan Loughney
Collation is what you need:
整理是你需要的：
db.collectionName.find().sort({PartnerID: 1}).collation({locale: "en_US", numericOrdering: true})
回答by Dalibor Adamec
Try:
尝试：
"TimeStamp":{$toDecimal: { $toDate:"$Datum"}}
