1934

I want to query something with SQL's like query:

SELECT * FROM users  WHERE name LIKE '%m%'

How can I achieve the same in MongoDB? I can't find an operator for like in the documentation.

3
  • 11
    see mongodb's docs: Advanced Queries -- Regular expressions mongodb.org/display/DOCS/…
    – douyw
    Nov 22, 2011 at 13:21
  • 3
    I seriously suggest taking a look at MongoDB Atlas Search, as it is much more resource efficient and feature rich for "like"-like queries that $text or $regex
    – Nice-Guy
    Mar 18, 2022 at 19:31
  • MongoDB Compass: {"field_name": { "$regex": "textOrRegex"}} (is roughly equivalent of LIKE '%text%'; LIKE 'text%'=> '^text'; LIKE '%text => 'text$') note that it's case SENSITIVE by default => '$options': 'i' will make it insensitive.
    – jave.web
    Feb 1 at 6:46

47 Answers 47

2536

That would have to be:

db.users.find({"name": /.*m.*/})

Or, similar:

db.users.find({"name": /m/})

You're looking for something that contains "m" somewhere (SQL's '%' operator is equivalent to regular expressions' '.*'), not something that has "m" anchored to the beginning of the string.

Note: MongoDB uses regular expressions (see docs) which are more powerful than "LIKE" in SQL. With regular expressions you can create any pattern that you imagine.

For more information on regular expressions, refer to Regular expressions (MDN).

10
  • 141
    is searching by regex expensive?
    – Freewind
    Jul 22, 2010 at 10:13
  • 177
    Actually, it depends. If the query doesn't use an index, and must do a table scan, then it can certainly be expensive. If you're doing a 'starts with' regex query, then that can use an index. Best to run an explain() to see what's happening. Jul 22, 2010 at 14:49
  • 43
    When not anchored to the beginning of the string, it is somewhat expensive. But then again, so is a LIKE query in SQL.
    – Emily
    Jul 26, 2010 at 18:50
  • 62
    I would add regex i javascript db.users.find({ "name": { $regex: /m/i } }) Jun 19, 2015 at 3:24
  • 8
    It is worth mentioning that if you want to use it from Node app and you want a dynamic search, you can use: users.find({"name": new RegExp('.*' + searchVariable + '.*')}) So, this way you can use it with other operators like $in, $nin, etc. Jul 30, 2021 at 21:18
647
db.users.insert({name: 'patrick'})
db.users.insert({name: 'petra'})
db.users.insert({name: 'pedro'})

Therefore:

For:

db.users.find({name: /a/})  // Like '%a%'

Output: patrick, petra

For:

db.users.find({name: /^pa/}) // Like 'pa%'

Output: patrick

For:

db.users.find({name: /ro$/}) // Like '%ro'

Output: pedro

4
  • 11
    You know why you have so many upvotes? Because the docs deserve equal many downvotes! Thank you Sep 21, 2021 at 22:19
  • To search for strings with forward slashes in them, just escape it like so: db.users.find({name: /^path\/to\/something/}) // Like 'path/to/something%'
    – Akaisteph7
    Aug 2, 2022 at 20:34
  • Performance wise, $regex is not the best approach...
    – Danielle
    Feb 20, 2023 at 5:28
  • Regex is the only way to answer the OPs question in MongoDB. Therefore there is no other approach @Danielle Feb 19 at 18:48
390

In

  • PyMongo using Python
  • Mongoose using Node.js
  • Jongo, using Java
  • mgo, using Go

you can do:

db.users.find({'name': {'$regex': 'sometext'}})
2
  • 95
    @TahirYasin if you're still wondering, case-insensitive search would be done like this: db.users.find({'name': {'$regex': 'sometext', '$options': 'i'}}) Jul 14, 2017 at 0:09
  • 1
    this applies to the whole word, not to a part of the word.
    – Revol89
    Oct 3, 2021 at 17:01
109

Here are different types of requirements and solutions for string search with regular expressions.

You can do with a regular expression which contains a word, i.e., like. Also you can use $options => i for a case insensitive search.

Contains string

db.collection.find({name:{'$regex' : 'string', '$options' : 'i'}})

Doesn't contain string, only with a regular expression

db.collection.find({name:{'$regex' : '^((?!string).)*$', '$options' : 'i'}})

Exact case insensitive string

db.collection.find({name:{'$regex' : '^string$', '$options' : 'i'}})

Start with string

db.collection.find({name:{'$regex' : '^string', '$options' : 'i'}})

End with string

db.collection.find({name:{'$regex' : 'string$', '$options' : 'i'}})

Keep Regular Expressions Cheat Sheet as a bookmark, and a reference for any other alterations you may need.

1
  • this is a later comment but, how can I use a variable in above example? like let name = 'john doe' . how can I implement name variable in regex? thanks Aug 26, 2021 at 15:29
108

In PHP, you could use the following code:

$collection->find(array('name'=> array('$regex' => 'm'));
1
  • You can also specify the flags in the second item of the $regex array, like so: $collection->find(array('name'=> array('$regex' => 'm', '$options => 'i')); Oct 13, 2022 at 15:16
74

You would use a regular expression for that in MongoDB.

For example,

db.users.find({"name": /^m/})
2
  • 35
    I think this only shows documents with a name value that starts with "m"
    – JackAce
    Jul 17, 2014 at 0:23
  • 1
    Important to remove quotes when you use regex, i.e. don't do "/^m/", just do /^m/
    – ethanjyx
    Feb 10, 2022 at 20:46
58

You have two choices:

db.users.find({"name": /string/})

or

db.users.find({"name": {"$regex": "string", "$options": "i"}})

For the second one, you have more options, like "i" in options to find using case insensitive.

And about the "string", you can use like ".string." (%string%), or "string.*" (string%) and ".*string) (%string) for example. You can use a regular expression as you want.

0
47

If using Node.js, it says that you can write this:

db.collection.find( { field: /acme.*corp/i } );

// Or
db.collection.find( { field: { $regex: 'acme.*corp', $options: 'i' } } );

Also, you can write this:

db.collection.find( { field: new RegExp('acme.*corp', 'i') } );
1
  • Thanks @Eddy. Providing '$options: 'i'' with regex made refactoring process to easy for me.
    – Nuri Engin
    Sep 25, 2021 at 8:33
32

Already you got the answers, but to match with a regular expression with case insensitivity, you could use the following query:

db.users.find ({ "name" : /m/i } ).pretty()

The i in the /m/i indicates case insensitivity and .pretty() provides a prettier output.

0
25

For Mongoose in Node.js:

db.users.find({'name': {'$regex': '.*sometext.*'}})
0
22

In MongoDb, can use like using MongoDb reference operator regular expression(regex).

For Same Ex.

MySQL - SELECT * FROM users  WHERE name LIKE '%m%'

MongoDb

    1) db.users.find({ "name": { "$regex": "m", "$options": "i" } })

    2) db.users.find({ "name": { $regex: new RegExp("m", 'i') } })

    3) db.users.find({ "name": { $regex:/m/i } })

    4) db.users.find({ "name": /mail/ })

    5) db.users.find({ "name": /.*m.*/ })

MySQL - SELECT * FROM users  WHERE name LIKE 'm%'

MongoDb Any of Above with /^String/

    6) db.users.find({ "name": /^m/ })

MySQL - SELECT * FROM users  WHERE name LIKE '%m'

MongoDb Any of Above with /String$/

    7) db.users.find({ "name": /m$/ })
1
  • It becomes extremely slow on large datasets just FYI. And there's no way to know what the score is. May 10, 2022 at 14:14
19

With MongoDB Compass, you need to use the strict mode syntax, as such:

{ "text": { "$regex": "^Foo.*", "$options": "i" } }

(In MongoDB Compass, it's important that you use " instead of ')

1
  • 1
    This nearly drove me crazy much appreciated, and why can't they give documentation or examples on doing things like this for Compass. I couldn't even find documentation on the strict mode syntax for Compass.
    – zerodoc
    Jan 3, 2023 at 15:01
17

You can use the new feature of MongoDB 2.6:

db.foo.insert({desc: "This is a string with text"});
db.foo.insert({desc:"This is a another string with Text"});
db.foo.ensureIndex({"desc":"text"});
db.foo.find({
    $text:{
        $search:"text"
    }
});
1
  • 8
    Note, AFAIK Mongodb's text searching works on whole words only by default, so this will match values like "This is a string with text", but not "This is a string with subtext". So it's not quite like sql's "LIKE" operator. Mar 10, 2015 at 18:34
16

In a Node.js project and using Mongoose, use a like query:

var User = mongoose.model('User');

var searchQuery = {};
searchQuery.email = req.query.email;
searchQuery.name = {$regex: req.query.name, $options: 'i'};
User.find(searchQuery, function(error, user) {
                if(error || user === null) {
                    return res.status(500).send(error);
                }
                return res.status(200).send(user);
            });
0
15

You can use a where statement to build any JavaScript script:

db.myCollection.find( { $where: "this.name.toLowerCase().indexOf('m') >= 0" } );

Reference: $where

1
  • 10
    $where is highly inefficient. Do full collection scan :( Sep 23, 2013 at 15:23
15

String yourdb={deepakparmar, dipak, parmar}

db.getCollection('yourdb').find({"name":/^dee/})

ans deepakparmar

db.getCollection('yourdb').find({"name":/d/})

ans deepakparmar, dipak

db.getCollection('yourdb').find({"name":/mar$/})

ans deepakparmar, parmar

0
13

In Go and the mgo driver:

Collection.Find(bson.M{"name": bson.RegEx{"m", ""}}).All(&result)

where the result is the struct instance of the sought-after type.

1
  • 2
    pls avoid unkeyed fields in literals, do bson:RegEx{Pattern:"m", Options:"i"} instead
    – bithavoc
    Nov 13, 2016 at 17:33
13

In SQL, the ‘like’ query looks like this:

select * from users where name like '%m%'

In the MongoDB console, it looks like this:

db.users.find({"name": /m/})     // Not JSON formatted

db.users.find({"name": /m/}).pretty()  // JSON formatted

In addition, the pretty() method will produce a formatted JSON structure in all the places which is more readable.

13

For PHP mongo Like.

I had several issues with PHP mongo like. I found that concatenating the regular expression parameters helps in some situations - PHP mongo find field starts with.

For example,

db()->users->insert(['name' => 'john']);
db()->users->insert(['name' => 'joe']);
db()->users->insert(['name' => 'jason']);

// starts with
$like_var = 'jo';
$prefix = '/^';
$suffix = '/';
$name = $prefix . $like_var . $suffix;
db()->users->find(['name' => array('$regex'=>new MongoRegex($name))]);
output: (joe, john)

// contains
$like_var = 'j';
$prefix = '/';
$suffix = '/';
$name = $prefix . $like_var . $suffix;
db()->users->find(['name' => array('$regex'=>new MongoRegex($name))]);

output: (joe, john, jason)
0
11

Using template literals with variables also works:

{"firstname": {$regex : `^${req.body.firstname}.*` , $options: 'si' }}

3
  • This doesn't work. Inputting names with special characters (for example () fails.
    – Marnix
    Nov 11, 2022 at 8:25
  • Are those valid variable names? Or do you mean: req.body.firstname = "John(Jimmy) Doe"?
    – besthost
    Nov 13, 2022 at 0:42
  • The second one yes.
    – Marnix
    Nov 23, 2022 at 8:39
10

Regular expressions are expensive to process.

Another way is to create an index of text and then search it using $search.

Create a text index of fields you want to make searchable:

db.collection.createIndex({name: 'text', otherField: 'text'});

Search for a string in the text index:

db.collection.find({
  '$text'=>{'$search': "The string"}
})
1
  • This doesn't work with partials. May 10, 2022 at 14:15
8

Use regular expressions matching as below. The 'i' shows case insensitivity.

var collections = mongoDatabase.GetCollection("Abcd");

var queryA = Query.And(
         Query.Matches("strName", new BsonRegularExpression("ABCD", "i")), 
         Query.Matches("strVal", new BsonRegularExpression("4121", "i")));

var queryB = Query.Or(
       Query.Matches("strName", new BsonRegularExpression("ABCD","i")),
       Query.Matches("strVal", new BsonRegularExpression("33156", "i")));

var getA = collections.Find(queryA);
var getB = collections.Find(queryB);
7

It seems that there are reasons for using both the JavaScript /regex_pattern/ pattern as well as the MongoDB {'$regex': 'regex_pattern'} pattern. See: MongoDB RegEx Syntax Restrictions

This is not a complete regular expression tutorial, but I was inspired to run these tests after seeing a highly voted ambiguous post above.

> ['abbbb','bbabb','bbbba'].forEach(function(v){db.test_collection.insert({val: v})})

> db.test_collection.find({val: /a/})
{ "val" : "abbbb" }
{ "val" : "bbabb" }
{ "val" : "bbbba" }

> db.test_collection.find({val: /.*a.*/})
{ "val" : "abbbb" }
{ "val" : "bbabb" }
{ "val" : "bbbba" }

> db.test_collection.find({val: /.+a.+/})
{ "val" : "bbabb" }

> db.test_collection.find({val: /^a/})
{ "val" : "abbbb" }

> db.test_collection.find({val: /a$/})
{ "val" : "bbbba" }

> db.test_collection.find({val: {'$regex': 'a$'}})
{ "val" : "bbbba" }
6

A like query would be as shown below:

db.movies.find({title: /.*Twelve Monkeys.*/}).sort({regularizedCorRelation : 1}).limit(10);

For the Scala ReactiveMongo API,

val query = BSONDocument("title" -> BSONRegex(".*" + name + ".*", "")) // like
val sortQ = BSONDocument("regularizedCorRelation" -> BSONInteger(1))
val cursor = collection.find(query).sort(sortQ).options(QueryOpts().batchSize(10)).cursor[BSONDocument]
5

If you are using Spring-Data MongoDB, you can do it in this way:

String tagName = "m";
Query query = new Query();
query.limit(10);
query.addCriteria(Criteria.where("tagName").regex(tagName));
5

If you have a string variable, you must convert it to a regex, so MongoDB will use a like statement on it.

const name = req.query.title; //John
db.users.find({ "name": new Regex(name) });

Is the same result as:

db.users.find({"name": /John/})
5

One way to find the result as with equivalent to a like query:

db.collection.find({name:{'$regex' : 'string', '$options' : 'i'}})

Where i is used for a case-insensitive fetch data.

Another way by which we can also get the result:

db.collection.find({"name":/aus/})

The above will provide the result which has the aus in the name containing aus.

4

Use aggregation substring search (with index!!!):

db.collection.aggregate([{
        $project : {
            fieldExists : {
                $indexOfBytes : ['$field', 'string']
            }
        }
    }, {
        $match : {
            fieldExists : {
                $gt : -1
            }
        }
    }, {
        $limit : 5
    }
]);
1
  • This might still be better than all these regex suggestions - but it still does a colscan.
    – tcurdt
    Aug 3, 2021 at 21:16
4

You can query with a regular expression:

db.users.find({"name": /m/});

If the string is coming from the user, maybe you want to escape the string before using it. This will prevent literal chars from the user to be interpreted as regex tokens.

For example, searching the string "A." will also match "AB" if not escaped. You can use a simple replace to escape your string before using it. I made it a function for reusing:

function textLike(str) {
  var escaped = str.replace(/[\-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/g, '\\$&');
  return new RegExp(escaped, 'i');
}

So now, the string becomes a case-insensitive pattern matching also the literal dot. Example:

>  textLike('A.');
<  /A\./i

Now we are ready to generate the regular expression on the go:

db.users.find({ "name": textLike("m") });
4

If you want a 'like' search in MongoDB then you should go with $regex. By using it, the query will be:

db.product.find({name:{$regex:/m/i}})

For more, you can read the documentation as well - $regex

Not the answer you're looking for? Browse other questions tagged or ask your own question.