How do I perform a LEFT JOIN and a LIKE on a field in MongoDB with Mongoose?

Question

I have more than three tables that need to be joined with Mongoose for MongoDB, but each of these tables has a code that could be in different positions. Let me give you an example:

table1 - Code: "13421" (string)
table2 - Code: "42932-13421" (string)
table3 - Code: "()13421" (string)

So, the index code is included in the 'code' field, but it could be in different positions. I already know that in MySql I could handle it with a LIKE:

SELECT *
FROM table1 
LEFT JOIN table2 ON table2.code LIKE %table1.code%
LEFT JOIN table3 ON table3.code LIKE %table1.code%

but with Mongoose, how do I perform a lookup specifically on that field?

I think that something similar should work:

table1.aggregate(
 {
   $lookup: {
     from: 'table2',
       pipeline: [
         {
           $match: {
             $expr: {
               $regexMatch: {
                 input: '$table2.code',
                 regex: { $regex: '.*$$code.*', $options: 'i' }
               }
             }
           }
         }
       ],
       as: 'table2'
     }
   },

This is the error from MongoDB:

Error: MongoServerError: An object representing an expression must have exactly one field: { $regex: ".*code.*", $options: "i" }

Answer 1

regex expect pattern and not expression, try to change the code to:

db.table1.aggregate([
  {
    $lookup: {
      from: "table2",
      let: {
        "code1": "$code"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $regexMatch: {
                input: "$code",
                regex: "$$code1",
                options: "i"
              }
            }
          }
        }
      ],
      as: "table2"
    }
  }
])