Regular Expression query for MongoDB

Question

I have users collection with these documents.
[
    {
        email:'abc+100@gmail.com'
    },{
        email:'abc+2345@gmail.com',
    },{
        email:'abc+234577@gmail.com',
    },{
        email:'abc+23450077@gmail.com',
    },{
        email:'abc+23450077-345@gmail.com',
    }
]
I want to search users with partial email or full email like ex--
req.body.email='abc+2345';//partial search
let email = req.body.email;
req.body.email='abc+234577@gmail.com';//full email search

Mongo query to search partial or full using Regx

Users.find({$or:[{ 'email': { $regex: email, '$options': 'i' }}]})

It is unable to find users whose email contain 'abc+2345' or 'abc+234577@gmail.com'.

score 0 · Answer 1 · answered Aug 07 '19 at 03:45

0

We need to escape '+' in the regex. The following query can also get you the expected output:

db.check.find({"email":/abc\+2345/i}).pretty()

Output:

{
    "_id" : ObjectId("5d4a4735cbfa696dcd99249f"),
    "email" : "abc+2345@gmail.com"
}
{
    "_id" : ObjectId("5d4a4735cbfa696dcd9924a0"),
    "email" : "abc+234577@gmail.com"
}
{
    "_id" : ObjectId("5d4a4735cbfa696dcd9924a1"),
    "email" : "abc+23450077@gmail.com"
}
{
    "_id" : ObjectId("5d4a4735cbfa696dcd9924a2"),
    "email" : "abc+23450077-345@gmail.com"
}

answered Aug 07 '19 at 03:45

Himanshu Sharma

2,940
1
7
18

How i'll use escape character in regx using variable name like email?? – sameer Aug 07 '19 at 03:55
@sameer Please refer https://stackoverflow.com/questions/3561493/is-there-a-regexp-escape-function-in-javascript – Himanshu Sharma Aug 07 '19 at 04:41

score 0 · Answer 2 · answered Aug 07 '19 at 03:55

0

do it like this

MongoDB Enterprise > db.user.find({$or :[{ email: { $regex: /^abc\+2345/ ,$options :'i'} },{email:"six666@gmail.com"}]})
{ "_id" : ObjectId("5d4a477dd72dc9d0a4b71d85"), "email" : "abc+2345@gmail.com" }
{ "_id" : ObjectId("5d4a477dd72dc9d0a4b71d86"), "email" : "abc+234577@gmail.com" }
{ "_id" : ObjectId("5d4a477dd72dc9d0a4b71d87"), "email" : "abc+23450077@gmail.com" }
{ "_id" : ObjectId("5d4a477dd72dc9d0a4b71d88"), "email" : "abc+23450077-345@gmail.com" }
{ "_id" : ObjectId("5d4a4b67d72dc9d0a4b71d89"), "email" : "six666@gmail.com" }
MongoDB Enterprise >

in $or the first stage is with regex ,second used for full match

answered Aug 07 '19 at 03:55

HbnKing

1,762
1
11
25

in $or the first stage is with regex,you have used with static email value; but i want escaping with dynamic email value using just req.body.email – sameer Aug 07 '19 at 04:02
with mongo shell or driver api ? – HbnKing Aug 07 '19 at 04:09
with driver api using node mongoose – sameer Aug 07 '19 at 04:10
you have to use `pattern` with it now . only when your data is very simple . you can try `string pattern` – HbnKing Aug 07 '19 at 04:23

Regular Expression query for MongoDB

2 Answers2