Query Similar to the SQL LIKE Statement in MongoDB

1. Create a Database in MongoDB
2. the db.collection.find() Method in MongoDB
3. Use the db.collection.find() Method to Query Similar to the SQL LIKE Statement in MongoDB

MongoDB is a powerful NoSQL database server. It uses JSON-like documents with optional schemas to store data.

It is always an essential task for a developer to organize data as it plays the most crucial role in the application’s performance. In MongoDB, you can use queries similar to the SQL LIKE statement to fetch data.

Create a Database in MongoDB

We will use some dummy data for the examples in this tutorial. You can also create your database and execute the command given below to insert dummy data.

db={
    "colors": [
        {
            "id": 100,
            "color": "Pink"
        },
        {
            "id": 101,
            "color": "Purple"
        },
        {
            "id": 102,
            "color": "Black"
        },
        {
            "id": 103,
            "color": "Blue"
        }
    ]
}

the db.collection.find() Method in MongoDB

The most crucial piece is the flexible yet straightforward db.collection.find() method when searching within a MongoDB collection.

With db.collection.find(), you can easily query a collection of documents by passing it through a few simple parameters and returning a cursor.

A cursor is simply a result set. We can iterate through it to manipulate or otherwise use the documents being pointed to by the cursor.

A simple example of the db.collection.find() method is shown below.

> db.colors.find()

Output:

The above program returns all the documents in a collection. But this is very uncommon to production requirements.

You always require some filtered results from a database.

For example, if you want to fetch all the data that contains color: Pink. Execute the query below.

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

Output:

Use the db.collection.find() Method to Query Similar to the SQL LIKE Statement in MongoDB

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

Now that you’re using .find() to query your collection, you can slightly alter your syntax and start looking for matches based on a word or phrase that might be a partial match within a specific field, similar to how SQL engines use the LIKE operator.

The trick utilizes the regular expressions (regex), a text string that defines a search pattern. Several regex engines are written in different syntax, but the fundamentals are the same.

At the most basic level, a regex is a string enclosed on both sides by a single slash (/).

db.collection.find({
    "k": /pattern/
})

But currently, as shell regex doesn’t work in query for mongoplayground.net, you can use the expression given below instead.

db.collection.find({
    "k": {
        "$regex": "pattern"
    }
})

For example, if you want to use regex to perform the same query as in the example above and find out how many documents contain the color Pink, you can replace your string "Pink" with /Pink/ instead.

Use the db.collection.find() Method to Search Matching Strings Anywhere

The first statement will search all documents where color names have Pink anywhere in the string. The second statement searches for all records where the color has Bl in their name.

The SQL statement for this query:

select * from colors where color LIKE "%Pink%"

The MongoDB statement for this query:

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

Output:

The MongoDB statement for this query:

db.colors.find({
    "color": {
        "$regex": "Bl"
    }
})

Output:

Using regex instead of a standard quoted string, you can start to look for partial word or phrase matches.

Use the db.collection.find() Method With ^ to Search Matching Strings Start Characters

This will match all strings that start with the character P. For example, the carrot ^ symbol is used.

The SQL statement for this query:

select * from colors where color LIKE "P%"

The MongoDB statement for this query:

db.colors.find({
    "color": {
        "$regex": "^P"
    }
})

Output:

Use the db.collection.find() Method With $ to Search Matching Strings End Characters

The dollar $ symbol matches string ends with specific characters.

The below example matches all strings that end with the character k.

The SQL statement for this query:

select * from colors where color LIKE "%k"

The MongoDB statement for this query:

db.colors.find({
    "color": {
        "$regex": "k$"
    }
})

Output:

Use the db.collection.find() Method With i to Search Matching String in Any Case

The default db.collection.find method searches with case-sensitive. However, you can instruct the find command to match characters in any case with the i option as used below.

The SQL statement for this query:

select * from colors where color LIKE BINARY "pink"

The MongoDB statement for this query:

db.colors.find({
    "color": {
        "$regex": "k$/i"
    }
})

Output:

This MongoDB tutorial article teaches you how to search for a database similar to SQL LIKE statements in MongoDB.