Regular expressions (regex) provide a way to match strings against a pattern so that your searches are "fuzzy" rather than exact. MongoDB comes with a regex engine built in so you can dig up documents even if you don't know exactly what the exact Field value is that you're looking for. In today's blog we'll learn how to use regexes in MongoDB, using Navicat for MongoDB.
Basic Syntax
MongoDB provides the regex operator for searching strings in the collection. The following example shows how it's done, using the Sakila Sample Database:
Let's say that we wanted to find movies with actors named "DAN", "DANNY", or even "DANIEL". Here's a statement to do that:
db.film_list.find({actors: {$regex: "DAN" }})
Once the command is executed successfully, the following Output will be shown:
We can simplify the statement by removing the "$regex:" qualifier and enclosing the search string within forward slashes (/) instead of quotes, as forward slashes denote a regex:
db.film_list.find({ actors: /DAN/ })
Searching with Multiple Search Strings
We can include more than on search string to match various combinations. Let's say that we wanted to find movies with Carrie-Anne Moss by matching "Carrie Moss" or "moss carrie-anne". Here's a statement to do that:
db.film_list.find(
{ actors: { $elemMatch: { actors: /Moss/i, actors: /carrie-anne/i } } }
);
$elemMatch will return those records, where an array element matches both criterias. In contrast, using a plain $and (which is the default for a list of criterias) without $elemMatch would return movies with "Carrie-Anne Moss", but also those where "Sandra Moss" and "Carrie-Anne Fisher" are featured. That would be more of a superset of the information we want to retrieve. Also note the "i" flag; it makes the regex case-insensitive. It's useful for searches that are entered by the user, because we can't rely on the user to use mixed case.
The options Parameter
We can also provide additional instructions to our regexes via the options parameter.
- i: Case insensitivity to match upper and lower cases.
- m: For patterns that include anchors (i.e. ^ for the start, $ for the end), match at the beginning or end of each line for strings with multiline values. Without this option, these anchors match at beginning or end of the string.
- x: Extended capability to ignore all white space characters in the $regex pattern unless escaped or included in a character class. Unlike other flags, this one requires $regex with $options syntax
- s: Allows the dot character (.) to match all characters including newline characters.
Conclusion
The $regex operator provides an easy means of pattern matching in MongoDB. For best results, make sure that the document fields that you are searching are indexed. That way, the query will use make use of indexed values to match the regular expression. This makes the search very fast as compared to the regular expression scanning the whole collection.
If you'd like to learn more about Navicat for MongoDB, please visit the product page. Do you work with many database types? Navicat Premium 12.1 also supports MongoDB!
