MongoDB use index in regular expression query

Question

I am using the official C# MongoDB driver.

If I have an index on three elements {"firstname":1,"surname":1,"companyname":1} can I search the collection by using a regular expression that directly matches against the index value?

So, if someone enters "sun bat" as a search term, I would create a regex as follows (?=.\bsun)(?=.\bbat).* and this should match any index entries where firstname or surname or companyname starts with 'sun' AND where firstname or surname or companyname starts with 'bat'.

If I can't do it this way, how can I do it? The user just types their search terms, so I won't know which element (firstname, surname, companyname) each search term (sun or bat) refers to.

Tyler Brock · Accepted Answer · 2014-05-22 18:57:53Z

7

Update: for MongoDB 2.4 and above you should not use this method but use MongoDB's text index instead.

Below is the original and still relevant answer for MongoDB < 2.4.

Great question. Keep this in mind:

MongoDB can only use one index per query.
Queries that use regular expressions only use an index when the regex is rooted and case sensitive.

The best way to do a search across multiple fields is to create an array of search terms (lower case) for each document and index that field. This takes advantage of the multi-keys feature of MongoDB.

So the document might look like:

{
    "firstname": "Tyler",
    "surname": "Brock",
    "companyname": "Awesome, Inc.",
    "search_terms": [ "tyler", "brock", "awesome inc"]
}

You would create an index: db.users.ensureIndex({ "search_terms": 1 })

Then when someone searches for "Tyler", you smash the case and search the collection using a case sensitive regex that matches the beginning of the string:

db.users.find({ "search_terms": /^tyler/ })

What mongodb does when executing this query is to try and match your term to every element of the array (the index is setup that way too -- so it's speedy). Hopefully that will get you where you need to be, good luck.

Note: These examples are in the shell. I have never written a single line of C# but the concepts will translate even though the syntax may differ.

edited May 22, 2014 at 18:57

answered Dec 8, 2011 at 16:36

Tyler Brock

30.3k16 gold badges82 silver badges82 bronze badges

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

1 Comment

Go Dan Over a year ago

See MongoDB - Advanced Queries - Regular Expressions and How to use MongoRegex (MongoDB C# Driver) for more info.

Collectives™ on Stack Overflow

MongoDB use index in regular expression query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related