MongoDB左连接如何避免匹配空值？聚合查询结果异常求助

问题描述 #

要实现与目标SQL一致的左连接逻辑：查询ParentRef为null的部门作为N1，依次左连接N2、N3、N4层级部门。但执行原聚合代码时出现异常：当N2的Syscode为空时，N3的Code和Name错误显示为N1的对应值。

原聚合代码：

db.Department.aggregate([
  {
    $match: {
      ParentRef: null
    }
  },
  {
    $lookup: {
      from: "Department",
      localField: "Syscode",
      foreignField: "ParentRef",
      as: "N2"
    }
  },
  {
    $unwind: {
      path: "$N2",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "Department",
      localField: "N2.Syscode",
      foreignField: "ParentRef",
      as: "N3"
    }
  },
  {
    $unwind: {
      path: "$N3",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "Department",
      localField: "N3.Syscode",
      foreignField: "ParentRef",
      as: "N4"
    }
  },
  {
    $unwind: {
      path: "$N4",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      "N1_Code": "$Code",
      "N1_Name": "$Name",
      "N2_Code": "$N2.Code",
      "N2_Name": "$N2.Name",
      "N3_Code": "$N3.Code",
      "N3_Name": "$N3.Name",
      "N4_Code": "$N4.Code",
      "N4_Name": "$N4.Name"
    }
  }
])

目标SQL逻辑：

select N1.Code, N1.Name, N2.Code, N2.Name, N3.Code, N3.Name, N4.Code, N4.Name
from Department  as N1
LEFT JOIN Department as N2 ON N1.Syscode = N2.ParentRef  
LEFT JOIN Department as N3 ON N2.Syscode = N3.ParentRef   
LEFT JOIN Department as N4 ON N3.Syscode = N4.ParentRef   
WHERE N1.ParentRef IS NULL 

问题原因 #

当N2不存在（即$unwind后N2为null），执行N3的$lookup时，localField: "N2.Syscode"会解析为null。此时MongoDB会匹配foreignField: "ParentRef"等于null的所有文档——也就是最初的N1级部门，导致N3错误关联到N1的数据，而非保持null，违背了左连接的预期行为。

解决方案 #

改用$lookup的pipeline形式，通过条件判断精确控制关联逻辑：只有当父级的Syscode不为空时才执行关联，否则返回空数组，确保空值场景下后续层级保持null，与SQL左连接行为一致。

修改后的聚合代码：

db.Department.aggregate([
  {
    $match: {
      ParentRef: null
    }
  },
  // 关联N2：原逻辑不变，N1的ParentRef已过滤为null，默认N1.Syscode非空
  {
    $lookup: {
      from: "Department",
      localField: "Syscode",
      foreignField: "ParentRef",
      as: "N2"
    }
  },
  {
    $unwind: {
      path: "$N2",
      preserveNullAndEmptyArrays: true
    }
  },
  // 关联N3：仅当N2.Syscode不为空时才匹配
  {
    $lookup: {
      from: "Department",
      let: { n2Syscode: "$N2.Syscode" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$ParentRef", "$$n2Syscode"] },
                { $ne: ["$$n2Syscode", null] }
              ]
            }
          }
        }
      ],
      as: "N3"
    }
  },
  {
    $unwind: {
      path: "$N3",
      preserveNullAndEmptyArrays: true
    }
  },
  // 关联N4：仅当N3.Syscode不为空时才匹配
  {
    $lookup: {
      from: "Department",
      let: { n3Syscode: "$N3.Syscode" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$ParentRef", "$$n3Syscode"] },
                { $ne: ["$$n3Syscode", null] }
              ]
            }
          }
        }
      ],
      as: "N4"
    }
  },
  {
    $unwind: {
      path: "$N4",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      "N1_Code": "$Code",
      "N1_Name": "$Name",
      "N2_Code": "$N2.Code",
      "N2_Name": "$N2.Name",
      "N3_Code": "$N3.Code",
      "N3_Name": "$N3.Name",
      "N4_Code": "$N4.Code",
      "N4_Name": "$N4.Name"
    }
  }
])

代码说明 #

1. 使用let定义父级Syscode变量，通过$expr在$match中编写关联条件
2. 添加$ne: ["$$xxxSyscode", null]判断，确保只有父级部门存在时才执行关联
3. 若父级部门不存在（变量为null），pipeline返回空数组，$unwind后对应层级为null，完全匹配SQL左连接的行为

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