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?
String can be converted to numbers in MongoDB v4.0 using $toInt operator. In this case
outputs:
Using MongoDB 4.0 and newer
You have two options i.e.
$toInt
or$convert
. Using$toInt
, follow the example below:If the conversion operation encounters an error, the aggregation operation stops and throws an error. To override this behavior, use
$convert
instead.Using
$convert
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 convertedmoop
string value to thePartnerID
for each document and emits thePartnerID
and convertedmoop
pair. This is where the javascript native functionparseInt()
can be applied:Next, define the corresponding reduce function with two arguments
keyCustId
andvaluesMoop
.valuesMoop
is an array whose elements are the integermoop
values emitted by the map function and grouped bykeyPartnerID
. The function reduces thevaluesMoop
array to the sum of its elements.For example, with the following sample collection of documents:
The above Map/Reduce operation will save the results to the
example_results
collection and the shell commanddb.example_results.find()
will give: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..
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:
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:
and
and
and