[sql] How to query MongoDB with "like"?

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

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

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

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

If you're using PHP, you can use 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);
}

Already u got the answers but to match regex 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 more pretty output

Regex are expensive are 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 text index:

db.collection.find({
  '$text'=>{'$search': "The string"}
})

There are already many answers. I am giving different types of requirements and solutions for string search with regex.

You can do with regex which contain word i.e like. Also you can use $options => i for case insensitive search

Contains string

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

Doesn't Contains string only with regex

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 this as a bookmark, and a reference for any other alterations you may need.

In PHP, you could use following code:

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

You would use regex for that in mongo.

e.g:

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

You can use the new feature of 2.6 mongodb:

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

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

This is not a complete RegEx 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" }

Just in case, someone is looking for a 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.*/]}})

As Mongo shell support regex, that's completely possible.

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

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

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

For PHP mongo Like.
I had several issues with php mongo like. i found that concatenating the regex params helps in some situations PHP mongo find field starts with. I figured I would post on here to contribute to the more popular thread

e.g

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)

In nodejs project and use mongoose use 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);
            });

• One way to find the result as with equivalent to like query

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

Where i use for cases insensitive fetch data

• Another way by which we can get result also

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

Above will provide the result which have the aus in name cantaing aus.

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

I found a free tool to translate MYSQL queries to MongoDB. http://www.querymongo.com/ I checked with several queries. as i see almost all them are correct. According to that, The answer is

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

String 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

MongoRegex has been deprecated.
Use MongoDB\BSON\Regex

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

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

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

Result:

[/My/, /Name/]

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/]

If you want 'Like' search in mongo then you should go with $regex by using this query will be

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

for more you can read the documentation as well. https://docs.mongodb.com/manual/reference/operator/query/regex/

You can also use the wildcard filter as follows:

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

be sure to use *.

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

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

There are various ways to accomplish this.

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 here https://docs.mongodb.com/manual/reference/operator/query/regex/

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

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

In Go and the mgo driver:

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

where result is the struct instance of the sought after type

Above answers are perfectly answering the questions about core mongodb query. But when using pattern based search query such as:

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

or

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

in spring boot jpa repository query with @Query annotation, use query something like:

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

and 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.*/");

Important point to note: each time ?0 field in @Query statement is replaced with double quoted string. so forwardslash(/)should not be used in these cases! always go for pattern using double quotes in searching pattern!! eg. use "^toron" or "^toron.*" over /^toron/ or /^toron.*/

You have 2 choices:

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

or

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

On 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 regular expression as you want.

Enjoy!

In

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

you can do:

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

Like Query would be as shown below

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

for 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]

If you are using Spring-Data Mongodb You can do this in this way:

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

You can use where statement to build any JS script:

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

Reference: http://docs.mongodb.org/manual/reference/operator/where/

For Mongoose in Node.js

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

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

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

Here is the command which uses starts with paradigm

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

Using template literals with variables also works:

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

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

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

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

In MongoDB console, it looks like this :

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

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

In addion pretty() method will in all the places where produce formatted JSON structure which is more readable.

db.users.insert({name: 'paulo'})
db.users.insert({name: 'patric'})
db.users.insert({name: 'pedro'})

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

out: paulo, patric

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

out: paulo, patric

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

out: pedro

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

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

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