How to use regex in the lookup pipeline?

[Sudhesh_Gnanasekaran]

Consider this aggregate query as a sample, and I want to fetch the results based on the code which contains the first 5 digits of the ProductSerial. So I’m using the starts with regex option to pull the data. How to frame the regex string from previous stage values.

  db.getCollection("ProductInfo").find(
    { 
        "ProductSerial" : /^38990.*/i
    }

Aggregate:

  $Project:
  {
    ProductCode : '$ProductCode' 
  }
  
  
  $lookup: {
        from: 'ForeignCollection',
        let: {
            variable: '$ProductCode'
        },
        pipeline: [
		    {               
			   {
                $match: {
                    ProductSerial: {
                        $regex: "^$$variable.*",
                        $options: "i"
                    }
                }
            }
        ],
        as: "Product"
    }

[Pavel_Duchovny]

Hi @Sudhesh_Gnanasekaran ,

I think you should build the $let stage with $concat expression :

let: {
            variable: {$concat :["^",'$ProductCode',".*"]}
        }

And use $expr in your match :

{
                $match: {
                  $expr : {  ProductSerial: {
                        $regex: "$$variable",
                        $options: "i"
                    }
                }}
            }

Perhaps it might work even without concat as variable in a match only work with expr

Thanks
Pavel

[Asya_Kamsky]

You cannot mix aggregation and match (find) expressions, @Pavel_Duchovny - if you are inside $expr then you can only use aggregation syntax, and Field:{$regex: ... is a match expression.

[Asya_Kamsky]

If you are on 4.2 or later, you can adjust the suggestion from @Pavel_Duchovny and use this aggregation regex expression.

So if you have a previous stage which figures out the “starts with” substring, you can use that inside $expr after assigning it to a variable like you are doing.

The stage would be:

{$lookup: {
    from: 'ForeignCollection',
    let: { regex: {$concat:["^", '$ProductCode']}},
    pipeline: [
         { $match: {$expr: {
               $regexMatch:{
                   input: "$ProductSerial",
                   regex: "$$regex",
                   options: "i"
               }
        }}}
    ],
    as: "Product"
}}