如何在Mongoose中实现多表LEFT JOIN并对字段进行模糊匹配？

场景说明 #

有三个MongoDB集合，需通过code字段关联，但各集合的code编码位置不同：

• table1：code为纯编码字符串，例："13421"
• table2：code为前缀+编码，例："42932-13421"
• table3：code为特殊字符+编码，例："()13421"

在MySQL中可通过LIKE语句实现这类关联，示例代码：

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

问题重现 #

尝试用Mongoose聚合查询实现时，写出如下代码：

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

执行后触发MongoDB错误：

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

错误原因 #

1. $regexMatch的regex参数不支持{ $regex: ..., $options: ... }格式，需直接使用正则表达式字符串或对象
2. 子管道无法直接引用主集合的code字段，需通过$lookup的let参数传递变量，再用$$变量名引用

正确实现代码 #

使用$lookup的let传递主集合的code值，在子管道中通过$concat动态生成匹配正则，实现模糊关联：

table1.aggregate([
  // 关联table2
  {
    $lookup: {
      from: "table2",
      let: { table1Code: "$code" }, // 将table1的code赋值为变量供子管道使用
      pipeline: [
        {
          $match: {
            $expr: {
              $regexMatch: {
                input: "$code", // 引用table2的code字段
                regex: { $concat: [".*", "$$table1Code", ".*"] }, // 动态拼接正则：匹配包含table1Code的任意字符串
                options: "i" // 忽略大小写
              }
            }
          }
        }
      ],
      as: "table2Data"
    }
  },
  // 关联table3（逻辑与table2一致）
  {
    $lookup: {
      from: "table3",
      let: { table1Code: "$code" },
      pipeline: [
        {
          $match: {
            $expr: {
              $regexMatch: {
                input: "$code",
                regex: { $concat: [".*", "$$table1Code", ".*"] },
                options: "i"
              }
            }
          }
        }
      ],
      as: "table3Data"
    }
  }
])

代码说明 #

• let：将主集合（table1）的code字段定义为变量，供子管道调用
• $concat：动态生成正则表达式字符串，确保匹配包含table1编码的任意内容
• $regexMatch：判断子集合的code字段是否包含table1的编码，实现类似MySQL LIKE的模糊匹配

内容的提问来源于stack exchange，提问作者Frash