How to query MongoDB with "like"?

Freewind Source

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

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

How to do the same in MongoDB?
I can't find a operator for like in the documentations.

mongodbmongodb-querysql-like

Answers

answered 8 years ago Joshua Partogi #1

You would use regex for that in mongo.

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

answered 8 years ago Kyle H #2

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 Regexp's '.*'), not something that has "m" anchored to the beginning of the string.

answered 7 years ago leon #3

In PHP, you could use following code:

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

answered 6 years ago Afshin Mehrabani #4

In

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

you can do:

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

answered 5 years ago Crasher #5

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/

answered 4 years ago Eddy #6

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

answered 4 years ago user2312578 #7

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

answered 4 years ago MAA #8

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.

answered 4 years ago Johnathan Douglas #9

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

answered 4 years ago cmarrero01 #10

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

answered 4 years ago Dap #11

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)

answered 4 years ago The6thSense #12

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

answered 3 years ago Vaibhav #13

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

answered 3 years ago prayagupd #14

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]

answered 3 years ago Shaishab Roy #15

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

answered 3 years ago Aqib Mumtaz #16

For Mongoose in Node.js

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

answered 3 years ago Shalabh Raizada #17

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

answered 3 years ago sravanthi #18

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

answered 2 years ago Somnath Muluk #19

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.

answered 2 years ago jarry jafery #20

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/

answered 2 years ago Lakmal Vithanage #21

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

answered 2 years ago Bruno Bronosky #22

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

answered 2 years ago Albert s #23

MongoRegex has been deprecated.
Use MongoDB\BSON\Regex

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

answered 1 year ago CEDA #24

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

answered 1 year ago damd #25

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

answered 1 year ago user3645907 #26

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!

answered 1 year ago Shubham Verma #27

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

answered 1 year ago priya raj #28

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!!!

answered 10 months ago dinesh vishe #29

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

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

answered 7 months ago kz_sergey #30

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

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

answered 3 months ago user3284463 #31

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

answered 2 months ago besthost #32

Using template literals with variables also works:

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

answered 1 month ago saim2025 #33

You can also use the wildcard filter as follows:

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

be sure to use *.

comments powered by Disqus