Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB $gt/$lt operators with prices stored as strings

Tags:

mongodb

I'm trying to query my database for prices greater than/less than a user specified number. In my database, prices are stored like so:

{price: "300.00"} 

According to the docs, this should work:

db.products.find({price: {$gt:30.00}}).pretty() 

But I get no results returned. I've also tried {price: {$gt:30}}.

What am I missing here?

It it because the prices are stored as a string rather than a number in the DB? Is there a way around this?

like image 414
JVG Avatar asked Aug 04 '13 03:08

JVG


People also ask

What is GTE and LT in MongoDB?

Comparison Operators Matches if values are greater than the given value. $lt. Matches if values are less than the given value. $gte. Matches if values are greater or equal to the given value.

Is $gt for greater than or equal to the value?

Definition. $gt selects those documents where the value of the field is greater than (i.e. > ) the specified value . For most data types, comparison operators only perform comparisons on fields where the BSON type matches the query value's type. MongoDB supports limited cross-BSON comparison through Type Bracketing.

How do I find the length of a string in MongoDB?

As for the logical condition, there are String Aggregation Operators that you can use $strLenCP operator to check the length of the string. If the length is $gt a specified value, then this is a true match and the document is "kept".

How does the $in operator work MongoDB?

For comparison of different BSON type values, see the specified BSON comparison order. If the field holds an array, then the $in operator selects the documents whose field holds an array that contains at least one element that matches a value in the specified array (for example, <value1> , <value2> , and so on).


1 Answers

If you intend to use $gt with strings, you will have to use regex, which is not great in terms of performance. It is easier to just create a new field which holds the number value of price or change this field type to int/double. A javascript version should also work, like so:

db.products.find("this.price > 30.00") 

as js will convert it to number before use. However, indexes won't work on this query.

like image 179
Jayz Avatar answered Sep 19 '22 17:09

Jayz