I have two collections 1 is campaigns and other is orders. I have to filter orders for each campaign. So what I am doing is that I'm fetching all the campaigns and after that I'm looking up for the orders that matches some specific criteria.

[
 { 
   $match: { type: 'FOLLOWUP' } 
 },
 {
   $lookup: {
     from: 'orders',
     as: 'orders',
     pipeline: [
       {
          $match: {
             'title': { $regex: '$keyword', $options: 'i' },
          }
       }
     ]
   }
 }
]

In above example every campaign contains a keyword field of type string. So I have to filter all the orders for every campaign that contains the keyword in their title and each campaign have a different keyword. How can I pass a dynamic reference to $regex, if I'm using a hard coded string it's working fine but for passing reference ('$keyword') it's not working.

Any help would be appreciated.

CodePudding user response：

You can try $regexMatch aggregation expression operator,

• let to pass keyword from campaigns collection to lookup
• $regexMatch aggregation expression operator to pass input as title and regex as keyword reference from let using $$ sign

[
  { $match: { type: "FOLLOWUP" } },
  {
    $lookup: {
      from: "orders",
      as: "orders",
      let: { keyword: "$keyword" },
      pipeline: [
        {
          $match: {
            $expr: {
              $regexMatch: {
                input: "$title",
                regex: "$$keyword",
                options: "i"
              }
            }
          }
        }
      ]
    }
  }
]

Playground