Mongo Cast String to Number for Query

Question

Is there a way to do a less than or equal to query on a string field of a number?

Example Data

|           Id           |           Price           |
|           1            |           "1000.00"       |
|           2            |           "5400.00"       |
|           3            |           "750.00"        |

When I execute this query it returns all of the records:

db.MyCollection.find({ Price: {$lte: "1000.0"} })

When I execute this query it returns nothing:

db.MyCollection.find({ Price: {$lte: 1000.0} })

Answer 1

Update (valid from MongoDB v4.0 onwards):

You can use a combination of $expr and $toDouble to achieve the desired behaviour like this:

db.MyCollection.find({ $expr: { $lte: [ { $toDouble: "$Price" }, 1000.0 ] } })

Original Answer (MongoDB v3.6 and below):

MongoDB cannot convert strings to numbers. So your only option here is to use the dreaded $where operator:

db.MyCollection.find({ $where: "parseInt(this.Price) <= 1000" })

This won't use any indexes and is not exactly fast but perhaps kind of ok still for small collections.

Nonetheless I would recommend storing numbers as numerical types. So you should convert your strings to ints or longs (or probably doubles even) as shown here: how to convert string to numerical values in mongodb