MongoDB LIKE query: search strings with regex
The MongoDB equivalent of SQL LIKE: contains, starts with and ends with searches using regex, case-insensitive matching, escaping user input and indexes.
Quick Answer
MongoDB has no LIKE operator. Use a regular expression instead. This is WHERE name LIKE '%smith%', case-insensitive:
db.users.find({ name: /smith/i })The same thing written as an operator, which is the form you need when the search term comes from a variable:
db.users.find({ name: { $regex: 'smith', $options: 'i' } })SQL LIKE patterns translated
| SQL | MongoDB | Matches |
|---|---|---|
LIKE '%smith%' | /smith/ | contains "smith" |
LIKE 'Al%' | /^Al/ | starts with "Al" |
LIKE '%.com' | /\.com$/ | ends with ".com" |
LIKE 'a_c' | /^a.c$/ | exactly three characters, a?c |
ILIKE '%smith%' | /smith/i | contains, any case |
NOT LIKE '%test%' | { $not: /test/ } | does not contain "test" |
Two differences from SQL: a regex matches anywhere in the string unless you anchor it with ^ and $, and . means "any character", so a literal dot must be written \..
Case-insensitive search
db.users.find({ name: /smith/i })
db.users.find({ name: { $regex: 'smith', $options: 'i' } })Case-insensitive regex can't use an index efficiently. If you mostly need exact matches that ignore case, such as looking up an email, a case-insensitive collation index is faster:
db.users.createIndex({ email: 1 }, { collation: { locale: 'en', strength: 2 } })
db.users.find({ email: 'ALICE@EXAMPLE.COM' })
.collation({ locale: 'en', strength: 2 })The query must pass the same collation as the index, or MongoDB won't use it.
Escape user input
If you build a regex from what a user typed, characters like ., ( and * become regex syntax. Searching for a.b would also match axb, and an input like ( throws an error. Escape the term first.
const escapeRegex = s => s.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')
const term = req.query.q
const users = await db.collection('users')
.find({ name: { $regex: escapeRegex(term), $options: 'i' } })
.limit(20)
.toArray()import re
term = request.args["q"]
users = db.users.find({"name": {"$regex": re.escape(term), "$options": "i"}}).limit(20)Always put a limit on search-as-you-type queries. An unanchored regex has to check every document, and a one-letter search can match most of the collection.
Match any of several patterns
$in accepts regexes, which is the cleanest way to write LIKE 'al%' OR LIKE 'car%':
db.users.find({ name: { $in: [/^al/i, /^car/i] } })To search several fields at once, use $or:
db.users.find({
$or: [{ name: /smith/i }, { email: /smith/i }]
})What we found testing this: regex and indexes
We loaded 200,000 documents into a collection with an index on name and ran each pattern with explain('executionStats'). Every query used the index, but the number of index keys it had to read varied a lot:
| Query | Matches | Index keys read |
|---|---|---|
/^apple/ | 40,000 | 40,001 |
/^apple/i | 60,000 | 200,000 (all of them) |
/apple/ | 60,000 | 200,000 |
/-199999$/ | 1 | 200,000 |
{ $gte: "apple", $lt: "applf" } with a strength-2 collation index | 60,000 | 60,000 |
- A case-insensitive prefix still reads every key. Adding
ito/^apple/took it from 40,001 keys to all 200,000. A 1-in-200,000 suffix match costs the same full scan. - Regex ignores collation. Run
/^apple/withcollation({ locale: 'en', strength: 2 })and you still get the 40,000 case-sensitive matches, not 60,000. So a case-insensitive index can't speed up a regex. The docs suggest Atlas Search for that; the next point works on any server. - What does work: a range on a collation index. Create the index with
{ collation: { locale: 'en', strength: 2 } }, then query the prefix as a range with the same collation. That read exactly one key per match, so it's a case-insensitive "starts with" that uses the index properly. Build the upper bound by bumping the prefix's last letter (applebecomesapplf). This is reliable for plain letters, but test it first if the prefix ends in punctuation or accented characters. /^apple.*/and/^apple/cost the same (40,001 keys each), so a trailing.*is harmless, just redundant.- An unescaped dot is a wildcard. Searching
{ $regex: 'apple.' }matched 60,000 documents, including "pineapple-…" and "applet…", which is why the escaping step above matters.
db.products.createIndex({ name: 1 }, { collation: { locale: 'en', strength: 2 } })
db.products.find({ name: { $gte: 'apple', $lt: 'applf' } })
.collation({ locale: 'en', strength: 2 }) // case-insensitive "starts with apple", index rangeFor full-text search over large collections, use a text index or Atlas Search. Note that $text matches whole words with stemming, so smith finds "Smith" but not "smithers". See MongoDB query optimization with indexes to check a plan yourself.
Frequently Asked Questions
Does MongoDB have a LIKE operator?
No. Use $regex or a regex literal such as /smith/i. /smith/ is equivalent to LIKE '%smith%'.
How do I do a case-insensitive search in MongoDB?
Add the i option: /smith/i or { $regex: "smith", $options: "i" }. For exact case-insensitive matches, a collation index with strength 2 is faster.
Why is my regex query slow?
Only case-sensitive prefix regexes such as /^abc/ use an index efficiently. In our test on 200,000 documents, /^apple/i and /apple/ both read all 200,000 index keys, while /^apple/ read 40,001.
Can a case-insensitive index speed up regex?
No. Regex ignores collation. For a case-insensitive starts-with, query a range such as { $gte: "apple", $lt: "applf" } with a strength-2 collation that matches a collation index.
How do I search for a string that contains special characters?
Escape it before building the regex: re.escape() in Python, or a replace of regex metacharacters in JavaScript.