How to query MongoDB with "like"

Question

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.

Skill

see mongodb's docs: Advanced Queries -- Regular expressions mongodb.org/display/DOCS/… – Beholden

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 – Rid

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. – Amatruda

Answer 1

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).

Daddylonglegs

is searching by regex expensive? – Skill

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. – Parlormaid

When not anchored to the beginning of the string, it is somewhat expensive. But then again, so is a LIKE query in SQL. – Fairy

I would add regex i javascript db.users.find({ "name": { $regex: /m/i } }) – Archon

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. – Breviary

@Ivan Cabrera, yes it is worth noting, as is putting '.*' anywhere there is whitespace. Othrer DBMS's allow perppered %. – Ardeliaardelis

Why ".*" at the beginning and end of the regex seems "optional" in you example? Does Mongo treat the regex /somePattern/ automatically as "contains somePattern"? – Unaunabated

showing syntax error for /m/ while using pymongo. what is correct syntax in python? – Chilopod

Just make sure that you escape the values that you place in this regex correctly especially if this is something that a user can input values into. You can cause Mongo to throw exceptions if its not properly escaped. – Ardith

Please not that these is no quotations in /m/ – Taxis

Answer 2

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

Footman

You know why you have so many upvotes? Because the docs deserve equal many downvotes! Thank you – Ardolino

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%' – Psychomancy

Performance wise, $regex is not the best approach... – Ching

Regex is the only way to answer the OPs question in MongoDB. Therefore there is no other approach @Ching – Emersen

Answer 3

In

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

you can do:

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

Eutrophic

@TahirYasin if you're still wondering, case-insensitive search would be done like this: db.users.find({'name': {'$regex': 'sometext', '$options': 'i'}}) – Execrative

this applies to the whole word, not to a part of the word. – Skyla

Answer 4

In PHP, you could use the following code:

$collection->find(array('name'=> array('$regex' => 'm'));

Thrasonical

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')); – Daydream

Answer 5

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.

Carolacarolan

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 – Ariannearianrhod

Answer 6

You would use a regular expression for that in MongoDB.

For example,

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

Liebowitz

I think this only shows documents with a name value that starts with "m" – India

Important to remove quotes when you use regex, i.e. don't do "/^m/", just do /^m/ – Graaf

Answer 7

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.

Berwickupontweed

Answer 8

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') } );

Domitian

Thanks @Eddy. Providing '$options: 'i'' with regex made refactoring process to easy for me. – Windtight

Answer 9

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.

Heroic

Answer 10

For Mongoose in Node.js:

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

Overcharge

Answer 11

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$/ })

Churchless

It becomes extremely slow on large datasets just FYI. And there's no way to know what the score is. – Template

Answer 12

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 ')

Gredel

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. – Retractile

Answer 13

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"
    }
});

Cuboid

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. – Narcotize

Answer 14

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);
            });

Crisscross

Answer 15

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

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

Reference: $where

Coupling

$where is highly inefficient. Do full collection scan :( – Valetudinarian

Answer 16

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

Simson

Answer 17

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.

Volva

pls avoid unkeyed fields in literals, do bson:RegEx{Pattern:"m", Options:"i"} instead – Kibbutz

Answer 18

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.

Autogamy

Answer 19

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)

Faun

Answer 20

Using template literals with variables also works:

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

Roughshod

This doesn't work. Inputting names with special characters (for example () fails. – Judenberg

Are those valid variable names? Or do you mean: req.body.firstname = "John(Jimmy) Doe"? – Roughshod

The second one yes. – Judenberg

Answer 21

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"}
})

Dismissive

This doesn't work with partials. – Template

Answer 22

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);

Frustrate

Answer 23

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" }

Annadiana

Answer 24

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]

Shabbygenteel

Answer 25

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));

Suffragist

Answer 26

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/})

Intercurrent

Answer 27

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.

Chloro

Answer 28

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

Algar

Answer 29

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

db.collection.aggregate([{
        $project : {
            fieldExists : {
                $indexOfBytes : ['$field', 'string']
            }
        }
    }, {
        $match : {
            fieldExists : {
                $gt : -1
            }
        }
    }, {
        $limit : 5
    }
]);

Boogeyman

This might still be better than all these regex suggestions - but it still does a colscan. – Systemic

Answer 30

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") });

Melodize

Answer 31

Use:

const indexSearch = await UserModel.find(
      { $text: { $search: filter } },
    );

    if (indexSearch.length) {
      return indexSearch;
    }
    return UserModel.find(
      {
        $or: [
          { firstName: { $regex: `^${filter}`, $options: 'i' } },
          { lastName: { $regex: `^${filter}`, $options: 'i' } },
          { middleName: { $regex: `^${filter}`, $options: 'i' } },
          { email: { $regex: `^${filter}`, $options: 'i' } },
        ],
      },
    );

I used a combination of regex and "index".

Link

Answer 32

As the MongoDB shell supports regular expressions, that's completely possible.

db.users.findOne({"name" : /.*sometext.*/});

If we want the query to be case-insensitive, we can use the "i" option, like shown below:

db.users.findOne({"name" : /.*sometext.*/i});

Vigor

Answer 33

MongoRegex has been deprecated.

Use MongoDB\BSON\Regex:

$regex = new MongoDB\BSON\Regex ( '^m');
$cursor = $collection->find(array('users' => $regex));
//iterate through the cursor

Rhoea

Answer 34

Use:

db.customer.find({"customerid": {"$regex": "CU_00000*", "$options": "i"}}).pretty()

When we are searching for string patterns, it is always better to use the above pattern as when we are not sure about case.

Enisle

Answer 35

There are various ways to accomplish this.

The simplest one:

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

{ <field>: { $regex: /pattern/, $options: '<options>' } }
{ <field>: { $regex: 'pattern', $options: '<options>' } }
{ <field>: { $regex: /pattern/<options> } }

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

More details can be found in $regex.

Devitt

Answer 36

Using a JavaScript RegExp

• split the name string by space and make an array of words
• map to an iterate loop and convert the string to a regex of each word of the name

let name = "My Name".split(" ").map(n => new RegExp(n));
console.log(name);

Result:

[/My/, /Name/]

There are two scenarios to match a string,

1. $in: (it is similar to the $or condition)

Try $in Expressions. To include a regular expression in an $in query expression, you can only use JavaScript regular expression objects (i.e., /pattern/). For example:

db.users.find({ name: { $in: name } }); // name = [/My/, /Name/]

2. $all: (it is similar to a $and condition) a document should contain all words

db.users.find({ name: { $all: name } }); // name = [/My/, /Name/]

Using nested $and and $or conditionals and $regex

There are two scenarios to match a string,

1. $or: (it is similar to the $in condition)

db.users.find({
  $or: [
    { name: { $regex: "My" } },
    { name: { $regex: "Name" } }
    // if you have multiple fields for search then repeat same block
  ]
})

Playground

2. $and: (it is similar to the $all condition) a document should contain all words

db.users.find({
  $and: [
    {
      $and: [
        { name: { $regex: "My" } },
        { name: { $regex: "Name" } }
      ]
    }
    // if you have multiple fields for search then repeat same block
  ]
})

Playground

Lawrence

Answer 37

I found a free tool to translate MySQL queries to MongoDB: http://www.querymongo.com/

I checked with several queries. As I see it, almost all of them are correct. According to that, the answer is

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

Spectroscopy

Answer 38

For the Go driver:

filter := bson.M{
    "field_name": primitive.Regex{
        Pattern: keyword,
        Options: "",
    },
}
cursor, err := GetCollection().Find(ctx, filter)

Use a regex in the $in query (MongoDB documentation: $in):

filter := bson.M{
    "field_name": bson.M{
        "$in": []primitive.Regex{
            {
                Pattern: keyword,
                Options: "",
            },
        }
    }
}
cursor, err := GetCollection().Find(ctx, filter)

Pterous

Answer 39

If you're using PHP, you can use the MongoDB_DataObject wrapper like below:

$model = new MongoDB_DataObject();

$model->query("select * from users where name like '%m%'");

while($model->fetch()) {
    var_dump($model);
}

Or:

$model = new MongoDB_DataObject('users);

$model->whereAdd("name like '%m%'");

$model->find();

while($model->fetch()) {
    var_dump($model);
}

Uzzi

Answer 40

FullName like 'last' with status==’Pending’ between two dates:

db.orders.find({
      createdAt:{$gt:ISODate("2017-04-25T10:08:16.111Z"),
      $lt:ISODate("2017-05-05T10:08:16.111Z")},
      status:"Pending",
      fullName:/last/}).pretty();

status== 'Pending' and orderId LIKE ‘PHA876174’:

db.orders.find({
     status:"Pending",
     orderId:/PHA876174/
     }).pretty();

Garbage

Answer 41

>> db.car.distinct('name')
[ "honda", "tat", "tata", "tata3" ]

>> db.car.find({"name":/. *ta.* /})

Castrato

Wow, this is probably an amazing answer. Care to elaborate on that? – Hydrophilic

Use your solution to answer the given example in the question. Do not create a random scenario that seems to overlap in functionality and expect it to seem clear. – Subtend

Answer 42

You can also use the wildcard filter as follows:

{"query": { "wildcard": {"lookup_field":"search_string*"}}}

Be sure to use *.

Inherence

Answer 43

Here is the command which uses the "starts with" paradigm:

db.customer.find({"customer_name" : { $regex : /^startswith/ }})

Caresse

Answer 44

Just in case, someone is looking for an SQL LIKE kind of query for a key that holds an array of strings instead of a string, here it is:

db.users.find({"name": {$in: [/.*m.*/]}})

Hurried

Answer 45

The previous answers are perfectly answering the questions about the core MongoDB query. But when using a pattern-based search query such as:

{"keywords":{ "$regex": "^toron.*"}}

or

{"keywords":{ "$regex": "^toron"}}

in a Spring Boot JPA repository query with @Query annotation, use a query something like:

@Query(value = "{ keyword : { $regex : ?0 }  }")
List<SomeResponse> findByKeywordContainingRegex(String keyword);

And the call should be either of:

List<SomeResponse> someResponseList =    someRepository.findByKeywordsContainingRegex("^toron");

List<SomeResponse> someResponseList =    someRepository.findByKeywordsContainingRegex("^toron.*");

But never use:

List<SomeResponse> someResponseList = someRepository.findByKeywordsContainingRegex("/^toron/");

List<SomeResponse> someResponseList =someRepository.findByKeywordsContainingRegex("/^toron.*/");

An important point to note: each time the ?0 field in @Query statement is replaced with a double quoted string. So forwardslash (/) should not be used in these cases! Always go for a pattern using double quotes in the searching pattern!! For example, use "^toron" or "^toron.*" over /^toron/ or /^toron.*/

Corgi

for more details follow link: docs.mongodb.com/manual/reference/operator/query/regex/… – Corgi

Answer 46

If you want to use mongo JPA like query you should try this.

@Query("{ 'title' : { $regex: '^?0', $options: 'i' } }")
List<TestDocument> findLikeTitle(String title);

Rager

Answer 47

It works too:

db.getCollection('collection_name').find({"field_name": /^searched_value/})

Iberian