Is it possible to cast in a MongoDB-Query?

user_425163 0
Viewed 23106

When I have two MongoDB documents like this...

db.test.insert( {"value" : "10123"} );
db.test.insert( {"value" : "160"} );

The result of a query like: 

db.test.find({"value" :{$gt : "12"} });

is..

{ "_id" : ObjectId("4c6d1b92304326161b678b89"), "value" : "160" }

It's obvious, that a string comparison is made, so that my first value is not returned. Is there any way to cast within the query?

Something like: 

db.test.find({ (int) "value" :{$gt : 12} });

would be great. A query like 

db.test.find({"value" :{$gt : 12} }); // without the quotes around "12"

returns nothing.

mongodb-query mongodb casting
5 Answers

Type casting in MongoDB is available after version >= 4.0. Check MongoDB's aggregation operator $convert and similar operators. Since you wanted to convert string to int you can use $toInt:

db.collection.find({ $expr: { $gt: [ { $toInt: "$value" }, 12 ] } })

Test : mongoplayground

Note :

Here we're converting value which is a string field to int on the fly & Since it got converted to int - we're comparing it to input of type int. Your output documents will still have original type for value field which is string (we're not actually changing type in response docs, if needed use aggregation & it's stage $project to see int values for field value in response docs).

Since we're using aggregation operators in .find() we need to wrap everything in $expr.

Even though this is pretty common nowadays & is easy to do, but remember we're casting string to int on every Read, rather if you can take care of this on Writes or Updates it would be easy & more efficient.

user_72376130
Related