Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 123 down vote favorite
30

I'm using mongodb 1.4.

I want query something as SQL's like.

For an example in SQL:

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

How to do the same in mongodb? I can't find a operator for like in the document of http://www.mongodb.org/display/DOCS/Advanced+Queries

share|improve this question
Your question has at least 5 votes for the like-operator tag. Could I kindly request that you suggest sql-like as a synonym? – FreshPrinceOfSO Apr 2 at 18:36
2  
thank you, I changed it – Freewind Apr 3 at 2:18

5 Answers

up vote 152 down vote accepted

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

share|improve this answer
2  
is searching by regex expensive? – Freewind Jul 22 '10 at 10:13
1  
@Freewind: NO it's not expensive. – jpartogi Jul 22 '10 at 12:51
26  
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. – Kyle Banker Jul 22 '10 at 14:49
5  
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 '10 at 18:50
1  
@Freewind: Under most circumstances, regexp searching is much more expensive than LIKE, as regular expressions can have more options. However, regular expressions have also been around for decades, and there are many, many implementations that optimize the expensive parts out, most notably the "perl-compatible regular expression" (PCRE) package. I fully endorse @kb's suggestion, the explain, to see what's being queried and its decision tree on how to process it. – Kyle H Jul 30 '10 at 1:56
show 2 more comments
up vote 22 down vote

in php, you should use coding like this:

$collection->find(array('name'=> array('$regex' => 'm'));
share|improve this answer
python + mongoengine: people = People.objects.raw_query({'name':{'$regex':'m'}}) – panchicore Aug 13 '12 at 21:56
up vote 17 down vote

You would use regex for that in mongo.

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

share|improve this answer
up vote 7 down vote

see mongodb's docs: Advanced Queries -- Regular expressions http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-RegularExpressions

share|improve this answer
up vote 6 down vote

In Python and PyMongo:

db.users.find({'name': {'$regex': 'sometext'}})
share|improve this answer
Same in Java, using Jongo ofc – kij Jun 18 at 11:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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