Combining $regex and $or operators in Mongo

Question

I want to use $or and $regex operators same time.

db.users.insert([{name: "Alice"}, {name: "Bob"}, {name: "Carol"}, {name: "Dan"}, {name: "Dave"}])

Using $regex works fine:

> db.users.find({name: {$regex: "^Da"}})
{ "_id" : ObjectId("53e33682b09f1ca437078b1d"), "name" : "Dan" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1e"), "name" : "Dave" }

When introducing $or, the response is changed. I expected the same response:

> db.users.find({name: {$regex: {$or: ["^Da"]}}})
{ "_id" : ObjectId("53e33682b09f1ca437078b1a"), "name" : "Alice" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1b"), "name" : "Bob" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1c"), "name" : "Carol" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1d"), "name" : "Dan" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1e"), "name" : "Dave" }

I also tried to change the order of the operators:

> db.users.find({name: {$or: [{$regex: "^Da"}, {$regex: "^Ali"}]}})
error: { "$err" : "invalid operator: $or", "code" : 10068 }

However, it seems that following query works fine, but it's a little bit long (name is repeated):

> db.users.find({$or: [{name: {$regex: "^Da"}}, {name: {$regex: "^Ali"}}]})
{ "_id" : ObjectId("53e33682b09f1ca437078b1a"), "name" : "Alice" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1d"), "name" : "Dan" }
{ "_id" : ObjectId("53e33682b09f1ca437078b1e"), "name" : "Dave" }

Is there any shorter way to use $regex and $or in queries like this?

The goal is to use $regex operator and not /.../ (real regular expressions).

Glottis

FWIW, you can (and probably should) do the or in the regex. ^(?:Da|Ali). – Hallway

Answer 1

The $or operator expects whole conditions so the correct form would be:

db.users.find({ "$or": [
    { "name": { "$regex": "^Da"} }, 
    { "name": { "$regex": "^Ali" }}
]})

Or of course using $in:

db.users.find({ "name": { "$in": [/^Da/,/^Ali/] } })

But it's a regex so you can do:

db.users.find({ "name": { "$regex": "^Da|^Ali" } })

Recollection

why does the regex (the last one) not work if I dont put double quotes around the query? ("^Da|^Ali") and what is the difference between using the $in for the /pattern/ instead of doing it with only regex? – Halliehallman

Answer 2

It is been a while. However, I would add case insensitive to the regex query like the query below. So that, it doesn't matter if names were saved into the database with capital letters:

db.users.find({ "name": { "$regex": "^Da|^Ali", "$options": "i" } })

Hope it helps

Postmaster

if you're planning to index the name field you'll find mongo doesn't use the index as soon as you include the case insensitive option - better to store the names lower case and make sure your term is lowercase – Murcia

Answer 3

It seems when you have $and or $or and multiple search based and used at least one $regex you have to use $regex for all conditions. First from below works ok, second more like $or operator.

db.big_data.users.find(
    { $and: [ 
       { sex: { $regex: /^M.*/ } },
       { name: { $regex: /^J.*/ } } 
    ] })


db.big_data.users.find({ $and: [ {sex: "M"}, { name: { $regex: /^J*/m } } ] })

Kwok

Answer 4

you can use OR operator like

db.collName.find({ "name": { "$regex": "^Da|^Ali" ,"$options": "i" } })

and operator

db.collName.find({ "name": { "$regex": "Ali" ,"$options": "i" } })

for more info source - https://www.cs.jhu.edu/~jason/405/lectures1-2/sld049.htm

Interrupted