MongoDB $gt/$lt 运算符，价格存储为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18039358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MongoDB $gt/$lt operators with prices stored as strings
提问by JVG
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}}.
但我没有得到任何结果。我也试过了{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?
是因为价格在数据库中存储为字符串而不是数字吗？有没有解决的办法？
采纳答案by Jayz
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:
如果您打算将 $gt 与字符串一起使用，则必须使用正则表达式，这在性能方面不是很好。只需创建一个包含价格数值的新字段或将此字段类型更改为 int/double 会更容易。javascript 版本也应该可以工作，如下所示：
db.products.find("this.price > 30.00")
as js will convert it to number before use. However, indexes won't work on this query.
因为 js 会在使用前将其转换为数字。但是，索引不适用于此查询。
回答by Derick
$gtis an operator that can work on any type:
$gt是一个可以处理任何类型的运算符：
db.so.drop();
db.so.insert( { name: "Derick" } );
db.so.insert( { name: "Jayz" } );
db.so.find( { name: { $gt: "Fred" } } );
Returns:
返回：
{ "_id" : ObjectId("51ffbe6c16473d7b84172d58"), "name" : "Jayz" }
If you want to compare against a number with $gtor $lt, then the value in your document also needs to be a number. Types in MongoDB are strict and do notauto-convert like they f.e. would do in PHP. In order to solve your issue, make sure you store the prices as numbers (floats or ints):
如果要使用$gt或与数字进行比较$lt，则文档中的值也必须是数字。MongoDB 中的类型是严格的，并且不会像在 PHP 中那样自动转换。为了解决您的问题，请确保将价格存储为数字（浮点数或整数）：
db.so.drop();
db.so.insert( { price: 50.40 } );
db.so.insert( { price: 29.99 } );
db.so.find( { price: { $gt: 30 } } );
Returns:
返回：
{ "_id" : ObjectId("51ffbf2016473d7b84172d5b"), "price" : 50.4 }
回答by Xavier Guihot
Starting Mongo 4.0, there is a new $toDoubleaggregation operator which converts from various types to double (in this case from a string):
开始Mongo 4.0，有一个新的$toDouble聚合运算符，它可以从各种类型转换为双精度（在本例中为字符串）：
// { price: "300.00" }
// { price: "4.2" }
db.collection.find({ $expr: { $gt: [{ $toDouble: "$price" }, 30] } })
// { price: "300.00" }
回答by TomoMiha
If you have newer version of mongodb then you can do this:
如果您有更新版本的 mongodb，则可以执行以下操作：
$expr: {
$gt: [
{ $convert: { input: '$price', to: 'decimal' } },
{ $convert: { input: '0.0', to: 'decimal' } }
]
}
$expr operator: https://docs.mongodb.com/manual/reference/operator/query/expr/
$expr 运算符：https: //docs.mongodb.com/manual/reference/operator/query/expr/
$convert opetator: https://docs.mongodb.com/manual/reference/operator/aggregation/convert/index.html
$convert 运算符：https://docs.mongodb.com/manual/reference/operator/aggregation/convert/index.html
回答by donquixote
Alternatively you can convert the values to Int, as per: http://www.quora.com/How-can-I-change-a-field-type-from-String-to-Integer-in-mongodb
或者，您可以将值转换为 Int，如：http: //www.quora.com/How-can-I-change-a-field-type-from-String-to-Integer-in-mongodb
var convert = function(document){
var intValue = parseInt(document.field, 10);
db.collection.update(
{_id:document._id},
{$set: {field: intValue}}
);
}
db.collection.find({field: {$type:2}},{field:1}).forEach(convert)
