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

User · Answer

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       amp       return new RegExp escaped   i        So now  the string becomes a case-insensitive pattern matching also the literal dot  Example    gt   textLike  A      lt    A   i   Now we are ready to generate the regular expression on the go   db users find    name   textLike  m

User · Answer

gt  gt  db car distinct  name      honda    tat    tata    tata3      gt  gt  db car find   name      ta

User · Answer

For PHP mongo Like  I had several issues with php mongo like  i found that concatenating the regex params helps in some situations PHP mongo find field starts with  I figured I would post on here to contribute to the more popular thread  e g  db  - gt users- gt insert   name    gt   john     db  - gt users- gt insert   name    gt   joe     db  - gt users- gt insert   name    gt   jason         starts with  like var    jo    prefix          suffix         name    prefix    like var    suffix  db  - gt users- gt find   name    gt  array   regex   gt new MongoRegex  name      output   joe  john      contains  like var    j    prefix         suffix         name    prefix    like var    suffix  db  - gt users- gt find   name    gt  array   regex   gt new MongoRegex  name       output   joe  john  jason

User · Answer

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

User · Answer

Using template literals with variables also works     firstname     regex       req body firstname        options   si

User · Answer

In    PyMongo using Python Mongoose using Node js  Jongo  using Java mgo  using Go   you can do   db users find   name      regex    sometext

User · Answer

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 Regexp s        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 info on regular expressions refer to this link  https   developer mozilla org en-US docs Web JavaScript Guide Regular Expressions

User · Answer

In nodejs project and use mongoose use 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

User · Answer

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

User · Answer

There are already many answers  I am giving different types of requirements and solutions for string search with regex     You can do with regex which contain word i e like  Also you can use  options   gt  i for case insensitive search  Contains string  db collection find  name    regex     string     options     i       Doesn t Contains string only with regex  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 this as a bookmark  and a reference for any other alterations you may need

User · Answer

It seems that there are reasons for using both the javascript  regex pattern  pattern as well as the mongo    regex    regex pattern   pattern  See  MongoBD RegEx Syntax Restrictions   This is not a complete RegEx tutorial  but I was inspired to run these tests after seeing a highly voted ambiguous post above    gt    abbbb   bbabb   bbbba   forEach function v  db test collection insert  val  v       gt  db test collection find  val   a       val     abbbb       val     bbabb       val     bbbba      gt  db test collection find  val     a         val     abbbb       val     bbabb       val     bbbba      gt  db test collection find  val     a         val     bbabb      gt  db test collection find  val    a       val     abbbb      gt  db test collection find  val   a        val     bbbba      gt  db test collection find  val     regex    a         val     bbbba

User · Answer

Like Query would be as shown below  db movies find  title     Twelve Monkeys      sort  regularizedCorRelation   1   limit 10     for scala ReactiveMongo api    val query   BSONDocument  title  - gt  BSONRegex      name              like val sortQ   BSONDocument  regularizedCorRelation  - gt  BSONInteger 1   val cursor   collection find query  sort sortQ  options QueryOpts   batchSize 10   cursor BSONDocument

User · Answer

You have 2 choices    db users find   name    string      or  db users find   name      regex    string     options    i       On 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 regular expression as you want   Enjoy

User · Answer

Regex are expensive are 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 text index   db collection find       text   gt    search    The string

User · Answer

One way to find the result as with equivalent to like query  db collection find  name    regex     string     options     i       Where  i use for cases insensitive fetch data  Another way by which we can get result also  db collection find   quot name quot   aus      Above will provide the result  which have the aus in name cantaing aus

User · Answer

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

User · Answer

In Go and the mgo driver   Collection Find bson M  name   bson RegEx  m         All  amp result    where result is the struct instance of the sought after type

User · Answer

Already u got the answers but to match regex 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 more pretty output

User · Answer

As Mongo shell support regex  that s completely possible   db users findOne   name       sometext         If we want the query to be case-insensitive  we can use  i  option  like shown below   db users findOne   name       sometext   i

User · Answer

If you re using PHP  you can use MongoDB DataObject wrapper like below    model   new MongoDB DataObject      model- gt query  select   from users where name like   m       while  model- gt fetch          var dump  model       OR    model   new MongoDB DataObject  users     model- gt whereAdd  name like   m        model- gt find     while  model- gt fetch          var dump  model

User · Answer

If you want  Like  search in mongo then you should go with  regex by using this query will be  db product find  name   regex  m i     for more you can read the documentation as well  https   docs mongodb com manual reference operator query regex

User · Answer

Above answers are perfectly answering the questions about core mongodb query  But when using pattern based search query such as     quot keywords quot     quot  regex quot    quot  toron   quot     or    quot keywords quot     quot  regex quot    quot  toron quot     in spring boot jpa repository query with  Query annotation  use query something like   Query value    quot   keyword      regex    0      quot   List lt SomeResponse gt  findByKeywordContainingRegex String keyword    and call should be either of  List lt SomeResponse gt  someResponseList      someRepository findByKeywordsContainingRegex  quot  toron quot     List lt SomeResponse gt  someResponseList      someRepository findByKeywordsContainingRegex  quot  toron   quot     But Never use  List lt SomeResponse gt  someResponseList   someRepository findByKeywordsContainingRegex  quot   toron  quot     List lt SomeResponse gt  someResponseList  someRepository findByKeywordsContainingRegex  quot   toron    quot     Important point to note  each time  0 field in  Query statement is replaced with double quoted string  so forwardslash   should not be used in these cases  always go for pattern using double quotes in searching pattern   eg  use  quot  toron quot  or  quot  toron   quot  over   toron  or   toron

User · Answer

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    quot name quot   new Regex name       Is the same result as  db users find   quot name quot    John

User · Answer

Just in case  someone is looking for a sql LIKE kind of query for a key that holds an Array of Strings instead of a String  here it is  db users find   quot name quot     in      m

User · Answer

db customer find   customerid      regex    CU 00000      options    i     pretty     When we are searching for string patterns  always it is better to use the above pattern as when we are not sure about case  Hope that helps

User · Answer

In SQL  the    like    query is looks like this    select   from users where name like   m     In MongoDB console  it looks like this    db users find   name    m           Not JSON formatted  db users find   name    m    pretty       JSON formatted   In addion pretty   method will in all the places where produce formatted JSON structure which is more readable

User · Answer

In PHP  you could use following code    collection- gt find array  name   gt  array   regex    gt   m

User · Answer

If you are using Spring-Data Mongodb You can do this in this way   String tagName    m   Query query   new Query    query limit 10           query addCriteria Criteria where  tagName   regex tagName

User · Answer

You can use where statement to build any JS script   db myCollection find     where   this name toLowerCase   indexOf  m    gt   0         Reference  http   docs mongodb org manual reference operator where

User · Answer

MongoRegex has been deprecated  Use MongoDB BSON Regex   regex   new MongoDB BSON Regex     m     cursor    collection- gt find array  users    gt   regex      iterate through the cursor

User · Answer

db users insert  name   paulo    db users insert  name   patric    db users insert  name   pedro     db users find  name   a       like   a     out  paulo  patric  db users find  name    pa      like  pa      out  paulo  patric  db users find  name   ro       like   ro    out  pedro

User · Answer

You can also use the wildcard filter as follows     query      wildcard     lookup field   search string        be sure to use

User · Answer

I found a free tool to translate MYSQL queries to MongoDB  http   www querymongo com  I checked with several queries  as i see almost all them are correct  According to that  The answer is   db users find        name     m

User · Answer

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

User · Answer

Use aggregation substring search  with index       db collection aggregate             project                 fieldExists                      indexOfBytes      field    string                                             match                 fieldExists                      gt   -1                                           limit   5

User · Answer

You can use the new feature of 2 6 mongodb   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

User · Answer

For Mongoose in Node js  db users find   name      regex      sometext

User · Answer

There are various ways to accomplish this  simplest-one db users find   quot name quot    m        lt field gt      regex   pattern    options    lt options gt          lt field gt      regex   pattern    options    lt options gt          lt field gt      regex   pattern  lt options gt       db users find    quot name quot      regex   quot m quot       More details can be found here https   docs mongodb com manual reference operator query regex

User · Answer

You would use regex for that in mongo  e g  db users find   quot name quot     m

User · Answer

String 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

User · Answer

Here is the command which uses starts with paradigm  db customer find   customer name       regex     startswith

User · Answer

split name string by space and make array of words map to iterate loop and convert string to regex of each word of name   x000D   x000D  let name    My Name  split      map n   gt  new RegExp n    console log name   x000D   x000D   x000D   Result    My    Name    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

[sql] How to query MongoDB with "like"?

Examples related to sql

Examples related to mongodb

Examples related to mongodb-query

Examples related to sql-like