MongoDB Regex Like Query pattern – II
Today in this article, we shall see how to write MongoDB Regex Like query ( resembles SQL ‘like’ query) using regex pattern with case sensitive and insensitive search with examples.
Today in this article, we will cover below aspects,
I shall be explaining the queries using the like query in MongoDB Shell. If you are interested to refer a mongo query without Regex, please refer to the article below,
I have a sample MongoDB document below in one of the collections. Here we shall be trying to search all the documents using a MongoDB-like query
[
{
"_id": "5db5a4476997188b2722c820",
"Name": "Design Patterns",
"Price": 54.93,
"Category": "Computers",
"Author": "Ralph Johnson"
},
{
"_id": "5ff50353a29ce9564c2724e2",
"Name": "Design Patterns",
"Price": 54.93,
"Category": "Computers",
"Author": "Mike Casper"
},
{
"_id": "5ff503cda29ce9564c2724e3",
"Name": "Design Patterns",
"Price": 54.93,
"Category": "Computers",
"Author": "TheCodeBuzz"
}
{
"_id": "45b5a4476543188b2722c34550",
"Name": "Design Patterns- II",
"Price": 60.93,
"Category": "Computers",
"Author": "Ralph johnson"
},
]
Scenarios 1 – MongoDB Regex Query with Like
Let’s build MongoDB-like query using a regex for getting Author names where the name contains a word like “J” or “j” for the Author field.
Assumptions:
“J” or “j” could be contained at the start or last or in the middle or anywhere else of Author names.
Query pattern
MongoDB regex query with case-sensitive search
{ Field Name: {'$regex' : 'string'}}
MongoDB regex query with case insensitive search
{ Field Name: {'$regex' : 'string', '$options' : 'i'}}
Example Query 1
db.Books.find({Author:{'$regex' : 'J'}})
Mongo Query Result :
db.Books.find({Author:{'$regex' : 'J'}}) { "_id" : ObjectId("5db5a4476997188b2722c820"), "Name" : "Design Patterns", "Price" : 54.93, "Category" : "Computers", "Author" : "Ralph Johnson" } { "_id" : ObjectId("5ff503cda29ce9564c2724e3"), "Name" : "Code Refactoring", "Price" : 43.15, "Category" : "Computers", "Author" : "Julia Robert" }
db.Books.find({Author:{'$regex' : 'j'}}) { "_id" : ObjectId("5f34a2430c3ca98a8c9052d2"), "Name" : "Design Patterns", "Price" : 54.93, "Category" : "Computers", "Author" : "Ralph johnson" }
Example Query 2 – Case insensitive
Let’s now use case insensitive query using $options,
{ Author:{'$regex' : 'J', '$options' : 'i'}
Mongo Query Result :
Below is the query for like in MongoDB
> db.Books.find({Author:{'$regex' : 'J', '$options' : 'i'}}) { "_id" : ObjectId("5db5a4476997188b2722c820"), "Name" : "Design Patterns", "Price" : 54.93, "Category" : "Computers", "Author" : "Ralph Johnson" } { "_id" : ObjectId("5f34a2430c3ca98a8c9052d2"), "Name" : "Design Patterns", "Price" : 54.93, "Category" : "Computers", "Author" : "Ralph johnson" } { "_id" : ObjectId("5ff503cda29ce9564c2724e3"), "Name" : "Code Refactoring", "Price" : 43.15, "Category" : "Computers", "Author" : "Julia Robert" }
Scenarios 2 – MongoDB Regex Query with ‘Like’ that start with criteria
Let’s build a query to get the list of items that start with some search criteria. Example “Ro” for Author field.
Query pattern
{ Field Name : /^Ro/ } Where Ro is your search cretieria.
Example Query
{ Author :{'$regex' : '^Ro', '$options' : 'i'}})
Search Result:
Scenarios 3 – Regex End with match Mongo Query
Let’s build a query to Get the list of items that ends with some search criteria. Example “rt” for Author field
Query pattern
{ Field Name : { ‘$regex’ : ‘robert$’ , ‘$options’ : ‘i’ } }
Where ‘robert’ is your search criteria
Example Query
{ Author :{'$regex' : 'robert$', '$options' : 'i'}}
Mongo Shell results,
> db.Books.find({ Author :{'$regex' : 'robert$', '$options' : 'i'}}) { "_id" : ObjectId("5ff503cda29ce9564c2724e3"), "Name" : "Code Refactoring", "Price" : 43.15, "Category" : "Computers", "Author" : "Julia Robert" }
Above query, we have Author “Julia Robert” that matches with search criteria.
Scenarios 4 – Regex Exact Match Mongo Query
Let’s look into Regex Exact Match Mongo Query.
Query pattern
{ Field Name : { ‘$regex’ : ‘robert$’ , ‘$options’ : ‘i’ } }
Where ‘robert’ is your search criteria
Example Query:
{ Author :{'$regex' : '^julia robert$', '$options' : 'i'}}
Where ‘julia robert’ is our exact search criteria. Here Author field value has to match exactly (but case insensitive )
Mongo Shell results,
> db.Books.find({ Author :{'$regex' : 'robert$', '$options' : 'i'}}) { "_id" : ObjectId("5ff503cda29ce9564c2724e3"), "Name" : "Code Refactoring", "Price" : 43.15, "Category" : "Computers", "Author" : "Julia Robert" }
Above query, we have Author “Julia Robert” that matches with exact search criteria.
References:
Do you have any comments or ideas or any better suggestions to share?
Please sound off your comments below.
Happy Coding !!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.