如何将SQL关联查询转换为Mongo查询，含exists判断及多级lookup实现

前置假设 #

MongoDB中存在4个集合，结构与SQL表逻辑对应：

• quizzes 集合：核心字段为_id（对应SQL的quizzes.id）、quiz_name、qty、count
• reports 集合：核心字段为_id、quiz_id（关联quizzes._id）、question_id（关联questions._id）、user_id（关联users._id）、report、report_desc
• questions 集合：核心字段为_id、question
• users 集合：核心字段为_id、username

查询1 对应实现 #

对应SQL逻辑：关联quizzes和reports表，过滤report字段不为空的记录，返回指定字段

db.quizzes.aggregate([
  // 关联reports集合，等效INNER JOIN的关联条件
  {
    $lookup: {
      from: "reports",
      localField: "_id",
      foreignField: "quiz_id",
      as: "report_docs"
    }
  },
  // 过滤关联后无匹配reports的记录，等效INNER JOIN的效果
  { $match: { report_docs: { $ne: [] } } },
  // 拆分数组，方便后续字段过滤
  { $unwind: "$report_docs" },
  // 匹配report字段存在且不为null，等效SQL的reports.report IS NOT NULL
  {
    $match: {
      "report_docs.report": { $exists: true, $ne: null }
    }
  },
  // 输出指定字段
  {
    $project: {
      _id: 0,
      quiz_name: 1,
      qty: 1,
      count: 1,
      report: "$report_docs.report"
    }
  }
])

查询2 对应实现 #

对应SQL逻辑：关联reports、questions、users、quizzes四个表，过滤quizzes.id=123的记录，返回指定字段（已修正原SQL的字段拼写错误）

db.quizzes.aggregate([
  // 先过滤quizzes.id=123的记录，缩小计算范围
  { $match: { _id: 123 } },
  // 第一层级关联：关联reports集合
  {
    $lookup: {
      from: "reports",
      localField: "_id",
      foreignField: "quiz_id",
      as: "report_docs"
    }
  },
  { $match: { report_docs: { $ne: [] } } },
  { $unwind: "$report_docs" },
  // 第二层级关联：关联questions集合
  {
    $lookup: {
      from: "questions",
      localField: "report_docs.question_id",
      foreignField: "_id",
      as: "question_docs"
    }
  },
  { $match: { question_docs: { $ne: [] } } },
  { $unwind: "$question_docs" },
  // 第三层级关联：关联users集合
  {
    $lookup: {
      from: "users",
      localField: "report_docs.user_id",
      foreignField: "_id",
      as: "user_docs"
    }
  },
  { $match: { user_docs: { $ne: [] } } },
  { $unwind: "$user_docs" },
  // 输出指定字段
  {
    $project: {
      _id: 0,
      username: "$user_docs.username",
      question: "$question_docs.question",
      report_desc: "$report_docs.report_desc"
    }
  }
])

核心逻辑说明 #

• lookup中使用exists判断：$lookup本身仅负责关联逻辑，字段存在性/非空判断需要放到关联后的$match阶段，使用{$exists: true, $ne: null}即可实现SQL中IS NOT NULL的效果。
• 多层级lookup关联：多层级关联仅需要在上一级关联完成后，继续追加$lookup阶段即可，每一级关联后通过$match过滤空数组、$unwind拆分数组，即可保证后续关联的准确性。

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