MongoDB如何实现等效SQL WITH子句的多表左连接查询？

MongoDB 没有提供与 SQL WITH（公共表表达式/CTE）直接对应的独立操作符，但通过聚合管道的阶段组合，可以完全复现你给出的示例查询逻辑，无需提前创建中间表。

你给出的SQL逻辑可以拆解为两部分：

• 分别从table1、table2查询指定字段、生成计算字段，得到两个临时结果集t1、t2
• 对t1、t2按field1字段做左连接，返回两个结果集的所有字段

推荐实现方案（MongoDB 5.0+ 版本，性能最优） #

直接利用$lookup的关联子管道能力，在关联阶段直接完成t2的结果预计算，和WITH子句的执行逻辑完全对齐，不需要额外缓存中间结果。
对应你的SQL示例，假设table1对应MongoDB集合collection1，table2对应collection2，聚合管道写法如下：

db.collection1.aggregate([
  // 等效 WITH 中 t1 的定义逻辑：从table1提取需要的字段、生成计算字段
  {
    $project: {
      _id: 0, // 不需要_id字段可手动关闭，按需调整
      field1: 1,
      field2: 1,
      calculatedField: 1 // 计算字段可直接在此处用聚合表达式生成，也可提前加$addFields阶段计算
    }
  },
  // 左连接逻辑，子管道内直接完成t2的定义，等效先查t2再关联
  {
    $lookup: {
      from: "collection2", // 对应SQL中的table2
      let: { join_key: "$field1" }, // 把t1的关联字段传入子管道
      pipeline: [
        // 等效 WITH 中 t2 的定义逻辑：从table2提取需要的字段、生成计算字段
        {
          $project: {
            _id: 0,
            field1: 1,
            field2: 1,
            calculatedField: 1
          }
        },
        // 匹配关联条件：t2.field1 = t1.field1
        {
          $match: {
            $expr: { $eq: ["$field1", "$$join_key"] }
          }
        }
      ],
      as: "t2_matched" // 关联匹配到的t2结果存入该字段
    }
  },
  // 展开关联结果，保留未匹配的t1记录，完全对齐SQL LEFT JOIN行为
  {
    $unwind: {
      path: "$t2_matched",
      preserveNullAndEmptyArrays: true
    }
  },
  // 把t2的字段合并到根层级，等效 SELECT a.*, b.*
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          "$t2_matched",
          { t2_matched: "$$REMOVE" } // 删除临时存储关联结果的字段
        ]
      }
    }
  }
])

多CTE复用场景实现方案 #

如果你的WITH子句定义了多个来自同一集合的临时结果，且后续逻辑需要多次引用同一个临时结果，可以使用$facet阶段一次性生成所有临时结果集，避免重复计算：

// 适用于多个临时结果均来自同一个集合的场景
db.collection1.aggregate([
  {
    $facet: {
      // 等效WITH中第一个临时结果的定义
      t1: [
        { $project: { field1:1, field2:1, calculatedField:1 } }
      ],
      // 等效WITH中第二个临时结果的定义
      t_other: [
        { $match: { status: "active" } },
        { $project: { field1:1, other_field:1 } }
      ]
    }
  }
  // 后续阶段对facet生成的多个临时结果做关联、合并即可
])

注意事项 #

• 左连接展开关联结果时，必须给$unwind设置preserveNullAndEmptyArrays: true，否则未匹配到t2记录的t1数据会被过滤，退化为内连接，和SQL LEFT JOIN行为不一致。
• 如果你定义的临时结果集需要被多个查询重复使用，可以创建只读视图做持久化，语法为db.createView("视图名", "源集合名", [对应临时结果的聚合管道阶段])，后续查询直接关联视图即可。
• MongoDB 5.0以下版本不支持$lookup携带自定义子管道，可以提前对两个集合做投影、计算字段生成，再用普通$lookup按字段关联，逻辑完全一致。

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