Complete Guide: MongoDB Like Query Syntax

To achieve SQL’s LIKE functionality in MongoDB, you need to use the $regex operator which provides pattern matching capabilities similar to SQL’s LIKE. For the specific query SELECT * FROM users WHERE name LIKE '%m%', you would use db.users.find({name: {$regex: 'm'}}) in MongoDB, where the regex pattern 'm' with no anchors behaves like SQL’s %m% wildcard pattern.

Contents

• Understanding SQL LIKE vs MongoDB Pattern Matching
• Using $regex Operator for Basic Pattern Matching
• Translating SQL LIKE Patterns to MongoDB Regex
• Case Sensitivity Options
• Performance Considerations
• Alternative Approaches
• Practical Examples

Understanding SQL LIKE vs MongoDB Pattern Matching

MongoDB doesn’t support a direct SQL-style LIKE operator, but it provides powerful pattern matching capabilities through regular expressions using the $regex operator. This approach offers even more flexibility than SQL’s LIKE while maintaining similar functionality for basic pattern matching needs.

The key difference is that SQL LIKE uses simple wildcard characters:

• % matches any sequence of zero or more characters
• _ matches any single character

While MongoDB’s $regex uses full regular expression syntax, which can handle more complex patterns but requires understanding regex fundamentals.

Using $regex Operator for Basic Pattern Matching

The $regex operator is MongoDB’s primary tool for pattern matching operations. It allows you to search for patterns within string fields using regular expressions.

Basic syntax:

db.collection.find({
  field_name: {
    $regex: 'pattern',
    $options: 'options'
  }
})

For your specific example of finding names containing ‘m’, the simplest approach is:

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

This is equivalent to SQL WHERE name LIKE '%m%' because:

• No anchors (^ or $) means the pattern can appear anywhere in the string
• The regex engine automatically matches if ‘m’ appears anywhere in the name

Translating SQL LIKE Patterns to MongoDB Regex

Here’s how to translate common SQL LIKE patterns to MongoDB regex:

For more complex patterns, you can use full regex syntax:

// Multiple patterns with OR
db.users.find({
  name: {
    $regex: 'm|n|p'  // contains m, n, or p
  }
})

// Character classes
db.users.find({
  name: {
    $regex: '[aeiou]'  // contains any vowel
  }
})

Case Sensitivity Options

By default, MongoDB regex queries are case-sensitive. To achieve case-insensitive matching similar to SQL’s LIKE (which is often case-insensitive depending on database configuration), you have several options:

Method 1: Using $options parameter

db.users.find({
  name: {
    $regex: 'm',
    $options: 'i'  // i = case insensitive
  }
})

Method 2: Using inline regex flags

db.users.find({
  name: {
    $regex: '(?i)m'  // (?i) enables case insensitive mode
  }
})

Method 3: Using collation for better performance

According to MongoDB documentation, for case-insensitive searches, creating a case-insensitive index with collation strength of 1 or 2 is recommended for better performance:

db.users.createIndex(
  { name: 1 },
  { collation: { strength: 2 } }
)

Then query with:

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

Performance Considerations

When working with regex patterns in MongoDB, performance is a crucial consideration:

Index Usage

• Simple prefixes: Patterns like ^m can use indexes efficiently
• Complex patterns: Patterns with no prefix (like m) or suffix patterns cannot use indexes effectively
• Case sensitivity: According to MongoDB docs, “Case-insensitive indexes do not improve performance for $regex queries, as the $regex operator is not collation-aware and therefore cannot take advantage of such indexes.”

Performance Optimization Tips

1. Use anchored patterns when possible: ^pattern or pattern$ can use indexes
2. Avoid leading wildcards: Patterns like %pattern cannot use indexes
3. Consider text search: For full-text searching, use $text operator instead of regex
4. Precompile regex: In programming languages, compile regex patterns once and reuse them

When Regex Might Not Be the Best Choice

As mentioned in some sources, regex can be computationally expensive for large datasets. Consider alternatives like:

• Storing normalized versions of fields (lowercase for case-insensitive searches)
• Using MongoDB’s aggregation framework for complex pattern matching
• Implementing search indexing solutions like MongoDB Atlas Search

Alternative Approaches

Method 1: Using $expr in Aggregation

For more complex pattern matching within aggregation pipelines:

db.users.aggregate([
  {
    $match: {
      $expr: {
        $regexMatch: {
          input: '$name',
          regex: 'm'
        }
      }
    }
  }
])

Method 2: Using String Operators

For simple substring matching without full regex:

// Contains
db.users.find({
  name: { $exists: true, $ne: null, $not: { $size: 0 } }
}).filter(doc => doc.name.includes('m'))

Note: This approach requires client-side filtering and may not be efficient for large datasets.

Method 3: Using Text Search Indexes

For full-text search capabilities:

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

// Search
db.users.find({
  $text: { $search: "m" }
})

Practical Examples

Example 1: Basic Pattern Matching

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

// With case insensitivity:
db.users.find({
  name: { 
    $regex: 'john',
    $options: 'i'
  }
})

Example 2: Pattern at Start

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

Example 3: Pattern at End

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

Example 4: Multiple Characters with Wildcards

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

Example 5: Complex Pattern with OR Logic

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

Example 6: Email Pattern Matching

// SQL: SELECT * FROM users WHERE email LIKE '%@example.com'
// MongoDB:
db.users.find({
  email: { $regex: '@example\\.com$' }
})

Sources

1. MongoDB $regex - Database Manual
2. How to query MongoDB with “like” - Stack Overflow
3. MongoDB Query with “like” Example - Spark By Examples
4. MongoDB Pattern Matching: Using Regex for ‘Like’ Queries - W3Resource
5. How to Query MongoDB with “like”? - GeeksforGeeks
6. MongoDB - Regex - GeeksforGeeks
7. MongoDB - Regular Expression - Tutorialspoint

Conclusion

MongoDB provides robust pattern matching capabilities through the $regex operator that can effectively replace SQL’s LIKE functionality. Key takeaways include:

• Use $regex: 'pattern' for basic pattern matching equivalent to LIKE '%pattern%'
• Anchor patterns with ^ and $ for specific starting/ending matches
• Use $options: 'i' for case-insensitive searches
• Be mindful of performance implications, especially with leading wildcards that cannot use indexes
• Consider alternative approaches like text search indexes for complex search requirements
• The regex syntax is more powerful than SQL LIKE but requires understanding regex fundamentals

For your specific query SELECT * FROM users WHERE name LIKE '%m%', the MongoDB equivalent is simply db.users.find({name: {$regex: 'm'}}), providing the same functionality while offering additional flexibility for more complex pattern matching needs.