如何在Mongodb上进行不区分大小写的查询?

var thename = 'Andrew';
db.collection.find({'name':thename});

How do I query case insensitive? I want to find result even if "andrew";

如何查询大小写不敏感?我想要找到结果，即使“安德鲁”;

9 个解决方案

#1

Chris Fulstow's solution will work (+1), however, it may not be efficient, especially if your collection is very large. Non-rooted regular expressions (those not beginning with ^, which anchors the regular expression to the start of the string), and those using the i flag for case insensitivity will not use indexes, even if they exist.

Chris Fulstow的解决方案是有效的(+1)，但是，它可能不是有效的，尤其是当你的收藏非常大的时候。内面正则表达式(那些没有从^开始,主持人开始的正则表达式的字符串),和那些使用我国旗不区分大小写不会使用索引,即使他们存在。

An alternative option you might consider is to denormalize your data to store a lower-case version of the name field, for instance as name_lower. You can then query that efficiently (especially if it is indexed) for case-insensitive exact matches like:

您可以考虑的另一种选择是将数据非规范化，以存储name字段的小写版本，例如name_lower。然后，您可以对不区分大小写的精确匹配进行高效的查询(特别是当它被索引时)，比如:

db.collection.find({"name_lower": thename.toLowerCase()})

Or with a prefix match (a rooted regular expression) as:

或与前缀匹配(根状正则表达式)为:

db.collection.find( {"name_lower":
    { $regex: new RegExp("^" + thename.toLowerCase(), "i") } }
);

Both of these queries will use an index on name_lower.

这两个查询都将在name_lower上使用索引。

#2

You'd need to use a case-insensitive regular expression for this one, e.g.

您需要使用一个不区分大小写的正则表达式。

db.collection.find( { "name" : { $regex : /Andrew/i } } );

To use the regex pattern from your thename variable, construct a new RegExp object:

要使用来自您的thename变量的regex模式，请构造一个新的RegExp对象:

var thename = "Andrew";
db.collection.find( { "name" : { $regex : new RegExp(thename, "i") } } );

Update: For exact match, you should use the regex "name": /^Andrew$/i. Thanks to Yannick L.

更新:精确匹配,您应该使用正则表达式的“名称”:/ ^安德鲁美元/我。由于亚尼克·L。

#3

I have solved it like this.

我像这样解出来的。

 var thename = 'Andrew';
 db.collection.find({'name': {'$regex': thename,$options:'i'}});

If you want to query on 'case-insensitive exact matchcing' then you can go like this.

如果你想查询“不区分大小写的精确匹配”，你可以这样做。

var thename =  '^Andrew$';
db.collection.find({'name': {'$regex': thename,$options:'i'}});

#4

I just solved this problem a few hours ago.

我几个小时前刚刚解决了这个问题。

var thename = 'Andrew'
db.collection.find({ $text: { $search: thename } });

• Case sensitivity and diacritic sensitivity are set to false by default when doing queries this way.
• 在以这种方式执行查询时，大小写敏感性和变音符敏感性默认设置为false。

You can even expand upon this by selecting on the fields you need from Andrew's user object by doing it this way:

你甚至可以通过在Andrew的用户对象中选择你需要的字段来扩展它:

db.collection.find({ $text: { $search: thename } }).select('age height weight');

Reference: https://docs.mongodb.org/manual/reference/operator/query/text/#text

参考:https://docs.mongodb.org/manual/reference/operator/query/text/文本

#5

MongoDB 3.4 now includes the ability to make a true case-insensitive index, which will dramtically increase the speed of case insensitive lookups on large datasets. It is made by specifying a collation with a strength of 2.

MongoDB 3.4现在可以生成一个真正的不区分大小写的索引，这将显著提高大小写不区分查找的速度。它是通过指定强度为2的排序规则来实现的。

Probably the easiest way to do it is to set a collation on the database. Then all queries inherit that collation and will use it:

可能最简单的方法是在数据库上设置排序规则。然后所有查询都继承该排序规则并将使用它:

db.createCollection("cities", { collation: { locale: 'en_US', strength: 2 } } )
db.names.createIndex( { city: 1 } ) // inherits the default collation

You can also do it like this:

你也可以这样做:

db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});

And use it like this:

像这样使用它:

db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});

This will return cities named "new york", "New York", "New york", etc.

这将返回命名为“纽约”、“纽约”、“纽约”等的城市。

For more info: https://jira.mongodb.org/browse/SERVER-90

更多信息:https://jira.mongodb.org/browse/server - 90

#6

The following query will find the documents with required string insensitively and with global occurrence also

下面的查询将会不敏感地找到需要字符串的文档，并且还会发现全局事件。

db.collection.find({name:{
                             $regex: new RegExp(thename, "ig")
                         }
                    },function(err, doc) {
                                         //Your code here...
                  });

#7

You can use Case Insensitive Indexes:

您可以使用不区分大小写的索引:

The following example creates a collection with no default collation, then adds an index on the name field with a case insensitive collation. International Components for Unicode

下面的示例创建一个没有默认排序的集合，然后在name字段上添加一个不区分大小写的排序。国际Unicode组件

/*
* strength: CollationStrength.Secondary
* Secondary level of comparison. Collation performs comparisons up to secondary * differences, such as diacritics. That is, collation performs comparisons of 
* base characters (primary differences) and diacritics (secondary differences). * Differences between base characters takes precedence over secondary 
* differences.
*/
db.users.createIndex( { name: 1 }, collation: { locale: 'tr', strength: 2 } } )

To use the index, queries must specify the same collation.

要使用索引，查询必须指定相同的排序规则。

db.users.insert( [ { name: "Oğuz" },
                            { name: "oğuz" },
                            { name: "OĞUZ" } ] )

// does not use index, finds one result
db.users.find( { name: "oğuz" } )

// uses the index, finds three results
db.users.find( { name: "oğuz" } ).collation( { locale: 'tr', strength: 2 } )

// does not use the index, finds three results (different strength)
db.users.find( { name: "oğuz" } ).collation( { locale: 'tr', strength: 1 } )

or you can create a collection with default collation:

或者你也可以创建一个默认排序的集合:

db.createCollection("users", { collation: { locale: 'tr', strength: 2 } } )
db.users.createIndex( { name : 1 } ) // inherits the default collation

#8

To find case-insensitive literals string:

查找不区分大小写的字串:

Using regex (recommended)

db.collection.find({
    name: {
        $regex: new RegExp('^' + name.replace(/[-\/\\^$*+?.()|[\]{}]/g, '\\$&') + '$', 'i')
    }
});

Using lower-case index (faster)

db.collection.find({
    name_lower: name.toLowerCase()
});

Regular expressions are slower than literal string matching. However, an additional lowercase field will increase your code complexity. When in doubt, use regular expressions. I would suggest to only use an explicitly lower-case field if it can replace your field, that is, you don't care about the case in the first place.

正则表达式比字符串匹配慢。但是，一个附加的小写字段将增加代码的复杂性。当有疑问时，使用正则表达式。我建议只使用一个显式的小写字段，如果它可以替换字段，也就是说，您首先不关心这个情况。

Note that you will need to escape the name prior to regex. If you want user-input wildcards, prefer appending .replace(/%/g, '.*') after escaping so that you can match "a%" to find all names starting with 'a'.

注意，您将需要在regex之前避免名称。如果您希望用户输入通配符，请在转义后添加.replace(/%/g， '.*')，以便您可以匹配“a%”以查找以“a”开头的所有名称。

#9

An easy way would be to use $toLower as below.

一个简单的方法是使用$toLower如下。

db.users.aggregate([
    {
        $project: {
            name: { $toLower: "$name" }
        }
    },
    {
        $match: {
            name: the_name_to_search
        }
    }
])