How to make the "LIKE" query work in MongoDB?

Question

I have a list of street names and I want to select all that start with "Al". In my MySQL, I would do something like

SELECT * FROM streets WHERE "street_name" LIKE "Al%" 

What about MongoDB using PHP?

Answer 1

Use regex:

 db.streets.find( { street_name : /^Al/i } ); 

or

 db.streets.find( { street_name : { $regex : '^Al', $options: 'i' } } ); 

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-RegularExpressions

Including this in PHP:

 $regex = new MongoRegex("/^Al/i"); $collection->find(array('street_name' => $regex)); 

Answer 2

See: http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

In addition, we strongly recommend that you use only the native mongodb connector from PHP instead of the shell. It is faster than any shell.

http://php.net/class.mongodb

Answer 3

MongoRegex is deprecated.
Use MongoDB \ BSON \ Regex

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

Answer 4

here is my working example:

 <?php use MongoDB\BSON\Regex; $collection = $yourMongoClient->yourDatabase->yourCollection; $regex = new Regex($text, 's'); $where = ['your_field_for_search' => $regex]; $cursor = $collection->find($where); //Lets iterate through collection 

Answer 5

$collection.find({"name": /.*Al.*/})

or similarly

$collection.find({"name": /Al/})

You are looking for something that contains "Al" somewhere (the SQL statement "%" is equivalent to regexps' '. *'), And not something that has "Al" attached to the beginning of the line.

Answer 6

 <?php $mongoObj = new MongoClient(); $where = array("name" => new MongoRegex("^/AI/i")); $mongoObj->dbName->collectionName->find($where); ?> 

View for more details.

Answer 7

You can also do something like this

 ['key' => ['$regex' => '(?i)value']]