Is it possible to cast in a MongoDB-Query?

Question

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.

Solution 1

You can use the following JavaScript expression:

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

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

Solution 2

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.

Solution 3

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.

Solution 4

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} });

View more solutions

Comments

[Giles Smith]

Is there a reason that you are storing integers as strings? Wouldn't be better to do db.test.insert({"value":10123});

[rgroli]

That's true, of course.. In my application I collect formvalues and pass them on to Mongo. POST and GET values are string-typed by default. I suspected the driver would take care of the type conversion. Unfortunately that's not the case. So your're right, I convert the data before inserting. Also, @niels made a good point... using a javascript-expression actually solves the problem.

[Ozal Zarbaliyev]

I face this problem in 2020 :). I think that it would be good type cast numeric string values to integer before storing it in db. So I am gonna do it before storing in db.

[whoami - fakeFaceTrueSoul]

@OzalZarbaliyev : Yes you're write - you need to take care of these scenarios on writes, but MongoDB now provides a way to deal with these, Check this :: stackoverflow.com/a/62178861/7237613

[mstearn]

just be careful since that can't use indexes

[casey]

This doesn't really use indexes for what you really need the index for

[Alexandru R]

does this still work? looks like is not working for array elements

[Thilina Rubasingha]

db.test.find("this.value > 12") this is not work for me and I try with this db.test.find({$where: "this.value > 12"}) now its working

[whoami - fakeFaceTrueSoul]

If you get any issues while conversion first check if input from field is valid or not on all docs & also refer to documentation. Just in case if your value field has a mix of double & int then you can just use $toDouble instead of $toInt - But remember if you're using conversion & returning converted value then "10123" will look like int 10123 but is type double 10123 - since you've used $toDouble. So if you've string (double & int) mixed on same field & wanted to maintain types as is then use $cond to check if string has . then use $toDouble else $toInt..