mongodb - special - pymongo like query




How to query MongoDB with “like”? (20)

I want to query something as SQL's like query:

select * 
from users 
where name like '%m%'

How to do the same in MongoDB?
I can't find a operator for like in the documentations.


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

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


For Mongoose in Node.js

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

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

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

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

If you're using PHP, you can use 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);
}

In

  • PyMongo using Python
  • Mongoose using Node.js
  • Jongo, using Java
  • mgo, using Go

you can do:

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

In Go and the mgo driver:

Collection.Find(bson.M{"name": bson.RegEx{"m", ""}}).All(&result)

where result is the struct instance of the sought after type


In PHP, you could use following code:

$collection->find(array('name'=> array('$regex' => 'm'));

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.

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

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" -> BSONRegex(".*"+name+".*", "")) //like
val sortQ = BSONDocument("regularizedCorRelation" -> BSONInteger(1))
val cursor = collection.find(query).sort(sortQ).options(QueryOpts().batchSize(10)).cursor[BSONDocument]

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'=>{'$search': "The string"}
})

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.


Use aggregation substring search (with index!!!):

db.collection.aggregate([{
        $project : {
            fieldExists : {
                $indexOfBytes : ['$field', 'string']
            }
        }
    }, {
        $match : {
            fieldExists : {
                $gt : -1
            }
        }
    }, {
        $limit : 5
    }
]);

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

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


You would use regex for that in mongo.

e.g: db.users.find({"name": /^m/})


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!!!





sql-like