Use $regex inside $expr in mongodb aggregation

Question

My doc looks as follows

doc = {
    name: 'abc',
    age:20
}

and my query looks like

{ $expr: {$and:[{ $gt:[ "$age",  10 ] },
                { $regex:["$name",'ab']}
               ]
          }
} }

But it's not working and I get an error

Unrecognized expression '$regex'

How can I make it work?

My original query looks like this

db.orders.aggregate([{
$match: {}},
{$lookup: {
    from: "orders",
    let: {
        "customer_details": "$customerDetails"
    },
    pipeline: [
        {
            $match: {
                $expr: {
                        $and: [
                                { $or: [
                                                {
                                                $eq: ["$customerDetails.parentMobile","$$customer_details.parentMobile"]
                                                },
                                                {$eq: ["$customerDetails.studentMobile","$$customer_details.parentMobile"]
                                                },
                                                {$eq: ["$customerDetails.studentMobile","$$customer_details.parentMobile"]
                                                },
                                                {$eq: ["$customerDetails.studentMobile","$$customer_details.studentMobile"]
                                                }
                                            ]
                                        },
                                {$eq: ["$customerDetails.zipCode","$$customer_details.zipCode"]},
                                {$eq: ["$customerDetails.address","$$customer_details.address"]}
                        ]
                    }

            }
        }],
    as: "oldOrder"
}
}])

I want to use regex for matching address.

Any help will be greatly appreciated. Thanks in advance.

could you add a sample of the data?, it seems you do the lookup from the same collection "orders"? — Mohammed Yousry
– Mohammed Yousry, Commented Apr 28, 2020 at 21:59

Ashh · Accepted Answer · 2020-04-28 21:45:23Z

9

$regex is a query operator you cannot use inside $expr because it only supports aggregation pipeline operators.

{
  "$expr": { "$gt": ["$age", 10] } ,
  "name": { "$regex": "ab" }
}

If you have mongodb 4.2, you can use $regexMatch

{ "$expr": {
  "$and": [
    { "$gt": ["$age", 10] },
    {
      "$regexMatch": {
        "input": "$name",
        "regex": "ab", //Your text search here
        "options": "i",
      }
    }
  ]
}}

edited Apr 28, 2020 at 21:45

answered Apr 28, 2020 at 20:11

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ganesh Over a year ago

How can do fuzzy matching inside expr , because I'm using $and inside $expr

Ashh Over a year ago

Above query will satisfy the $and condition. BTW what is your mongodb version?

ganesh Over a year ago

problem with $regexMatch is , I'm using this $expr inside match of $lookup , inside $lookup i'm using variable declaration and using those variable inside $regexMatch as $regexMatch: {input :"$name" , regex:"$$NAME"} where NAME is variable declared in $lookup

Ashh Over a year ago

@ganesh this is working mongoplayground.net/p/kwMV8iHIVQK

Ashh Over a year ago

Pls show your actual documents and query then only I can help.

Mohammed Yousry · Accepted Answer · 2020-04-28 20:20:27Z

5

If your mongoDB version is 4.2, then you can use $regexMatch

try this

db.collection.find({
  $expr: {
    $and: [
      {
        $gt: [
          "$age",
          10
        ]
      },
      {
        $regexMatch: {
          input: "$name",
          regex: "ab"
        }
      }
    ]
  }
})

check this Mongo Playground

answered Apr 28, 2020 at 20:20

Mohammed Yousry

2,1841 gold badge8 silver badges7 bronze badges

3 Comments

ganesh Over a year ago

what if i want to pass "ab" as a variable like "$$name" not a hardcoded string

Mohammed Yousry Over a year ago

Could you add the whole query to the question?

Mohammed Yousry Over a year ago

and a sample of your data, and the expected output too

Collectives™ on Stack Overflow

Use $regex inside $expr in mongodb aggregation

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related