Case insensitive search in mongo without regex?

Question

The question "how can I do a case insensitive find in mongo" seems to be mostly answered along the lines of "use a regex". This is fine if your search string is known in advance (/foo/), or is known not to contain regex operators (^.+*$/\-()[]...).

In my case, I want to do a case insensitive search on email addresses, which can contain dots and plusses. "Regex escaping" the string is certainly possible, but even if there was a standard "Regex.escapeString" function (which there isn't), it's already starting to feel difficult to explain and maintain for such a simple problem.

Is there a way to do a case insensitive match in mongo without using regex?

There is a reason why *"all the answers"* say that, and that is because unless you have a normalized case string version stored ( probably the best option ) then that is what you are doing. As for *"special characters"*, well you can always escape them, which is what "\" is for. So "\\" means the "string" "\". — Blakes Seven, Feb 25 '16 at 01:50
Thanks @BlakesSeven. That sounds like a great answer. (I've also reworded my opening hyperbolic sentence) — aaaidan, Feb 25 '16 at 01:54
Obviously this will not address all cases, but if you are saving something as all lowercase or all uppercase by default, the setter will address searches. For example, for states saved as FL, CA, NY, etc., using `set: state => state.toUpperCase()` will also cause your searches to be automatically capitalized. — SuperCodeBrah, Dec 28 '18 at 01:24
I have the same problem. A user wants to lookup a customer by email address and you want to do a case-insensitive search but you don't want to open up all the possible impacts of regex support such as open searches `.*` etc which can return millions of rows — Philluminati, Jun 27 '22 at 10:17

score 2 · Answer 1 · answered Sep 26 '21 at 17:11

You can use aggregate() to do it, instead of find() with $regex:

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$eq": [
          { "$toLower": "$email" },
          { "$toLower": "EXAMPLE+@gmail.com" }
        ]
      }
    }
  }
])

Working Example

score 0 · Answer 2 · answered Dec 05 '22 at 07:32

You can use the Case Insensitive Indexes:

db.users.createIndex(
    { type: 1 },
    { collation: { locale: 'en', strength: 2 } }
)

and query for users as follows:

db.users.find(
    { email: "aaaidan@example.com" }
).collation(
    { locale: 'en', strength: 2 }
)

...will give you results:

aaaidan@example.com
aaaiDan@example.com
AAAidan@example.com
...

The strength parameter is the level of comparison to perform. Corresponds to ICU Comparison Levels.

Case insensitive search in mongo without regex?

2 Answers2