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

Answer 1

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 (see docs) 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).

Answer 2

It works too:

db.getCollection('collection_name').find({"field_name": /^searched_value/})

Answer 3

If you want to use mongo JPA like query you should try this.

@Query("{ 'title' : { $regex: '^?0', $options: 'i' } }")
List<TestDocument> findLikeTitle(String title);

Answer 4

db.users.insert({name: 'patrick'})
db.users.insert({name: 'petra'})
db.users.insert({name: 'pedro'})

Therefore:

For:

db.users.find({name: /a/})  // Like '%a%'

Output: patrick, petra

For:

db.users.find({name: /^pa/}) // Like 'pa%'

Output: patrick

For:

db.users.find({name: /ro$/}) // Like '%ro'

Output: pedro

Answer 5

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$/ })

Answer 6

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

Answer 7

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)

Answer 8

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".

Answer 9

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,

1. $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/]

2. $all: (it is similar to a $and condition) a document should contain all words

db.users.find({ name: { $all: name } }); // name = [/My/, /Name/]

Using nested $and and $or conditionals and $regex

There are two scenarios to match a string,

1. $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
  ]
})

Playground

2. $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
  ]
})

Playground

Answer 10

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.*/

Answer 11

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.*/]}})

Answer 12

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.

Answer 13

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.

Answer 14

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/})

Answer 15

Here is the command which uses the "starts with" paradigm:

db.customer.find({"customer_name" : { $regex : /^startswith/ }})

Answer 16

You can also use the wildcard filter as follows:

{"query": { "wildcard": {"lookup_field":"search_string*"}}}

Be sure to use *.

Answer 17

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"}
})

Answer 18

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.

Answer 19

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.

Answer 20

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);
}

Answer 21

MongoRegex has been deprecated.

Use MongoDB\BSON\Regex:

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

Answer 22

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" }

Answer 23

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%"
});

Answer 24

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

Answer 25

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

db.collection.find({name:{'$regex' : 'string', '$options' : 'i'}})

Doesn't contain string, only with a regular expression

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 Regular Expressions Cheat Sheet as a bookmark, and a reference for any other alterations you may need.

Answer 26

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});

Answer 27

For Mongoose in Node.js:

db.users.find({'name': {'$regex': '.*sometext.*'}})

Answer 28

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);
            });

Answer 29

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]

Answer 30

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));