MongoDB聚合$match+$expr数组关联统计课程申请数问题

运行环境 #

• MongoDB 5.0.9

实现需求 #

• 统计各课程及其对应专业方向的申请总量
• 按课程及其对应专业方向维度，统计payment_info.status为paid的已付费申请数量

集合说明 #

• courses集合：存储课程基础信息，其中course_specialization字段为数组类型，存储课程关联的专业方向，该字段可能为null（即课程无对应专业方向）
• studentApplicationForms集合：存储学生提交的课程申请表单详情

样例数据 #

courses集合样例数据：

[
    {
      "_id": {
        "$oid": "62aab6669b3740313d881a30"
      },
      "course_name": "Master",
      "fees": "Rs.1000.0/-",
      "course_specialization": [
        {
          "spec_name": "Social Work",
          "is_activated": true
        }
      ],
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "is_pg": true
    },
    {
      "_id": {
        "$oid": "62aab6669b3740313d881a38"
      },
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "course_name": "BBA",
      "fees": "Rs.1000.0/-",
      "is_pg": false,
      "course_specialization": null
    },
    {
      "_id": {
        "$oid": "628f3967cb69fc0789e69181"
      },
      "course_name": "BTech",
      "fees": "Rs.1000.0/-",
      "course_specialization": [
        {
          "spec_name": "Computer Science and Engineering",
          "is_activated": true
        },
        {
          "spec_name": "Mutiple Specs",
          "is_activated": true
        }
      ],
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "is_pg": false
    },
    {
      "_id": {
        "$oid": "628f35a1cb69fc0789e6917e"
      },
      "course_name": "Bachelor",
      "fees": "Rs.1000.0/-",
      "course_specialization": [
        {
          "spec_name": "Social Work",
          "is_activated": true
        }
      ],
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "is_pg": false
    }
  ]

studentApplicationForms集合样例数据：

[
    {
      "_id": {
        "$oid": "62cd476adbc878a0490e20ee"
      },
      "spec_name1": "Social Work",
      "spec_name2": "",
      "spec_name3": "",
      "student_id": {
        "$oid": "62cd1374dbc878a0490e20a5"
      },
      "course_id": {
        "$oid": "62aab6669b3740313d881a30"
      },
      "current_stage": 2.5,
      "declaration": true,
      "payment_info": {
        "payment_id": "123458",
        "status": "paid"
      },
      "enquiry_date": {
        "$date": {
          "$numberLong": "1657620330432"
        }
      },
      "last_updated_time": {
        "$date": {
          "$numberLong": "1657621796062"
        }
      }
    },
    {
      "_id": {
        "$oid": "62cd476adbc878a0490e20ef"
      },
      "spec_name1": "",
      "spec_name2": "",
      "spec_name3": "",
      "student_id": {
        "$oid": "62cd1374dbc878a0490e20a5"
      },
      "course_id": {
        "$oid": "62aab6669b3740313d881a38"
      },
      "current_stage": 2.5,
      "declaration": true,
      "payment_info": {
        "payment_id": "123458",
        "status": "paid"
      },
      "enquiry_date": {
        "$date": {
          "$numberLong": "1657620330432"
        }
      },
      "last_updated_time": {
        "$date": {
          "$numberLong": "1657621796062"
        }
      }
    },
    {
      "_id": {
        "$oid": "62cdc12000b820f5ea58cc60"
      },
      "spec_name1": "Social Work",
      "spec_name2": "",
      "spec_name3": "",
      "student_id": {
        "$oid": "62cdad90a9b64d58b15e6976"
      },
      "course_id": {
        "$oid": "628f35a1cb69fc0789e6917e"
      },
      "current_stage": 6.25,
      "declaration": false,
      "payment_info": {
        "payment_id": "",
        "status": ""
      },
      "enquiry_date": {
        "$date": {
          "$numberLong": "1657651488511"
        }
      },
      "last_updated_time": {
        "$date": {
          "$numberLong": "1657651987155"
        }
      }
    }
  ]

期望输出 #

按课程下的每个专业方向输出统计结果，预期结构如下（原示例存在字段语法、专业方向匹配错误，已按业务逻辑修正）：

[
  {
    "_id": {
      "coursename": "Master",
      "spec": "Social Work",
      "Application_Count": 1,
      "Paid_Application_Count": 1
    }
  },
  {
    "_id": {
      "coursename": "Bachelor",
      "spec": "Social Work",
      "Application_Count": 1,
      "Paid_Application_Count": 0
    }
  },
  {
    "_id": {
      "coursename": "BBA",
      "spec": "",
      "Application_Count": 1,
      "Paid_Application_Count": 1
    }
  },
  {
    "_id": {
      "coursename": "BTech",
      "spec": "Computer Science and Engineering",
      "Application_Count": 0,
      "Paid_Application_Count": 0
    }
  },
  {
    "_id": {
      "coursename": "BTech",
      "spec": "Mutiple Specs",
      "Application_Count": 0,
      "Paid_Application_Count": 0
    }
  }
]

原有聚合逻辑问题说明 #

• 错误对字符串类型的course_name字段执行$unwind，导致基础数据结构异常
• 专业方向匹配逻辑不全：仅匹配spec_name1字段，漏了spec_name2/spec_name3的匹配；无专业方向的空值场景未做适配，导致对应申请统计丢失
• $facet阶段未做结果合并，三个统计分支数据无法对齐；分组时直接传入course_specialization对象而非spec_name字符串，导致跨课程同专业名分组错误
• 计数逻辑不严谨：$sum直接传入布尔表达式未做显式类型转换，部分MongoDB版本会出现统计值为0的异常

正确可执行聚合语句 #

[
  {
    $match: {
      college_id: ObjectId('628dfd41ef796e8f757a5c13')
    }
  },
  {
    $addFields: {
      course_specialization: {
        $ifNull: ["$course_specialization", [{spec_name: ""}]]
      }
    }
  },
  {
    $unwind: {
      path: "$course_specialization",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "studentApplicationForms",
      let: {
        courseId: "$_id",
        specName: "$course_specialization.spec_name"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {$eq: ["$course_id", "$$courseId"]},
                {
                  $cond: [
                    {$eq: ["$$specName", ""]},
                    {$and: [
                      {$eq: ["$spec_name1", ""]},
                      {$eq: ["$spec_name2", ""]},
                      {$eq: ["$spec_name3", ""]}
                    ]},
                    {$or: [
                      {$eq: ["$spec_name1", "$$specName"]},
                      {$eq: ["$spec_name2", "$$specName"]},
                      {$eq: ["$spec_name3", "$$specName"]}
                    ]}
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "applications"
    }
  },
  {
    $unwind: {
      path: "$applications",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        coursename: "$course_name",
        spec: "$course_specialization.spec_name"
      },
      Application_Count: {
        $sum: {$cond: [{$gt: ["$applications._id", null]}, 1, 0]}
      },
      Paid_Application_Count: {
        $sum: {$cond: [{$eq: ["$applications.payment_info.status", "paid"]}, 1, 0]}
      }
    }
  },
  {
    $project: {
      _id: {
        coursename: "$_id.coursename",
        spec: "$_id.spec",
        Application_Count: "$Application_Count",
        Paid_Application_Count: "$Paid_Application_Count"
      }
    }
  }
]

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