You can query MongoDB similarly to the “LIKE” operator in SQL, though MongoDB doesn’t have a direct operator, however, you can achieve the same functionality by using the alternative query methods. You can use $regex (regular expression), full-text search, etc. In this blog, you will learn how to perform LIKE searches in MongoDB efficiently.
Table of Contents:
What is “LIKE” in MongoDB?
In SQL, the LIKE operator is used to find a similar pattern in a column. But in MongoDB, you cannot use the LIKE function, so instead you can use $regex, $text, etc.
Syntax:
SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';
Why Use Regex Instead of LIKE?
In MongoDB, $regex is the closest or most similar function that can be used instead of LIKE in SQL. It is more powerful. It can easily find prefixes, suffixes, and full-text searches. You can use it to search for any string with the help of keywords or the start and end letters of the string. $regex will find the string based on the key given.
Syntax:
{ "field": { "$regex": "pattern", "$options": "i" } }
Steps to Connect the MongoDB Server to Colab
Step 1. Firstly, set up an account at MongoDB Atlas.
Step 2. Connect your database by registering with your username and your password.
Step 3. Create a cluster of your own and connect the database.
Step 4. Save the URI generated and change <mypassword> into your password and username.
Step 5. Now, open Google Colab and type:
!pip install pymongo
import pymongo
After the packages are installed, proceed to the next step.
Step 6. Connect the MongoDB with Colab
You can connect to MongoDB with Colab using the following code.
client = pymongo.MongoClient("mongodb+srv://username:<mypassword>@cluster0.ru6wk.mongodb.net/?retryWrites=true&w=majority&appName=Cluster0")
db = client["test_db"]
collection = db["test_collection"]
Step 7. Insert the data into the database.
data = [
{"name": "Kiran", "email": "[email protected]"},
{"name": "Priya", "email": "[email protected]"},
{"name": "Savee", "email": "[email protected]"},
{"name": "Alex", "email": "[email protected]"},
{"name": "Jagan", "email": "[email protected]"},
{"name": "Hari", "email": "[email protected]"}
]
collection.insert_many(data)
print("Data inserted successfully!")
If the data was inserted successfully. It means that the database has been connected successfully.
Methods to Query MongoDB with LIKE
Various methods can be used to find or get the matching string as an alternative to the LIKE operator in SQL. You can use the regular expression ($regex), $text, $regexMatch, etc.
Method 1: $regex (Regular Expressions) in MongoDB
The $regex function in MongoDB is an alternative for the LIKE operator in SQL. It partially matches the case-insensitive searches and pattern matches. It supports characters like ^, *, and $. It is very flexible but slower while handling larger datasets.
Example:
query = {"name": {"$regex": "Hari", "$options": "i"}}
results = collection.find(query)
for doc in results:
print(doc)
Output:
Explanation: Here, the $regex found the match that matches the string name “Hari.”
To find the name containing “pri”:
query = {"name": {"$regex": "pri", "$options": "i"}}
results = collection.find(query)
for doc in results:
print(doc)
Output:
Explanation: Here, the regex fetched all the data that has the “Pri” string present.
To find a name ending with “an”
query = {"name": {"$regex": "an$", "$options": "i"}}
results = collection.find(query)
for doc in results:
print(doc)
Output:
Explanation: Here, the regex function fetched the names that have “an” in them.
Method 2: Using $text (Full-Text Search) in MongoDB
In MongoDB, the server has a full-text search, which uses the $text operator to search the strings. Unlike $regex, it searches for the full word instead of a substring. It works well on large datasets. It cannot perform its operation on substrings, it needs the full word to search for the match.
Example:
collection.create_index([("name", "text")])
query = {"$text": {"$search": "Savee"}}
results = collection.find(query)
Output:
Explanation: The $text operator searched and found the name “Savee” in the data.
Method 3: Using $regexMatch (Aggregation Framework) in MongoDB
The $regexMatch in MongoDB is used to search a pattern inside aggregated pipelines. It will search the data based on patterns, and it’s very useful while filtering the data dynamically. It is very efficient for complex filtering.
Example:
pipeline = [
{
"$match": {
"$expr": {
"$regexMatch": {"input": "$email", "regex": "98", "options": "i"}
}
}
}
]
results = collection.aggregate(pipeline)
for doc in results:
print(doc)
Output:
Explanation: Here, the $regexMatch used the pattern of an email that has “98” and fetched the data.
| Methods | Use Case | Performance |
| $regex | It is very flexible and efficient. It can match data with substrings. | It is slower on large datasets. |
| $text | It is flexible and can handle complex queries. | It cannot match substrings. It needs a full word to match. |
| $regexMatch | It can filter the aggregation and dynamic data efficiently. | It is slower as it needs to filter and cannot handle large datasets. |
Real-World Examples
Case 1: To find emails of people who have numbers between 4 to 6 in their email.
Example:
users = [
{"name": "Alice", "email": "[email protected]"},
{"name": "Bob", "email": "[email protected]"},
{"name": "Charlie", "email": "[email protected]"},
{"name": "David", "email": "[email protected]"},
{"name": "Eve", "email": "[email protected]"},
{"name": "Adam", "email": "[email protected]"},
{"name": "Annie", "email": "[email protected]"},
{"name": "Andrew", "email": "[email protected]"}
]
collection.insert_many(users)
print("New dataset inserted successfully!")
query = {"email": {"$regex": "[4-6]"}}
results = collection.find(query)
for doc in results:
print(doc)
Output:
Explanation: Here, the $regex fetched the people who have 4-6 numbers in their email ID.
Case 2: To find people’s email, which has 4@example in it.
Example:
users = [
{"name": "Rohan", "email": "[email protected]"},
{"name": "Meera", "email": "[email protected]"},
{"name": "Sahil", "email": "[email protected]"},
{"name": "Nisha", "email": "[email protected]"},
{"name": "Vikram", "email": "[email protected]"},
{"name": "Fatima", "email": "[email protected]"},
{"name": "Arjun", "email": "[email protected]"},
{"name": "Sneha", "email": "[email protected]"}
]
collection.insert_many(users)
print("New dataset inserted successfully!")
query = {
"$expr": {
"$regexMatch": {"input": "$email", "regex": "4@example\\."}
}
}
results = collection.find(query)
for doc in results:
print(doc)
Output:
Explanation: Here, the $expr and $regex functions fetched the people’s emails, which have 4@example in them.
Case 3: To find the data that has the word “corporate” in it.
Example:
users = [
{"name": "Rohan", "email": "[email protected]"},
{"name": "Meera", "email": "[email protected]"},
{"name": "Sahil", "email": "[email protected]"},
{"name": "Nisha", "email": "[email protected]"},
{"name": "Vikram", "email": "[email protected]"},
{"name": "Fatima", "email": "[email protected]"},
{"name": "Arjun", "email": "[email protected]"},
{"name": "Sneha", "email": "[email protected]"}
]
collection.insert_many(users)
print("New dataset inserted successfully!")
# Create a text index on the email field
collection.create_index([("email", "text")])
query = {"$text": {"$search": "corporate"}}
results = collection.find(query)
for doc in results:
print(doc)
Output:
Explanation: Here, the $text function matched the word “corporate” and returned the output.
Conclusion
In the MongoDB server, there is no operator called LIKE as in SQL. But it has similar functionality that has the same performance as the LIKE operator. It is a $regex operator that allows matching the substrings flexibly, $text that can match the complex queries efficiently, but it cannot match a substring. Instead, it matches the full word. $regexMatch is for pattern-matching aggregate pipeline filtering and can match data dynamically. By understanding their performance and use cases, you can use these methods efficiently. In this blog, you have understood the functionality of the $regex, $text, and $regexMatch work as an alternative for the “LIKE” operator in SQL.
Take your skills to the next level by enrolling in our SQL Training Course today and gain hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.
How to Query MongoDB with “like”? – FAQs
1. How to use ‘like’ in a MongoDB query?
Use $regex for pattern matching, e.g., { “field”: { “$regex”: “pattern”, “$options”: “i” } }.
2. How to use MongoDB like SQL?
MongoDB uses JSON-based queries; for SQL-like LIKE, use $regex, and for filtering, use operators like $match or $text.
3. What is the equivalent of like in MongoDB?
The equivalent of LIKE in MongoDB is $regex.
4. How to escape special characters in a MongoDB query?
Use a backslash (\) before special characters in regex, e.g., “pattern\\.example” to match “pattern.example”.
5. How to escape in the MongoDB connection string?
Replace special characters in the username or password using percent-encoding.