Case insensitive query Regex (startsWith)

Working with Data
#1

Hello there,

using mongo since approx. 2 months and so far I’m quite happy, however we stumbled upon our first real problem.
One of our main use cases is a query e.g. by name. By default, the server should search case insensitively and with startsWith, so I’m doing sg. like this:

{"name.family" : /^christiansen/i}

While this delivers the correct results, it is very inefficient. This is stated in the documentation and I can validate this by looking at the explain-stats (it uses the index but scans ALL documents).

An index with a collation strength of 1 or 2 does not help my case, because then I still loose the startsWith capability.

Normalizing data on the way in is not really an option, unfortunately.

I’m quite baffled this does not really work, I think i must be missing something. That is something that is very easy to do in most common sql environments by using a simple LIKE qiery along with a functional index (toLowerCase or sg. similar).

Thank you!

1 Like
#2

Hi @Johannes_B,

One of the possible solutions to your problem is a use of a text index in a combination of an additional $and expression.

https://docs.mongodb.com/manual/core/index-text/

There are two possible ways to write this query once you indexed the field:

  1. Using the aggregation command you have 2 stages where first run the $search insesetive on the term “christiansen”, followed by a second $match on {"name.family" : /^christiansen/}.
  2. Use a find and do a $search with a $and on{"name.family" : /^christiansen/} .

This combination solve the insesetive search and the startWith in 2 efforts

Let me know if that helps.

Pavel

#3

Hello Pavel,

thank you for your quick answer!
Not sure I understand it though :wink: Maybe my example was a little misleading.

In short, the term /^chri/ should deliver “Christiansen” in the resultset.

For solution
1: As far as I understood the documentation, regex with a text search does not work?
2: The collation indices don’t go well with regex searches, do they?

It would be great if you could give me a short example.

Thank you!

#4

Hi @Johannes_B,

Ok thank you for explaining in more detail. My solution was relevant for the first example when the full name is provided. This is since text searches are based on words or delimited words. Therefore with a text index you cant search a partial expression.

I suggest exploring our Atlas search which comes to solve those problems on Atlas:
https://docs.atlas.mongodb.com/atlas-search/

https://docs.atlas.mongodb.com/reference/atlas-search/regex/

The original example was considering the following documents:

{ _id: ObjectID("5f2949ebbdee40880a3db0c0"),
  name: { first: 'David', family: 'Christiansen' } }
{ _id: ObjectID("5f294e2bbdee40880a3db0c1"),
  name: { first: 'David', family: 'Owen-Christiansen' } }

Where the following aggregation will find the needed document:

db.users.aggregate([{$match: {
  $text :{ "$search" : "christiansen" }
}}, {$match: {
  "name.family" : /^christiansen/i
}}])


[ 
    name: { first: 'David', family: 'Christiansen' } } ]

This will utilize the text index.

Best regards
Pavel

1 Like
#5

Ok thank you Pavel!

That’s a shame, we can not consider running in an atlas cloud due to project constraints.

Thank you nevertheless, time to get creative then :wink: Bye!