How to query MongoDB with "like"
SqlMongodbMongodb QuerySql LikeSql Problem Overview
I want to query something with SQL's like
query:
SELECT * FROM users WHERE name LIKE '%m%'
How can I achieve the same in MongoDB? I can't find an operator for like
in the documentation.
Sql Solutions
Solution 1 - Sql
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 regular expressions' '.*
'), 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 information on regular expressions, refer to Regular expressions (MDN).
Solution 2 - Sql
db.users.insert({name: 'paulo'})
db.users.insert({name: 'patric'})
db.users.insert({name: 'pedro'})
Therefore:
For:
db.users.find({name: /a/}) // Like '%a%'
Output: paulo, patric
For:
db.users.find({name: /^pa/}) // Like 'pa%'
Output: paulo, patric
For:
db.users.find({name: /ro$/}) //like '%ro'
Output: pedro
Solution 3 - Sql
In
- PyMongo using Python
- Mongoose using Node.js
- Jongo, using Java
- mgo, using Go
you can do:
db.users.find({'name': {'$regex': 'sometext'}})
Solution 4 - Sql
In PHP, you could use the following code:
$collection->find(array('name'=> array('$regex' => 'm'));
Solution 5 - Sql
Here are different types of requirements and solutions for string search with regular expressions.
You can do with a regular expression which contains a word, i.e., like. Also you can use $options => i
for a case insensitive search.
string
Contains db.collection.find({name:{'$regex' : 'string', '$options' : 'i'}})
string
, only with a regular expression
Doesn't contain db.collection.find({name:{'$regex' : '^((?!string).)*$', '$options' : 'i'}})
string
Exact case insensitive db.collection.find({name:{'$regex' : '^string$', '$options' : 'i'}})
string
Start with db.collection.find({name:{'$regex' : '^string', '$options' : 'i'}})
string
End with db.collection.find({name:{'$regex' : 'string$', '$options' : 'i'}})
Keep Regular Expressions Cheat Sheet as a bookmark, and a reference for any other alterations you may need.
Solution 6 - Sql
You would use a regular expression for that in MongoDB.
For example,
db.users.find({"name": /^m/})
Solution 7 - Sql
You have two choices:
db.users.find({"name": /string/})
or
db.users.find({"name": {"$regex": "string", "$options": "i"}})
For the 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 a regular expression as you want.
Solution 8 - Sql
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') } );
Solution 9 - Sql
Already you got the answers, but to match with a regular expression 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 prettier output.
Solution 10 - Sql
For Mongoose in Node.js:
db.users.find({'name': {'$regex': '.*sometext.*'}})
Solution 11 - Sql
You can use the new feature of MongoDB 2.6:
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"
}
});
Solution 12 - Sql
In a Node.js project and using Mongoose, use a 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);
});
Solution 13 - Sql
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 '
)
Solution 14 - Sql
You can use a where statement to build any JavaScript script:
db.myCollection.find( { $where: "this.name.toLowerCase().indexOf('m') >= 0" } );
Reference: $where
Solution 15 - Sql
In Go and the mgo driver:
Collection.Find(bson.M{"name": bson.RegEx{"m", ""}}).All(&result)
where the result is the struct instance of the sought-after type.
Solution 16 - Sql
For PHP mongo Like.
I had several issues with PHP mongo like. I found that concatenating the regular expression parameters helps in some situations - https://stackoverflow.com/questions/25872398/php-mongo-find-field-starts-with.
For example,
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)
Solution 17 - Sql
Using template literals with variables also works:
{"firstname": {$regex : `^${req.body.firstname}.*` , $options: 'si' }}
Solution 18 - Sql
In SQL, the ‘like’ query looks like this:
select * from users where name like '%m%'
In the MongoDB console, it looks like this:
db.users.find({"name": /m/}) // Not JSON formatted
db.users.find({"name": /m/}).pretty() // JSON formatted
In addition, the pretty()
method will produce a formatted JSON structure in all the places which is more readable.
Solution 19 - Sql
Regular expressions are expensive to 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 the text index:
db.collection.find({
'$text'=>{'$search': "The string"}
})
Solution 20 - Sql
In MongoDb, can use like using MongoDb reference operator regular expression(regex).
For Same Ex.
MySQL - SELECT * FROM users WHERE name LIKE '%m%'
MongoDb
1) db.users.find({ "name": { "$regex": "m", "$options": "i" } })
2) db.users.find({ "name": { $regex: new RegExp("m", 'i') } })
3) db.users.find({ "name": { $regex:/m/i } })
4) db.users.find({ "name": /mail/ })
5) db.users.find({ "name": /.*m.*/ })
MySQL - SELECT * FROM users WHERE name LIKE 'm%'
MongoDb Any of Above with /^String/
6) db.users.find({ "name": /^m/ })
MySQL - SELECT * FROM users WHERE name LIKE '%m'
MongoDb Any of Above with /String$/
7) db.users.find({ "name": /m$/ })
Solution 21 - Sql
String yourdb={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
Solution 22 - Sql
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);
Solution 23 - Sql
It seems that there are reasons for using both the JavaScript /regex_pattern/
pattern as well as the MongoDB {'$regex': 'regex_pattern'}
pattern. See: MongoDB RegEx Syntax Restrictions
This is not a complete regular expression 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" }
Solution 24 - Sql
A like query would be as shown below:
db.movies.find({title: /.*Twelve Monkeys.*/}).sort({regularizedCorRelation : 1}).limit(10);
For the 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]
Solution 25 - Sql
If you are using Spring-Data MongoDB, you can do it in this way:
String tagName = "m";
Query query = new Query();
query.limit(10);
query.addCriteria(Criteria.where("tagName").regex(tagName));
Solution 26 - Sql
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/})
Solution 27 - Sql
As the MongoDB shell supports regular expressions, that's completely possible.
db.users.findOne({"name" : /.*sometext.*/});
If we want the query to be case-insensitive, we can use the "i" option, like shown below:
db.users.findOne({"name" : /.*sometext.*/i});
Solution 28 - Sql
If you want a 'like' search in MongoDB then you should go with $regex. By using it, the query will be:
db.product.find({name:{$regex:/m/i}})
For more, you can read the documentation as well - $regex
Solution 29 - Sql
Use aggregation substring search (with index!!!):
db.collection.aggregate([{
$project : {
fieldExists : {
$indexOfBytes : ['$field', 'string']
}
}
}, {
$match : {
fieldExists : {
$gt : -1
}
}
}, {
$limit : 5
}
]);
Solution 30 - Sql
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") });
Solution 31 - Sql
One way to find the result as with equivalent to a like query:
db.collection.find({name:{'$regex' : 'string', '$options' : 'i'}})
Where i
is used for a case-insensitive fetch data.
Another way by which we can also get the result:
db.collection.find({"name":/aus/})
The above will provide the result which has the aus in the name containing aus.
Solution 32 - Sql
MongoRegex has been deprecated.
Use MongoDB\BSON\Regex:
$regex = new MongoDB\BSON\Regex ( '^m');
$cursor = $collection->find(array('users' => $regex));
//iterate through the cursor
Solution 33 - Sql
Use:
db.customer.find({"customerid": {"$regex": "CU_00000*", "$options": "i"}}).pretty()
When we are searching for string patterns, it is always better to use the above pattern as when we are not sure about case.
Solution 34 - Sql
Use:
const indexSearch = await UserModel.find(
{ $text: { $search: filter } },
);
if (indexSearch.length) {
return indexSearch;
}
return UserModel.find(
{
$or: [
{ firstName: { $regex: `^${filter}`, $options: 'i' } },
{ lastName: { $regex: `^${filter}`, $options: 'i' } },
{ middleName: { $regex: `^${filter}`, $options: 'i' } },
{ email: { $regex: `^${filter}`, $options: 'i' } },
],
},
);
I used a combination of regex and "index".
Solution 35 - Sql
I found a free tool to translate MySQL queries to MongoDB: http://www.querymongo.com/
I checked with several queries. As I see it, almost all of them are correct. According to that, the answer is
db.users.find({
"name": "%m%"
});
Solution 36 - Sql
There are various ways to accomplish this.
The 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 in $regex.
Solution 37 - Sql
Using a JavaScript RegExp
- split the
name
string by space and make an array of words - map to an iterate loop and convert the string to a regex of each word of the name
let name = "My Name".split(" ").map(n => new RegExp(n));
console.log(name);
Result:
[/My/, /Name/]
There are two scenarios to match a string,
$in
: (it is similar to the$or
condition)
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/]
$all
: (it is similar to a$and
condition) a document should contain all words
db.users.find({ name: { $all: name } }); // name = [/My/, /Name/]
$and
and $or
conditionals and $regex
Using nested There are two scenarios to match a string,
$or
: (it is similar to the$in
condition)
db.users.find({
$or: [
{ name: { $regex: "My" } },
{ name: { $regex: "Name" } }
// if you have multiple fields for search then repeat same block
]
})
$and
: (it is similar to the$all
condition) a document should contain all words
db.users.find({
$and: [
{
$and: [
{ name: { $regex: "My" } },
{ name: { $regex: "Name" } }
]
}
// if you have multiple fields for search then repeat same block
]
})
Solution 38 - Sql
If you're using PHP, you can use the 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);
}
Solution 39 - Sql
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();
Solution 40 - Sql
For the Go driver:
filter := bson.M{
"field_name": primitive.Regex{
Pattern: keyword,
Options: "",
},
}
cursor, err := GetCollection().Find(ctx, filter)
Use a regex in the $in query (MongoDB documentation: $in):
filter := bson.M{
"field_name": bson.M{
"$in": []primitive.Regex{
{
Pattern: keyword,
Options: "",
},
}
}
}
cursor, err := GetCollection().Find(ctx, filter)
Solution 41 - Sql
>> db.car.distinct('name')
[ "honda", "tat", "tata", "tata3" ]
>> db.car.find({"name":/. *ta.* /})
Solution 42 - Sql
You can also use the wildcard filter as follows:
{"query": { "wildcard": {"lookup_field":"search_string*"}}}
Be sure to use *
.
Solution 43 - Sql
Here is the command which uses the "starts with" paradigm:
db.customer.find({"customer_name" : { $regex : /^startswith/ }})
Solution 44 - Sql
Just in case, someone is looking for an 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.*/]}})
Solution 45 - Sql
The previous answers are perfectly answering the questions about the core MongoDB query. But when using a pattern-based search query such as:
> {"keywords":{ "$regex": "^toron.*"}}
or
> {"keywords":{ "$regex": "^toron"}}
in a Spring Boot JPA repository query with @Query annotation, use a query something like:
@Query(value = "{ keyword : { $regex : ?0 } }")
List<SomeResponse> findByKeywordContainingRegex(String keyword);
And the 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.*/");
An important point to note: each time the ?0 field in @Query statement is replaced with a double quoted string. So forwardslash (/) should not be used in these cases! Always go for a pattern using double quotes in the searching pattern!! For example, use "^toron" or "^toron.*"
over /^toron/ or /^toron.*/