I am trying to convert a string that contains a numerical value to its value in an aggregate query in 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?
This question is related to
mongodb
mongodb-query
Collation is what you need:
db.collectionName.find().sort({PartnerID: 1}).collation({locale: "en_US", numericOrdering: true})
String can be converted to numbers in MongoDB v4.0 using $toInt operator. In this case
db.col.aggregate([
{
$project: {
_id: 0,
moopNumber: { $toInt: "$moop" }
}
}
])
outputs:
{ "moopNumber" : 1234 }
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 moop from string to integer in my_collection following the example in Simone's answer MongoDB: How to change the type of a field?.
If you can edit all documents in aggregate :
"TimeStamp": {$toDecimal: {$toDate: "$Your Date"}}
And for the client, you set the query :
Date.parse("Your date".toISOString())
That's what makes you whole work with ISODate.
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.
Try this query..
db.collectionName.find().forEach( function (x) {
x.FieldName = parseInt(x.FieldName);
db.collectionName.save(x);
});
db.user.find().toArray().filter(a=>a.age>40)
Three things need to care for:
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:
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 }
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);
});
Try:
"TimeStamp":{$toDecimal: { $toDate:"$Datum"}}
MongoDB aggregation not allowed to change existing data type of given fields. In this case you should create some programming code to convert string to int. Check below code
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 bulk operations, using mongo bulk operations also updated data type
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.
Though $toInt is really useful, it was added on mongoDB 4.0, I've run into this same situation in a database running 3.2 which upgrading to use $toInt was not an option due to some other application incompatibilities, so i had to come up with something else, and actually was surprisingly simple.
If you $project and $add zero to your string, it will turn into a number
{
$project : {
'convertedField' : { $add : ["$stringField",0] },
//more fields here...
}
}
Using MongoDB 4.0 and newer
You have two options i.e. $toInt or $convert. Using $toInt, follow the example below:
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 $convert instead.
Using $convert
groupStage = {
'$group': {
'_id': '$PartnerID',
'total': {
'$sum': {
'$convert': { 'input': '$moop', 'to': 'int' }
}
}
}
};
Using 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, this refers to the document that the map-reduce operation is processing. The function maps the converted moop string value to the PartnerID for each document and emits the PartnerID and converted moop pair. This is where the javascript native function parseInt() can be applied:
var mapper = function () {
var x = parseInt(this.moop);
emit(this.PartnerID, x);
};
Next, define the corresponding reduce function with two arguments keyCustId and valuesMoop. valuesMoop is an array whose elements are the integer moop values emitted by the map function and grouped by keyPartnerID.
The function reduces the valuesMoop array to the sum of its elements.
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_results collection and the shell command db.example_results.find() will give:
/* 0 */
{
"_id" : "123456",
"value" : 1258
}
/* 1 */
{
"_id" : "123457",
"value" : 29
}
Source: Stackoverflow.com