能否用MongoDB聚合连接子查询？求等效指定SQL的单聚合查询

1. 能否使用MongoDB聚合操作连接子查询？ #

可以。MongoDB聚合框架通过$lookup（实现类似SQL JOIN的关联）、$facet（并行生成多个数据集）、嵌套管道（$lookup的pipeline参数）等功能，能够模拟SQL中子查询关联的逻辑。你可以先通过聚合阶段生成类似子查询的中间数据集，再对这些数据集进行关联操作，只是实现逻辑和SQL的子查询写法有所不同。

2. 等效给定SQL的单条MongoDB聚合查询 #

存在等效的单条聚合查询。针对你提供的SQL，核心逻辑是先分别关联T3&T4、T5&T6得到两个中间数据集，再将这两个数据集按C1和C2字段关联，最后提取指定字段。以下是两种实现方式：

方式一：分步生成中间数据集再关联 #

db.t3.aggregate([
  // 生成T1：T3与T4关联
  {
    $lookup: {
      from: "t4",
      localField: "C0",
      foreignField: "C0",
      as: "t4_data"
    }
  },
  { $unwind: "$t4_data" }, // 展开关联结果，模拟内连接
  {
    $project: {
      _id: 0,
      C1: "$C1",
      C2: "$t4_data.C2"
    }
  },
  { $group: { _id: null, T1: { $push: "$$ROOT" } } },
  
  // 生成T2：T5与T6关联
  {
    $lookup: {
      from: "t5",
      pipeline: [
        {
          $lookup: {
            from: "t6",
            localField: "C0",
            foreignField: "C0",
            as: "t6_data"
          }
        },
        { $unwind: "$t6_data" },
        {
          $project: {
            _id: 0,
            C1: "$C1",
            C2: "$t6_data.C2"
          }
        }
      ],
      as: "T2"
    }
  },
  
  // 关联T1和T2，匹配C1、C2相等的记录
  { $unwind: "$T1" },
  { $unwind: "$T2" },
  {
    $match: {
      $expr: {
        $and: [
          { $eq: ["$T1.C1", "$T2.C1"] },
          { $eq: ["$T1.C2", "$T2.C2"] }
        ]
      }
    }
  },
  
  // 提取并整理目标字段
  {
    $project: {
      _id: 0,
      C1: "$T1.C1",
      C2: "$T2.C2"
    }
  }
])

方式二：用$facet并行生成中间数据集 #

db.t3.aggregate([
  {
    $facet: {
      // 并行生成T1数据集
      T1: [
        {
          $lookup: {
            from: "t4",
            localField: "C0",
            foreignField: "C0",
            as: "t4_data"
          }
        },
        { $unwind: "$t4_data" },
        {
          $project: {
            _id: 0,
            C1: "$C1",
            C2: "$t4_data.C2"
          }
        }
      ],
      // 并行生成T2数据集
      T2: [
        {
          $lookup: {
            from: "t5",
            pipeline: [
              {
                $lookup: {
                  from: "t6",
                  localField: "C0",
                  foreignField: "C0",
                  as: "t6_data"
                }
              },
              { $unwind: "$t6_data" },
              {
                $project: {
                  _id: 0,
                  C1: "$C1",
                  C2: "$t6_data.C2"
                }
              }
            ],
            as: "t5_t6"
          }
        },
        { $unwind: "$t5_t6" },
        { $replaceRoot: { newRoot: "$t5_t6" } }
      ]
    }
  },
  // 关联两个数据集并过滤匹配条件
  { $unwind: "$T1" },
  { $unwind: "$T2" },
  {
    $match: {
      $expr: {
        $and: [
          { $eq: ["$T1.C1", "$T2.C1"] },
          { $eq: ["$T1.C2", "$T2.C2"] }
        ]
      }
    }
  },
  // 输出目标字段
  {
    $project: {
      _id: 0,
      C1: "$T1.C1",
      C2: "$T2.C2"
    }
  }
])

注意事项 #

• 上述查询通过$unwind自动过滤未匹配的文档，模拟SQL的内连接效果；如果需要保留未匹配记录，可以添加preserveNullAndEmptyArrays: true参数到$unwind中。
• 若数据集较大，笛卡尔积关联可能影响性能，建议对关联字段（C0、C1、C2）建立索引，或考虑拆分查询优化性能。

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