MongoDB doesn’t support SQL like operator to query the document, however, you can use the $regex operator with db.collection.find() method to get a similar result. The $regex operator is used to perform pattern matching against a string. Furthermore, we can search for documents in the collection that match a particular pattern.
This article will explain how to perform like queries in MongoDB using the $regex operator with examples.
Let’s insert some documents into the MongoDB collection titled Info and will use this collection to explain $regex that gives you a similar result to SQL like operator.
# Create collection
db.Info.insertMany([
{ "id": 01, name:"Kim David", "subject": "Python" , "status": "Teacher"},
{ "id": 02, name:"Caroline", "subject": "Javascript" , "status": "Student"},
{ "id": 03, name:"Emily", "subject": "Kotlin" , "status": "Senior Teacher"},
{ "id": 04, name:"Leo David", "subject": "Docker" , "status": "Student"},
{ "id": 05, name:"Klaus", "subject": "Scala" , "status": "Senior Teacher"},
])
1. Using Regular Expression to get SQL like Results
MongoDB uses regular expressions which are more powerful than SQL “LIKE”. With regular expressions, you can create any pattern that you imagine and get SQL-like results in MongoDB.
1.1 Syntax
Following is the syntax of the regular expression
# Using regular expression
db.collection.find({field: /pattern/})
1.2 Example
The following MongoDB example uses the regular expression to get the result from the collection where the subject contains a string like ‘%a%‘
# Like '%a%' using regular expression
db.Info.find({subject: /a/})
2. Using $regex Operator
Similarly, you can also use the $regex operator to get results similar to SQL like. This operator is similar to using the regular expression explained in the above section.
2.1 Syntax of $regex Operator
The $regex operator takes a regular expression as its parameter, which is defined as a pattern that matches a set of strings.
Following is the basic syntax of the $regex operator.
# Syntax using $regex operator
db.collection.find({ field: { $regex: /pattern/ } }
In the above syntax, the field is the name of the field that we want to search for a pattern, and the pattern is the regular expression that we want to match against the field’s value.
2.2 Example
Following is an example of using $regex operator.
# Example of $regex operator
db.Info.find({ subject: { $regex: /a/ } }
You can get more details about the $regex operator here.
3. Finding Documents that Begin with a Specific String
Here, we want to search for all documents where the field starts with the given string. We have called the $regex operator that uses the ^ character to indicate the beginning of the string, followed by the pattern Python. This will match all documents where the subject field starts with the string MongoDB.
# Using ^ character
db.Info.find({ subject: { $regex: /^Python/ } })
The expected output is:
4. Find that Begins with a Specific Substring
Now, we would like to search for all documents where the field contains the defined substring. For this, we use the pattern script to match all documents where the subject field contains the substring script.
# Using script pattern
db.Info.find({ subject: { $regex: /script/ } })
The resultant documents are:
5. Find that End with a Specific String:
Moreover, we can search for all documents whose field ends with the assigned string. In the following query, we utilize the $ character to indicate the end of the string, preceded by the pattern line. This will match all documents where the field name ends with the string line.
# Using $ character
db.Info.find({ name: { $regex: /line$/ } })
The output fetched from the above query is:
6. Find that Begin with a Specific Letter
Next, we want to search for those documents where the field starts with the given letter. To do this, we have applied the query that uses the ^ symbol to indicate the beginning of the string, followed by the pattern D. The regex pattern will match all documents where the field subject begins with the letter D.
# ^D pattern usage
db.Info.find({ subject: { $regex: /^D/ } })
We got the following output:
7. Find that Contain a Specific Word
Here is the case where we want to search for the documents where the field contains the specific word. However, we want to make sure that we only match the whole word and not any substring that contains the word.
For this, we used the \b anchor to match the word boundary before and after the pattern Student. We also used i option to make the query case-insensitive so it will match both Student and student. We will match all documents where the field status contains the whole word Student and not any substring that contains the word.
# Using \b anchor
db.Info.find({ status: { $regex: /\bStudent\b/i } })2
The results obtained are:
8. Finding Documents that Contain Multiple Words
There might be a case when we would like to search for all documents whose field contains more than one word. In this query, we have the | operator to match either the pattern Kim or David for the name field.
# Using | operator
db.Info.find({ name: { $regex: /kim|David/ } })
The matched documents output:
9. Conclusion
We have seen different examples of using like query in MongoDB expressions. With this operator, we can search for documents in the collection that match a particular pattern. MongoDB doesn’t support like operator hence the $regex operator is used to execute pattern matching against a string while SQL typically uses the LIKE operator.