How to Use SQL LIKE Statement in MongoDB

MongoDB is an powerful Nosql database server. MongoDB uses JSON-like documents with optional schemas to store data.

Its always a critical task for a develop to organize data. As it plays most important role in the application performance. In Mongodb, you can use queries similar to SQL LIKE statement to fetch data.

For the examples used in this tutorial, we uses some dummy data as below. You can also create a database and execute below commands to insert dummy data.

db.colors.insert({ "id": 100, "color": "Pink"})
db.colors.insert({ "id": 101, "color": "Purple"})
db.colors.insert({ "id": 102, "color": "Black"})
db.colors.insert({ "id": 103, "color": "Blue"})

Using .find()

The Mongodb find() command is used to search documents from a collection. This function provides flexible options to search documents.

The default find() function retrieve all the documents in a collection. It also allows you to query a collection of documents, by passing a few simple parameters, and return a cursor.

A simple example of the .find() method look like below:

> db.colors.find()

{ "_id" : ObjectId("5f697e4ccc528930cde49f53"), "id" : 100, "color" : "Pink" }
{ "_id" : ObjectId("5f697e4fcc528930cde49f54"), "id" : 101, "color" : "Purple" }
{ "_id" : ObjectId("5f697e52cc528930cde49f55"), "id" : 102, "color" : "Black" }
{ "_id" : ObjectId("5f697e55cc528930cde49f56"), "id" : 103, "color" : "Blue" }

The above returns all the documents in a collection. But this is very uncommon on production requirements. You always required some filtered results from a database.

For example, fetch all documents contains “color: Pink”. Execute query like:

>  db.colors.find({color: "Pink"})

Using .find() as SQL LIKE Statement

You can use regular expression for searching documents in monogdb. This will be similar to LIKE statements in SQL queries.

1. Search String Anywhere – To search all document where color name have “Pink” anywhere in string. The second statement searches for all documents where color have "Bl" in there name.

   ### SQL Statement 
   select * from colors where color LIKE "%Pink%"
   
   ### Mongodb Statement 
   db.colors.find(color: "/Pink/")
   

   

2. Search String Start With – This will match all the string start with P characters. The carrot “^” symbol is used for start with.

   ### SQL Statement 
   select * from colors where color LIKE "P%"
   
   ### Mongodb Statement 
   db.colors.find(color: "/^P/")
   

   

3. Search String End With – The dollar “$” symbol is used to match string ends with specific characters. The below example matches all strings ends with “k” character.

   ### SQL Statement 
   select * from colors where color LIKE "%k"
   
   ### Mongodb Statement 
   db.colors.find(color: "/k$/")
   

   

4. Search String In Any Case – The default find method search with case-sensitive. You can instruct find command to match characters in any case with “i” option as used in below example.

   ### SQL Statement 
   select * from colors where color LIKE BINARY "pink"
   
   ### Mongodb Statement 
   db.colors.find(color: "/pink/i")
   

   

Conclusion

In this tutorial, you have learned to to search database similar to SQL LIKE statements in Mongodb.