How to query MongoDB with "like"

user_342235 0
Viewed 1481917

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.

mongodb-query mongodb sql-like sql
45 Answers

Using template literals with variables also works:

{"firstname": {$regex : `^${req.body.firstname}.*` , $options: 'si' }}

user_55281600

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

Admin1

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

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

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

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

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.

user_61810010

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.

user_7971630

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

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

user_89871280

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

user_92216370

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)
user_71592050 
>> db.car.distinct('name')
[ "honda", "tat", "tata", "tata3" ]

>> db.car.find({"name":/. *ta.* /})
user_13959220

You can also use the wildcard filter as follows:

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

Be sure to use *.

user_56729790

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

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

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

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

user_116252530
Related