Introduction
In SQL, the ‘LIKE’ and ‘NOT LIKE’ operators are widely used to search for a specified pattern in a column. Since MongoDB is a NoSQL database, it does not support these operators directly. However, MongoDB provides a powerful alternative through the use of regular expressions (regex). In this tutorial, we will explore how to mimic the ‘LIKE’ and ‘NOT LIKE’ operators in MongoDB by using regex.
Understanding Regex in MongoDB
Regular expressions (regex) are powerful tools for pattern matching in strings. MongoDB’s regex implementation allows you to perform complex query operations similar to the ‘LIKE’ operator in SQL.
Basic Pattern Matching
To start, let’s look at a basic example. Assume we have a ‘users’ collection with a field ‘name’. To find all users with names containing ‘john’, we can use the following query:
db.users.find({ "name": /john/i })The ‘i’ flag passed in the regex makes the search case-insensitive, similar to SQL’s ‘LIKE’.
Exact Match
MongoDB can also perform exact case-sensitive matches without using regular expressions:
db.users.find({ "name": "John" })This query will return users with the name ‘John’ and not ‘john’, ‘JOHN’, etc.
Using Regex to Mimic ‘LIKE’
In this section, we’ll demonstrate how to apply regex in ways that resemble the usage of ‘LIKE’ in SQL.
Match Anywhere in String
To replicate the ‘%term%’ pattern in ‘LIKE’, use regex as follows:
db.users.find({ "name": /alex/ })This will match any document where ‘name’ contains ‘alex’ in any position, e.g., ‘Alexander’, ‘Alexis’.
Match Start of String
To replicate ‘term%’ in SQL’s ‘LIKE’, meaning the term is at the start of the string, use the regex anchor ‘^’:
db.users.find({ "name": /^alex/ })This will match any ‘name’ starting with ‘alex’, such as ‘Alex’, but not ‘Dealex’.
Match End of String
To replicate ‘%term’ with ‘LIKE’, where the term is at the end of the string, use the regex anchor ‘$’:
db.users.find({ "name": /alex$/ })This will find names ending with ‘alex’, such as ‘Dealex’, but not ‘Alexei’.
Using Regex to Mimic ‘NOT LIKE’
Now let’s see how to invert the regex match to form a ‘NOT LIKE’ operation.
Exclude Pattern Anywhere in String
We can achieve a ‘NOT LIKE’ equivalent using the ‘$not’ and regex:
db.users.find({ "name": { $not: /alex/ } })This will exclude any documents where ‘name’ contains ‘alex’.
Exclude Patterns at Start or End of String
Similar to ‘LIKE’, we can exclude patterns that either start or end with a certain term:
db.users.find({ "name": { $not: /^alex/ } })This will exclude names that start with ‘alex’. To exclude names that end with a term, use:
db.users.find({ "name": { $not: /alex$/ } })This query will not match any names ending in ‘alex’.
Advanced Regex Usage in MongoDB
As we become more familiar with regex, advanced patterns can be constructed to cover more complex ‘LIKE’ statements.
Match Multiple Criteria
To combine multiple patterns, use the ‘|’, which represents ‘OR’:
db.users.find({ "name": /(alex|john)/ })Match names containing either ‘alex’ or ‘john’.
Exclude Multiple Criteria
Just as with ‘OR’, multiple exclusions can be specified using a combination of ‘$not’ and the ‘|’ operator:
db.users.find({ "name": { $not: /(alex|john)/ } })This query excludes any document with ‘name’ containing either ‘alex’ or ‘john’.
Other Considerations
Besides pattern matching, it’s important to consider indexes, performance, and using Mongos native query operators to achieve optimal search operations. Regular expressions can be resource-intensive and, if used carelessly, may lead to performance bottlenecks.
Conclusion
Through regular expressions, MongoDB offers flexibility in querying for pattern matches, akin to SQL’s ‘LIKE’ and ‘NOT LIKE’ operators. As shown through numerous examples, regex can query for substrings, match patterns at the string boundaries, and perform complex, compounded pattern exceptions. Remember to use indexes appropriately for better performance on text searches.