MongoDB: Not getting any result with $regex with $match and $or for multiple column search

Question

I'm using mongodb 4.4 and making aggregate query to find data from the collection.

Here is my code:

db.getCollection('users').aggregate([
    {
        $match: {
            $or: [{
                "name": {
                    $regex: /a/,
                    $options: 'i'
                },
                "email": {
                    $regex: /a/,
                    $options: 'i'
                }
            }]
        }
    }
])

I want to do query just like SQL like on all column of the user collection. I am not getting any result with this.

CASE 1: If I use /(:?)/ it does return me all the collection and that works fine CASE 2: If i user /ad/ it does not return anything even though there is email and name containing admin string.

remove `/`, when you use single/double quotes in `$regex` operator. use `/a/` or `'a'` — turivishal, Jun 28 '21 at 05:09
I have edited question. But seems it still abnormal behaviour as well — Mysterious Coder, Jun 28 '21 at 05:13
can you explain more what is *abnormal behaviour* in terms of error/problem? provide some example document and your expectation from that document. — turivishal, Jun 28 '21 at 05:17
@turivishal please check above edited question and cases at the bottom — Mysterious Coder, Jun 28 '21 at 05:19
it is the correct query and It should return, see working [playground](https://mongoplayground.net/p/8cebY9M0q1A), probably you are missing something. — turivishal, Jun 28 '21 at 05:23
You have user 'ad' as a string which also works for me but regex is not working, — Mysterious Coder, Jun 28 '21 at 05:30
I have done same for dynamic values but that's the thing still not working — Mysterious Coder, Jun 28 '21 at 05:35
we can't help you without showing your implementation. look at the similar question. — turivishal, Jun 28 '21 at 06:00
Does this answer your question? [How to query MongoDB with "like"](https://stackoverflow.com/questions/3305561/how-to-query-mongodb-with-like) — turivishal, Jun 28 '21 at 06:00
I have checked and followed same as well but it is not working as expected — Mysterious Coder, Jun 28 '21 at 06:15
Your query looks good, i have provided solution for js regex, and shared a similar question as well, so we can't help you on the base of your statement *is not working as expected*. we don't have any superpower to debug your code in your machine, you need to share more details, implementation in nodejs. — turivishal, Jun 28 '21 at 06:25
Both are same query, in your question and in your answer, i don't see any problem in $or in your question. — turivishal, Jun 28 '21 at 06:27

score -1 · Answer 1 · answered Jun 28 '21 at 06:24

Finally found the issue after getting too much over the docs.

there is was a slight problem with the syntax of $or

what worked for me is something like this:

db.getCollection('users').aggregate([
    {
        $match: {
                    $or: [
                        {
                            "name": {
                                $regex: /a/,
                                $options: 'i'
                            }
                        },
                        {
                            "email": {
                                $regex: /a/,
                                $options: 'i'
                            }
                        }
                    ]
                }
    }
])

MongoDB: Not getting any result with $regex with $match and $or for multiple column search

1 Answers1