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.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
That would have to be:
Or, similar:
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).
Therefore:
For:
Output: patrick, petra
For:
Output: patrick
For:
Output: pedro
In
you can do:
In PHP, you could use the following code:
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.Contains
string
Doesn’t contain
string
, only with a regular expressionExact case insensitive
string
Start with
string
End with
string
Keep Regular Expressions Cheat Sheet as a bookmark, and a reference for any other alterations you may need.
You would use a regular expression for that in MongoDB.
For example,
You have two choices:
or
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.
If using Node.js, it says that you can write this:
Also, you can write this:
Already you got the answers, but to match with a regular expression with case insensitivity, you could use the following query:
The
i
in the/m/i
indicates case insensitivity and.pretty()
provides a prettier output.For Mongoose in Node.js:
You can use the new feature of MongoDB 2.6:
In a Node.js project and using Mongoose, use a like query:
With MongoDB Compass, you need to use the strict mode syntax, as such:
(In MongoDB Compass, it’s important that you use
"
instead of'
)You can use a where statement to build any JavaScript script:
Reference: $where
In Go and the mgo driver:
where the result is the struct instance of the sought-after type.
For PHP mongo Like.
I had several issues with PHP mongo like. I found that concatenating the regular expression parameters helps in some situations – PHP mongo find field starts with.
For example,
Using template literals with variables also works:
{"firstname": {$regex : `^${req.body.firstname}.*` , $options: 'si' }}
In SQL, the ‘like’ query looks like this:
In the MongoDB console, it looks like this:
In addition, the
pretty()
method will produce a formatted JSON structure in all the places which is more readable.String yourdb={deepakparmar, dipak, parmar}
ans deepakparmar
ans deepakparmar, dipak
ans deepakparmar, parmar
In MongoDb, can use like using MongoDb reference operator regular expression(regex).
For Same Ex.
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:
Search for a string in the text index:
Use regular expressions matching as below. The ‘i’ shows case insensitivity.
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 RestrictionsThis is not a complete regular expression tutorial, but I was inspired to run these tests after seeing a highly voted ambiguous post above.
A like query would be as shown below:
For the Scala ReactiveMongo API,
If you are using Spring-Data MongoDB, you can do it in this way:
If you have a string variable, you must convert it to a regex, so MongoDB will use a like statement on it.
Is the same result as:
As the MongoDB shell supports regular expressions, that’s completely possible.
If we want the query to be case-insensitive, we can use the "i" option, like shown below:
If you want a ‘like’ search in MongoDB then you should go with $regex. By using it, the query will be:
For more, you can read the documentation as well – $regex
Use aggregation substring search (with index!!!):
You can query with a regular expression:
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:So now, the string becomes a case-insensitive pattern matching also the literal dot. Example:
Now we are ready to generate the regular expression on the go:
One way to find the result as with equivalent to a like query:
Where
i
is used for a case-insensitive fetch data.Another way by which we can also get the result:
The above will provide the result which has the aus in the name containing aus.
MongoRegex has been deprecated.
Use MongoDB\BSON\Regex:
Use:
When we are searching for string patterns, it is always better to use the above pattern as when we are not sure about case.
There are various ways to accomplish this.
The simplest one:
More details can be found in $regex.
Using a JavaScript RegExp
name
string by space and make an array of wordsResult:
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:$all
: (it is similar to a$and
condition) a document should contain all wordsUsing nested
$and
and$or
conditionals and$regex
There are two scenarios to match a string,
$or
: (it is similar to the$in
condition)Playground
$and
: (it is similar to the$all
condition) a document should contain all wordsPlayground
Use:
I used a combination of regex and "index".
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
If you’re using PHP, you can use the MongoDB_DataObject wrapper like below:
Or:
FullName like ‘last’ with status==’Pending’ between two dates:
status== ‘Pending’ and orderId LIKE ‘PHA876174’:
For the Go driver:
Use a regex in the $in query (MongoDB documentation: $in):
You can also use the wildcard filter as follows:
Be sure to use
*
.Here is the command which uses the "starts with" paradigm:
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:
The previous answers are perfectly answering the questions about the core MongoDB query. But when using a pattern-based search query such as:
or
in a Spring Boot JPA repository query with @Query annotation, use a query something like:
And the call should be either of:
But never use:
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.*/