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
– Blakes Seven, Commented Feb 25, 2016 at 1:50
Thanks @BlakesSeven. That sounds like a great answer. (I've also reworded my opening hyperbolic sentence) — aaaidan
– aaaidan, Commented Feb 25, 2016 at 1: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
– SuperCodeBrah, Commented Dec 28, 2018 at 1: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
– Philluminati, Commented Jun 27, 2022 at 10:17

NeNaD · Accepted Answer · 2021-09-26 17:11:03Z

2

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

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$eq": [
          { "$toLower": "$email" },
          { "$toLower": "[email protected]" }
        ]
      }
    }
  }
])

Working Example

answered Sep 26, 2021 at 17:11

NeNaD

20.8k11 gold badges64 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ikar Pohorský · Accepted Answer · 2022-12-05 07:32:27Z

0

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: "[email protected]" }
).collation(
    { locale: 'en', strength: 2 }
)

...will give you results:

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

answered Dec 5, 2022 at 7:32

Ikar Pohorský

5,0897 gold badges43 silver badges60 bronze badges

Collectives™ on Stack Overflow

Case insensitive search in mongo without regex?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related