Casting string to number in MongoDB aggregation

Question

I have tried to do this in every possible way, searched through all the resources but could not find a solution to what appears to be a trivial problem.

I have a huge collection imported from elsewhere with a document field being of string rather than of numeric type. Attempting to do sorting on field works but naturally does a string sort rather than numeric. Doing a $sum or $avg yields 0 and null respectively.

What I desperately need is a way to $cast my string field into a numeric value but such feature does not appear to exist. I have tried tricks like $add: ['field', 0] or $multiply: ['field', 1] without any success as the arithmetic operators apparently want numeric values. No automatic conversion seems to occur. I have tried this both in $group and $project stages to no avail.

If everything else fails, I will have to update my collection by inserting a new numeric field corresponding to my field but this isn't the preferred solution as the collection will be frequently and fully re-imported from an external source.

Possible duplicate of [how to convert string to numerical values in mongodb](https://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb) — dnickless, Oct 30 '17 at 15:45
That post (https://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb) angles toward actually converting the values. — Buzz Moschetti, Oct 30 '17 at 15:50

score 2 · Answer 1 · answered Oct 30 '17 at 15:54

Here's a quick stab at it. Hope it's all positive integers (i.e. no "-" or ".") otherwise the logic will get more complex. The idea is break the string into chars and "manually" convert to an int. Given an input doc like this:

{  snum: "34567" }

it will create a new var qq that is an actual int, e.g.

{  qq: 34567 }


db.foo.aggregate([
{$addFields: {x: {$map: {
        input: {$range: [0,{$strLenBytes: "$snum"}] } ,
        as: "z",
        in: {
            $let: {
            vars: {c: {$substrBytes:["$snum","$$z",1]}},
            in: {
                "v" : {$switch:
               {
                branches: [
{ case: {$eq: [ "0", "$$c" ]}, then: 0}
,{ case: {$eq: [ "1", "$$c" ]}, then: 1}
,{ case: {$eq: [ "2", "$$c" ]}, then: 2}
,{ case: {$eq: [ "3", "$$c" ]}, then: 3}
,{ case: {$eq: [ "4", "$$c" ]}, then: 4}
,{ case: {$eq: [ "5", "$$c" ]}, then: 5}
,{ case: {$eq: [ "6", "$$c" ]}, then: 6}
,{ case: {$eq: [ "7", "$$c" ]}, then: 7}
,{ case: {$eq: [ "8", "$$c" ]}, then: 8}
,{ case: {$eq: [ "9", "$$c" ]}, then: 9}
                       ],
               default: "$$c"
               }
                },
                exp: {$subtract: [ {$strLenBytes: "$snum"}, {$add:[1,"$$z"]} ]}
            }
            }
        }
        }}
    }}

,{$addFields: {qq: {$reduce: {
        input: "$x",
        initialValue: 0,
        in: {$add: [ "$$value", {$multiply: ["$$this.v", {$pow: [ 10, "$$this.exp"]} ]} ]}
        }}
    }}
            ]);

Casting string to number in MongoDB aggregation

1 Answers1