MongoDB不同集合字段对比查询匹配无结果问题排查

需求说明 #

需要对多个集合（对应关系型数据库中的表）的多个字段值做比对，输出符合关联条件的结果，对应实现该逻辑的SQL语句如下：

select pd.service_id,ps.service_id from player pd, service ps where pd.subject_id=ps.subject_id and pd.service_id = ps.service_id

原有问题实现 #

初始编写的MongoDB聚合查询语句如下：

db.player.aggregate([
        {
          "$lookup":{
            "from":"service",
            "localField":"player.subject_id",
            "foreignField":"subject_id",
            "as":"ps"
          }
        },
        {
          "$unwind":"$ps"
        },
        {
          "$match":{
           "service_id":{
            "$eq": "ps.service_id"
           }
          }
        }  
      ]

测试使用的样本数据：

• player集合：

[{subject_id:23,service_id:1},{subject_id:76,service_id:9}]

• service集合：

[{subject_id:76,service_id:9},{subject_id:99,service_id:10}]

执行现象：查询的$match阶段未正常生效，无法筛选出两个集合中service_id一致的记录，执行后无任何返回结果。

错误点说明 #

语句共有两处核心错误：

1. $lookup阶段关联字段路径配置错误：player集合的文档直接存储subject_id字段，不存在player.subject_id这个嵌套字段路径，导致关联阶段无法正确匹配到service集合的对应文档。
2. $match阶段等值判断逻辑错误：直接写"$eq": "ps.service_id"是将player集合的service_id字段值和*字符串常量ps.service_id*做比较，并非引用$unwind展开后ps子文档里的service_id字段值做字段间比对；MongoDB中要做两个字段的等值判断，必须使用$expr操作符包裹条件。

修正后的实现 #

推荐直接在$lookup阶段通过自定义pipeline完成多条件关联，减少后续阶段的处理开销，语句如下：

db.player.aggregate([
  {
    $lookup: {
      from: "service",
      // 声明player侧需要用到的关联字段变量
      let: {
        p_subject_id: "$subject_id",
        p_service_id: "$service_id"
      },
      // 关联pipeline中直接完成双字段匹配
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$subject_id", "$$p_subject_id"] },
                { $eq: ["$service_id", "$$p_service_id"] }
              ]
            }
          }
        }
      ],
      as: "matched_ps"
    }
  },
  // 展开匹配到的service记录
  { $unwind: "$matched_ps" },
  // 输出和原SQL一致的字段
  {
    $project: {
      "pd.service_id": "$service_id",
      "ps.service_id": "$matched_ps.service_id",
      _id: 0
    }
  }
])

如果要保留原有的lookup+unwind+match结构，修正后的语句如下：

db.player.aggregate([
  {
    "$lookup":{
      "from":"service",
      "localField":"subject_id", // 修正错误的字段路径
      "foreignField":"subject_id",
      "as":"ps"
    }
  },
  {
    "$unwind":"$ps"
  },
  {
    "$match":{
      $expr: { // 用$expr实现两个字段的等值比对
        $eq: ["$service_id", "$ps.service_id"]
      }
    }
  },
  {
    $project: {
      "pd.service_id": "$service_id",
      "ps.service_id": "$ps.service_id",
      _id: 0
    }
  }
])

针对提供的样本数据，上述两个修正语句执行后都会返回预期结果：{ "pd.service_id": 9, "ps.service_id": 9 }，和原SQL的查询结果完全一致。

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