Query a subdocument and select filtered data from it Mongoose

Issue

I want to query a subdocument that lives within a parent document and only select (return in the express response) a specific subdocument with it’s own data, but the way I’m doing it, it’s selecting/returning all the subdocuments, without filtering the one I need, which I’m trying to filter by the field candidateId

Here is my query:

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" });
  }
);

Here is my document and subdocument structure:

{
    "_id" : ObjectId("61223b3c88e21fe0ee69d689"),
    "username" : "randomUserName",
    "email" : "[email protected]",
    "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
}

Below, you can see what my query returns, which basically is the whole candidates collection, instead of the filtered subdocuments that I need:

{
    "Candidato": [
        {
            "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"
        }
            ]
        }
    ]
}

Important Note: remember that I just want it to return either 123 or 1234 subdocument, depending on the candidateId I pass down to the query

Here is a workaround I found but I think is a bit dirty and would appreciate another alternative:

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 });
        }
      }
    );

With this workaround, I get what I need, but it looks a bit messy and I feel there might be some other cleaner solution. This is what I get returned:

{
    "Candidato": [
        {
            "_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"
        },
    ]
}

Any ideas? Thank you!

Issue solved by @eol – Simple note

As said, @eol worked it out and shared the solution. I took his answer and translated it into JavaScript/Mongoose sintaxis and here it goes:

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" });
  }
);

Solution

You can do a simple aggregation where you first $match documents with the given email, then $unwind the candidates elements and the filter those who $match the given id:

db.collection.aggregate([
  {
    "$match": {
      email: "[email protected]"
    }
  },
  {
    "$unwind": "$candidates"
  },
  {
    "$match": {
      "candidates.candidateId": 1234
    }
  }
])

Here’s an example on mongoplayground: https://mongoplayground.net/p/GPB3oYqe5em

Answered By – eol

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0