How to Query MongoDB with LIKE: Complete Guide

MongoDB doesn’t have a direct LIKE operator like SQL, but you can achieve the same functionality using the $regex operator. To replicate SELECT * FROM users WHERE name LIKE '%m%', you would use db.users.find({ name: { $regex: 'm' } }) or db.users.find({ name: { $regex: /m/ } }). The $regex operator supports JavaScript-style regular expressions to perform pattern matching similar to SQL’s LIKE operator.

Contents

• Basic $regex Usage
• Different LIKE Patterns in MongoDB
• Case Sensitivity Options
• Performance Considerations
• Practical Examples
• Alternative Approaches

Basic $regex Usage

The primary way to achieve LIKE functionality in MongoDB is through the $regex operator in combination with the find() method. According to the official MongoDB documentation, the $regex operator provides “regular expression capabilities for pattern matching strings in queries.”

Basic Syntax

// Find documents where a field contains a specific substring
db.collection.find({ field: { $regex: 'pattern' } })

// Alternatively using RegExp object
db.collection.find({ field: { $regex: /pattern/ } })

Example for the User’s Specific Query

For the SQL query SELECT * FROM users WHERE name LIKE '%m%', the MongoDB equivalent would be:

db.users.find({ name: { $regex: 'm' } })
// or
db.users.find({ name: { $regex: /m/ } })

This will find all documents where the name field contains the letter “m” anywhere in the string, just like the SQL LIKE ‘%m%’ pattern.

Different LIKE Patterns in MongoDB

SQL LIKE patterns have different wildcard characters, and each can be replicated in MongoDB using regex patterns:

SQL LIKE Pattern Equivalents

Practical Examples

// SQL: SELECT * FROM users WHERE name LIKE 'John%'
// MongoDB:
db.users.find({ name: { $regex: '^John' } })

// SQL: SELECT * FROM users WHERE name LIKE '%son'
// MongoDB:
db.users.find({ name: { $regex: 'son$' } })

// SQL: SELECT * FROM users WHERE name LIKE '%mit%'
// MongoDB:
db.users.find({ name: { $regex: 'mit' } })

// SQL: SELECT * FROM users WHERE name LIKE '_ohn'
// MongoDB:
db.users.find({ name: { $regex: '.ohn' } })

As Spark by Examples explains, “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.”

Case Sensitivity Options

Default Case-Sensitive Matching

By default, MongoDB regex queries are case-sensitive:

db.users.find({ name: { $regex: 'john' } }) // Only matches "john", not "John" or "JOHN"

Case-Insensitive Matching

To perform case-insensitive searches, use the $options parameter with ‘i’:

db.users.find({ 
    name: { 
        $regex: 'john', 
        $options: 'i' 
    } 
})
// Matches: "john", "John", "JOHN", "jOhN", etc.

According to DataFlair, when you want to search all documents which are having “ab” in their Employee_name irrespective of case sensitivity, you can use the $options parameter.

Advanced Case Control

You can also use inline modifiers for more granular control:

// Case-insensitive only for specific parts
db.users.find({ 
    name: { 
        $regex: '(?i)john(?-i)son' 
    } 
})
// Matches "johnson" case-insensitive, but "son" case-sensitive

Performance Considerations

Index Usage

MongoDB can only use indexes efficiently with regex queries under specific conditions:

• The pattern must be anchored at the beginning of the string (starts with ^)
• The match must be case-sensitive
• No anchors or case-insensitive matches mean the query must scan all documents

As the MongoDB documentation states, “$regex can only use an index efficiently when the regular expression has an anchor for the beginning (i.e. ^) of a string and is a case-sensitive match.”

Performance Optimization Tips

1. Use anchored patterns for better performance:

   javascript

   // Better performance - can use index
   db.users.find({ name: { $regex: '^John' } })
   
   // Poor performance - must scan all documents
   db.users.find({ name: { $regex: 'John' } })
   

2. Avoid case-insensitive searches on large collections when possible

3. Consider text search for complex pattern matching:

   javascript

   // Create text index first
   db.users.createIndex({ name: "text" })
   
   // Use text search
   db.users.find({ 
       $text: { $search: "John" } 
   })
   

As noted in the MongoDB blog post, “While pragmatic and basically effective, these approaches do not perform well at a large scale.”

Practical Examples

Complete Workflow Example

Let’s see a practical example with different LIKE scenarios:

// Sample data
db.users.insertMany([
    { name: "John Doe", email: "john@example.com" },
    { name: "Alice Johnson", email: "alice@example.com" },
    { name: "Bob Smith", email: "bob@example.com" },
    { name: "Mary Wilson", email: "mary@example.com" },
    { name: "johnny walker", email: "johnny@example.com" }
])

// SQL: SELECT * FROM users WHERE name LIKE '%John%'
// MongoDB:
db.users.find({ name: { $regex: 'John' } })
// Result: "John Doe", "Alice Johnson", "johnny walker"

// SQL: SELECT * FROM users WHERE name LIKE '%john%' (case insensitive)
// MongoDB:
db.users.find({ name: { $regex: 'john', $options: 'i' } })
// Result: "John Doe", "Alice Johnson", "johnny walker", "john@example.com"

// SQL: SELECT * FROM users WHERE name LIKE 'J%'
// MongoDB:
db.users.find({ name: { $regex: '^J' } })
// Result: "John Doe"

// SQL: SELECT * FROM users WHERE name LIKE '%son'
// MongoDB:
db.users.find({ name: { $regex: 'son$' } })
// Result: "Alice Johnson", "Bob Smith"

Node.js Example

As shown in Morioh’s tutorial, you can also use JavaScript RegExp objects:

const MongoClient = require('mongodb').MongoClient;

async function findUsersWithSubstring(searchTerm) {
    const client = await MongoClient.connect('mongodb://localhost:27017');
    const db = client.db('mydb');
    const collection = db.collection('users');
    
    // Case insensitive substring search
    const results = await collection.find({
        name: new RegExp(searchTerm, 'i')
    }).toArray();
    
    console.log(results);
    client.close();
}

// Usage
findUsersWithSubstring('john'); // Finds any name containing 'john' case-insensitive

Alternative Approaches

1. Using $where for Complex Logic

For very complex LIKE-like operations that can’t be expressed with regex:

db.users.find({
    $where: "this.name.indexOf('John') !== -1"
})

2. Aggregation Pipeline with $substrCP

For specific substring operations:

db.users.aggregate([
    {
        $addFields: {
            hasSubstring: {
                $regexMatch: {
                    input: "$name",
                    regex: "John"
                }
            }
        }
    },
    {
        $match: { hasSubstring: true }
    }
])

3. Text Search for Full-Text Capabilities

For more sophisticated text searching:

// Create text index first
db.users.createIndex({ name: "text", email: "text" })

// Use $text operator
db.users.find({
    $text: { $search: "John" }
})

According to Chartio’s tutorial, “There are a number of regex engines that are written in slightly different syntax, but the fundamentals are all basically the same, and in this case, MongoDB uses the Perl Regex (PCRE) engine.”

Sources

1. MongoDB $regex Operator Documentation - MongoDB Docs
2. MongoDB Query with “like” Example - Spark By Examples
3. How to query MongoDB with “like” - Stack Overflow
4. MongoDB LIKE Query Alternatives: String Searching Explained - SQLPey
5. How to Query MongoDB with “like”? - GeeksforGeeks
6. MongoDB Pattern Matching: Using Regex for ‘Like’ Queries - W3Resource
7. MongoDB Text Search: Finding Words — Use Search Instead - MongoDB Blog
8. How to Use a SQL Like and Regex Search in MongoDB and Node.JS - Morioh
9. How to Use a SQL LIKE Statement in MongoDB - Chartio
10. MongoDB Regular Expression ($option & $regex) with Examples - DataFlair

Conclusion

To achieve SQL LIKE functionality in MongoDB, you need to understand these key points:

1. Use $regex operator as the primary equivalent to SQL LIKE - it’s MongoDB’s pattern matching mechanism for strings
2. Master the regex patterns: pattern for %pattern%, ^pattern for pattern%, and pattern$ for %pattern
3. Leverage $options parameter for case-insensitive searches with 'i' option
4. Consider performance implications - anchored patterns (^pattern) perform better as they can use indexes
5. Explore alternatives like text search for complex scenarios on large datasets

For your specific query SELECT * FROM users WHERE name LIKE '%m%', the simplest MongoDB equivalent is db.users.find({ name: { $regex: 'm' } }). If you need case-insensitive matching, use db.users.find({ name: { $regex: 'm', $options: 'i' } }).

Remember that while regex provides powerful pattern matching capabilities, it’s important to be mindful of performance considerations, especially when working with large datasets or complex patterns.