MongoDB - Perform $divide on String fields

Question

I am trying to perform an aggregation query on a collection. I want to divide 2 of the fields and check if this is less than a certain value.

The way this db was made, it has all the fields as strings, and this isn't something I can really change right now. I wondered if there was a way to cast the values as numbers, maybe using $let or something?

As it is I currently get the error

exception: $divide only supports numeric types, not String and String

This is an example of the sort of query I am trying to run:

db.myCollection.aggregate([{
    "$project": {
        "mins": {
            "$divide": ["$ALOWOVRLTOTL", "$FEESCALLCOST"]}
         }
     },
     "$match": {
         "mins": {
              "$lte": 40
          }
      }
}])

score 0 · Answer 1 · edited May 23 '17 at 12:08

0

You won't be able to perform the divide operation on non-numerical values. A possible solution to this would be to run an update and parse the String values to a numerical value.

Have a look at this question: how to convert string to numerical values in mongodb

edited May 23 '17 at 12:08

Community

1
1

answered Jun 27 '16 at 09:58

Andy Jenkins

617
8
26

score 0 · Answer 2 · answered Sep 01 '23 at 21:17

You need to pay attention to the exception. And adjust your query accordingly.

exception: $divide only supports numeric types, not String and String

db.myCollection.aggregate([{
"$project": {
    "mins": {
        "$divide": [{'$toDouble': '$ALOWOVRLTOTL' }, {'$toDouble': '$FEESCALLCOST' }]}
     }
 },
 "$match": {
     "mins": {
          "$lte": 40
      }
  }
}])

You have several types available to convert your string values to depending on your particular situation. Find below a list of types you can convert.

$toDecimal
$toDouble
$toInt
$toLong

See MongoDB official documentation on Conversion.

MongoDB - Perform $divide on String fields

2 Answers2