Advertisement
Advertisement


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 to do I achieve the same in MongoDB? I can't find an operator for like in the documentation.

2019/03/12
1
1486
3/12/2019 10:51:33 AM

Accepted Answer

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.

note: mongodb uses regular expressions which are more powerful than "LIKE" in sql. With regular expressions you can create any pattern that you imagine.

For more info on regular expressions refer to this link https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions

2019/11/14
2009
11/14/2019 12:11:12 PM

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

2015/01/28

In

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

you can do:

db.users.find({'name': {'$regex': 'sometext'}})
2017/04/27

In PHP, you could use following code:

$collection->find(array('name'=> array('$regex' => 'm'));
2015/06/10

You would use regex for that in mongo.

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

2010/07/22

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.

2016/08/11

Source: https://stackoverflow.com/questions/3305561
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]