CODEWITHSUNDEEP
mongodb
Meredian
Meredian

Reputation: 970

How to do MongoDB case-insensitive search for single field only?

So, case is simple. I want to have case-insensitive search on a field name. Luckily, latest Mongo brings us concept of collation & collation indexes.

That will easily work with something like:

db.users.find({ name: 'John' }).collation({ locale: 'en', strength: 2 })

And I have backing index for that fields with given collation strength - very efficient.

However, if I want to limit search within some stricter scope, e.g. having some companyId constant, things become a bit messy:

db.users.find({ companyId: 'some-company-id', name: 'John' }).collation({ locale: 'en', strength: 2 })

That collation applies both for name AND some-company-id. So it will look as well for SOME-COMPANY-ID and SoMe-CoMpAnY-iD, which may lead to undesired collisions, and since all cases matches, any supporting index on companyId does not help.

My question is: How can I effectively search with only 1 string field being case-insensitive?

Possible answer could be something like regex-search:

db.users.find({ companyId: 'some-company-id', name: /^John$/i })

...but unfortunately this search can't be supported by any index.

Upvotes: 6

Views: 576

Answers (1)

Meredian
Meredian

Reputation: 970

So far, after long discussions, googling, trying to reach mongoDB team, we decided that there's nothing more reliable & transparent then supporting separate field for indexed search, like name_lowercase.

  1. It's very transparent.
  2. You can manage invariant in a way you want.
  3. But you have to support it in your codebase.
  4. Doubled data & index requires more disk space & network bandwidth.

But if you actually need to do it for one specific case, do it this way.

Upvotes: 2

Related Questions