9

I have a collection of documents that have a value that is known to be a number, but is stored as a string. It is out of my control to change the type of the field, but I want to use that field in an aggregation (say, to average it).

It seems that I should be using a projection prior to grouping, and in that projection convert the field as needed. I can't seem to get the syntax just right - everything I try either gives me NaN, or the new field is simply missing from the next step in the aggregation.

$project: {
    value: '$value',
    valueasnumber: ????
}

Given the very simple example above, where the contents of $value in all documents are string type, but will parse to a number, what do I do to make valueasnumber a new (non-existing) field that is of type double with the parsed version of $value in it?

I've tried things like the examples below (and about a dozen similar things):

{ $add: new Number('$value').valueOf() }
new Number('$value').valueOf()

Am I barking up the wrong tree entirely? Any help would be greatly appreciated!

(To be 100% clear, below is how I would like to use the new field).

$group {
    score: {
        $avg: '$valueasnumber'
    }
}
VMai
  • 10,156
  • 9
  • 25
  • 34
Jesse English
  • 91
  • 1
  • 1
  • 2
  • 1
    As of MongoDB 2.6, you are out of luck. There's no number parsing options available in the aggregation framework. If the field represents a number, it should really be stored as a number; perhaps you could add on another field that is the number form of the number? You can also consider map/reduce. – wdberkeley Sep 23 '14 at 14:33
  • Thanks for the comments everyone. I'm trying out both approaches to see what works best. It seems that the aggregation framework has better performance than map/reduce, so it might be worth maintaining the second field (as a number) so that aggregation can be used as intended. – Jesse English Sep 24 '14 at 13:05
  • Possible duplicate of [how to convert string to numerical values in mongodb](http://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb) – styvane Dec 29 '16 at 16:44

3 Answers3

3

One of the way which I can think of is to use a mongo shell javascript to modify the document by adding new number field, valuesasnumber (number conversion of existing string 'value' field) in the existing document or in the new doc. Then using this numeric field for further calculations.

db.numbertest.find().forEach(function(doc) {
    doc.valueasnumber = new NumberInt(doc.value);
    db.numbertest.save(doc);
});

Using the valueasnumber field for numeric calculation

db.numbertest.aggregate([{$group : 
   {_id : null, 
    "score" : {$avg : "$valueasnumber"}
   }
}]);
Vijay Rawat
  • 241
  • 2
  • 7
2

The core operation is to convert value from string to number which is unable to handled in aggregate pipeline operation currently.
mapReduce is an alternative as below. 

db.c.mapReduce(function() {
    emit( this.groupId, {score: Number(this.value), count: 1} );
}, function(key, values) {
    var score = 0, count = 0;
    for (var i = 0; i < values.length; i++) {
        score += values[i].score;
        count += values[i].count;
    }
    return {score: score, count: count};
}, {finalize: function(key, value) {
    return {score: value.score / value.count};
}, out: {inline: 1}});
Wizard
  • 4,341
  • 1
  • 15
  • 13
1

Now there is $toInt conversion operators in aggregation, you can check: https://jira.mongodb.org/browse/SERVER-11400

coding
  • 627
  • 7
  • 20