MongoDB查询：获取当前暑期申请者信息及已完成暑期次数

我们需要从MongoDB的Applicant和Application两个集合中查询数据，满足以下要求：

• 仅返回当前暑期的申请记录
• 每条结果展示申请者的FirstName、LastName、当前申请的Status、EmailAddress
• 同时统计该申请者所有状态为Completed的申请总次数

集合结构示例 #

Application集合 #

{
  "_id": { "$oid": "6324c900ba6fad569a175778" },
  "Applicant": { "ID": { "$oid": "6324c900ba6fad569a175777" } },
  "Summer": { "ID": { "$oid": "932567532532d278b614742a" } },
  "Status": "Hired",
  ...
},
{
  "_id": { "$oid": "6424c900ba6fad569a175765" },
  "Applicant": { "ID": { "$oid": "6324c900ba6fad5d9a175777" } },
  "Summer": { "ID": { "$oid": "5624c900ba6fadd59a17577e" } },
  "Status": "Complete",
  ...
}

Applicant集合 #

{
  "_id": { "$oid": "6324c900ba6fad5d9a175777" },
  "FirstName": "Ted",  
  "LastName": "Doe",
  "CellPhoneNumber": "1234567890",
  "HomePhoneNumber": null,
  "EmailAddress": "ted@somewhere.com",  
  "MailingAddress": {
    "Address1": "123 Some Rd",
    "Address2": "",
    "City": "Somewhere",
    "State": "TX",
    "PostalCode": "23334",
    "Country": "United States"
  },
  ...
}

预期输出示例 #

First Last  Status    EmailAddress         Completed Summers
John  Doe   Pending   jdoe@somewhere.com   3
Jane  Smith Hired     janie@somewhere.com  2
Fred  Smith Withdrawn fred@somewhere.com   0
Ted   Doe   Hired     ted@somewhere.com    2
Sally So    Completed sally@somewhere.com  3

现有查询的问题 #

• 第一种查询：能获取当前暑期申请者的基本信息，但无法统计所有Completed状态的申请次数
• 第二种查询：能统计次数，但无法正确关联当前暑期的申请信息，也未过滤当前暑期的记录

最优查询方案 #

我们可以通过先统计所有申请者的Completed次数，再关联当前暑期的申请和申请者信息的方式实现，具体聚合管道如下：

[
  // 1. 统计每个申请者的Completed状态申请次数
  {
    $match: { Status: "Completed" }
  },
  {
    $group: {
      _id: "$Applicant.ID",
      completedCount: { $sum: 1 }
    }
  },
  {
    $set: { applicantId: "$_id" }
  },
  // 2. 关联当前暑期的Application记录，仅获取对应状态
  {
    $lookup: {
      from: "Application",
      let: { applicantId: "$applicantId" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$Applicant.ID", "$$applicantId"] },
            "Summer.ID": ObjectId("932567532532d278b614742a") // 替换为当前暑期ID
          }
        },
        {
          $project: { Status: 1 }
        }
      ],
      as: "currentSummerApplication"
    }
  },
  { $unwind: { path: "$currentSummerApplication", preserveNullAndEmptyArrays: true } },
  // 3. 关联Applicant集合获取个人信息
  {
    $lookup: {
      from: "Applicant",
      localField: "applicantId",
      foreignField: "_id",
      as: "applicantInfo"
    }
  },
  { $unwind: "$applicantInfo" },
  // 4. 整理输出字段，处理空值情况
  {
    $project: {
      _id: 0,
      FirstName: "$applicantInfo.FirstName",
      LastName: "$applicantInfo.LastName",
      Status: { $ifNull: ["$currentSummerApplication.Status", "No Current Application"] },
      EmailAddress: "$applicantInfo.EmailAddress",
      "Completed Summers": { $ifNull: ["$completedCount", 0] }
    }
  },
  // 可选：仅保留有当前暑期申请的记录，取消注释以下阶段
  // {
  //   $match: { Status: { $ne: "No Current Application" } }
  // }
]

方案说明 #

1. 统计Completed次数：先过滤所有Status为Completed的申请，按申请者ID分组统计总次数
2. 关联当前暑期申请：使用带子管道的$lookup，精准关联当前暑期的申请记录，只提取所需的状态字段
3. 关联申请者信息：通过申请者ID关联Applicant集合，获取个人基本信息
4. 字段整理：用$ifNull处理无Completed记录（默认设为0）或无当前暑期申请的情况，确保输出格式统一
5. 可选过滤：如果只需要展示有当前暑期申请的申请者，添加最后的$match阶段即可

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