How to query MongoDB with “like” ?

To query MongoDB with a “LIKE” operator (similar to SQL’s LIKE), you use regular expressions with the $regex operator. Here’s how to implement different types of pattern matching:

1. Basic “Contains” Query

Find documents where a field contains a substring:

// SQL equivalent: WHERE field LIKE '%value%'
db.collection.find({ field: { $regex: 'value', $options: 'i' } });

• $options: 'i' makes the search case-insensitive.

2. Starts With

Find documents where a field starts with a specific string:

// SQL equivalent: WHERE field LIKE 'value%'
db.collection.find({ field: { $regex: '^value', $options: 'i' } });

3. Ends With

Find documents where a field ends with a specific string:

// SQL equivalent: WHERE field LIKE '%value'
db.collection.find({ field: { $regex: 'value$', $options: 'i' } });

4. Exact Match (Case-Insensitive)

// SQL equivalent: WHERE field LIKE 'VALUE' (case-insensitive)
db.collection.find({ field: { $regex: /^value$/i } });

5. Using Aggregation Pipeline

Combine $regex with other operators:

db.collection.aggregate([
  {
    $match: {
      field: { $regex: 'pattern' }
    }
  }
]);

6. Index Optimization

For better performance with $regex, use:

• Prefix Anchors: Queries starting with ^ (e.g., ^value) can use indexes.
• Avoid Leading Wildcards: Patterns like .*value disable index usage.

Examples

Case 1: Find users with “john” in their name (case-insensitive)

db.users.find({ name: { $regex: 'john', $options: 'i' } });

Case 2: Find products starting with “A”

db.products.find({ name: { $regex: '^A', $options: 'i' } });

Case 3: Find emails ending with “@gmail.com”

db.users.find({ email: { $regex: '@gmail\\.com$' } });

Key Notes

1. Escape Special Characters: Use \\ for characters like ., *, etc.

   db.collection.find({ field: { $regex: '\\*special' } });

2. Performance: Use ^ at the start of patterns to leverage indexes.
3. Alternatives: For full-text search, consider MongoDB’s text indexes.

Comparison with SQL

SQL	MongoDB Equivalent
LIKE '%value%'	{ $regex: 'value' }
LIKE 'value%'	{ $regex: '^value' }
LIKE '%value'	{ $regex: 'value$' }
NOT LIKE '%val%'	{ field: { $not: /val/ } }

By combining $regex with other MongoDB operators, you can create complex pattern-matching queries efficiently.