Is it possible to cast in a MongoDB-Query?

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.

You can use the following JavaScript expression:

db.test.find("this.value > 12")

This uses JavaScript's automatic conversion from string to number.

I have a similar workaround, i find that if you can use the mongo shell, you can write an statement to do this in javascript, but capable of using indexes.

var myItems = []
var it = db.test.find({},{value:1})
while (it.hasNext()){
 var item = it.next();
 if(parseInt(item.value) > 12)
  myItems.push(item);
}

If you want this to run faster than previus solution, you have to ensure the index on the value field.

To convert String into int use this

db.test.find({'year': {$type: 2}}).forEach(
    function (x) {
        x.value=new NumberInt(x.value); 
        db.test.save(x)}
    )

And after that you can directly query like :

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

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.

Is it possible to cast in a MongoDB-Query?

Related

Recent Posts