Mongoose如何查询父文档中匹配candidateId的指定子文档

需求说明 #

需要查询嵌套在父文档中的子文档，希望在Express接口的响应中仅返回携带自身数据的特定子文档，但初始实现会返回所有子文档，无法按照candidateId字段过滤出目标子文档。

初始查询代码 #

interviewRouter.get(
  "/:idCandidato&:userEmail",
  async (req: Request, res: Response) => {
    const { idCandidato, userEmail } = req.params;

    parseInt(idCandidato);

    const infoCandidato = await User.find({
      email: userEmail,
      "candidates.candidateId": idCandidato,
    }).select({"_id": 0, "candidates": 1});

    infoCandidato
      ? res.status(200).json({ Candidato: infoCandidato })
      : res.status(404).json({ Candidato: "Candidato no encontrado" });
  }
);

父文档与子文档结构示例 #

{
    "_id" : ObjectId("61223b3c88e21fe0ee69d689"),
    "username" : "randomUserName",
    "email" : "random@email.com",
    "pictureUrl" : "some/url/to/the/user/picture/username",
    "role" : "admin",
    "candidates" : [
        {
            "_id" : ObjectId("612242cd11a7f1ebc6a18661"),
            "candidateName" : "some candidates name",
            "candidateId" : 123,
            "candidateInfo" : [
                {
                    "_id" : ObjectId("612242cd11a7f1ebc6a18662"),
                    "currentSituation" : "3",
                    "motivationToChange" : "3",
                    "postSavingDate" : ISODate("2021-08-22T12:27:57.490Z")
                },
                {
                    "_id" : ObjectId("612242cd11a7f1ebc6a04854"),
                    "currentSituation" : "6",
                    "motivationToChange" : "7",
                    "postSavingDate" : ISODate("2021-08-22T13:56:57.490Z")
                }
            ],
            "availableNow" : false,
            "mainSkills" : "MM"
        },
        {
            "_id" : ObjectId("612261abf7b68bfaf56345df"),
            "candidateName" : "some other guy",
            "candidateId" : 1234,
            "candidateInfo" : [
                {
                    "_id" : ObjectId("612261abf7b68bfaf56345e0"),
                    "currentSituation" : "dunno",
                    "motivationToChange" : "no idea",
                    "postSavingDate" : ISODate("2021-08-22T14:39:39.161Z")
                }
            ],
            "availableNow" : false,
            "mainSkills" : "JavaScript"
        }
    ],
    "__v" : 0
}

临时解决方案（手动过滤） #

初始查询返回了完整的candidates数组而非过滤后的目标子文档，最初的临时方案为查询到结果后手动过滤candidates数组，但该方案实现不够优雅：

interviewRouter.get(
  "/:idCandidato&:userEmail",
  async (req: Request, res: Response) => {
    const { idCandidato, userEmail } = req.params;

    const infoCandidato = await User.findOne(
      {
        email: userEmail,
        "candidates.candidateId": idCandidato,
      },
      function (err: any, response: any) {
        if (err) {
          res.status(400).send(err);
        } else {
          let newResponse = response["candidates"].filter((element: any) => {
            return element.candidateId === parseInt(idCandidato);
          });
          res.status(200).json({ Candidato: newResponse });
        }
      }
    );

最终最优方案（聚合查询实现） #

通过aggregate聚合查询实现了更简洁的过滤逻辑，代码如下：

interviewRouter.get(
  "/:idCandidato&:userEmail",
  async (req: Request, res: Response) => {
    const { idCandidato, userEmail } = req.params;
    const infoCandidato = await User.aggregate([
      {
        "$match": {
          email: userEmail
        }
      },
      {
        "$unwind": "$candidates"
      },
      {
        "$match": {
          "candidates.candidateId": parseInt(idCandidato)
        }
      }]);
      infoCandidato.length !== 0
      infoCandidato
      ? res.status(200).json({ Candidato: infoCandidato })
      : res.status(404).json({ Candidato: "Candidato no encontrado" });
  }
);

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