single chained query mongodb / mongoose to fetch all comments

Question

I am trying to develop a personal project, a website that functions in a similair way to Stack Exchange, a user can ask a question which can receive multiple answers. Each question and answer can have multiple comments.

I am using nodeJS for my backend.

How can I fetch all comments for all the answers on a particular question in a single mongoDB / mongoose query?

It would be even more helpful if you could tell me how to fetch all comments for all the answers on a particular question as well as all the comments for the question in a single mongoDB / mongoose query?

Mongoose Schemas:

const questionSchema = new mongoose.Schema({
    title: String,
    content: String
})

const answerSchema = new mongoose.Schema({
    questionId: String,
    content: String,
})

const commentSchema = new mongoose.Schema({
    idQuestion: String, // nullable
    idAnswer: String, // nullable
    content: String
})

Currently, I am performing a mongoose query to find all the answers for a particular questions. Then, using forEach, performing a mongoose query on each answer to find all the comments for each answer. I believe this is very taxing, performance wise and is not an ideal way to do what I would like to achieve.

Answer 1

You can try below aggregation. Match on question id followed by join to lookup all the answers ids with question id followed by lookup to pull in all comments.

db.questions.aggregate([
  {"$match":{"_id":input_question_id}},
  {"$lookup":{
    "from":"answers",
    "localField":"_id",
    "foreignField":"questionId",
    "as":"answers"
  }},
  {"$lookup":{
    "from":"comments",
    "let":{"ids":{"answers_id":"$answers._id","question_id":"$_id"}},
    "pipeline":[
      {"$match":{"$expr":{
        "$or":[
          {"$eq":["$idQuestion","$$ids.question_id"]},
          {"$in":["$idAnswer","$$ids.answers_id"]}
        ]
      }}}
    ],
    "as":"comments"
  }},
  {"$project":{"comments":"$comments.content"}}
])

Working example here - https://mongoplayground.net/p/qBlKqk-JsxA

Answer 2

You can try,

• $match your conditions questionId
• $lookup join with comments

db.answers.aggregate([
  { $match: { questionId: 1 } },
  {
    $lookup: {
      from: "comments",
      localField: "_id",
      foreignField: "idAnswer",
      as: "comments"
    }
  }
])

Playground

---

Second approach, if you want to select questions with all answers and comment then try,

• $match your conditions
• $lookup with pipeline join with answers collection
  • pipeline field can allow to add all stages of pipeline that we are using in root level
  • $match questionId and get answers
  • $lookup join with comments collection

db.questions.aggregate([
  { $match: { _id: 1 } }, // this is optional if you want to select all questions then remove this 
  {
    $lookup: {
      from: "answers",
      let: { questionId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$questionId", "$questionId"] } } },
        {
          $lookup: {
            from: "comments",
            localField: "_id",
            foreignField: "idAnswer",
            as: "comments"
          }
        }
      ],
      as: "answers"
    }
  }
])

Playground

---

Show or hide extra fields, you can use $project operator at the end of above query,

• You can show fields as per your needs

  {
    $project: {
      _id: 1,
      content: 1,
      "comments._id": 1,
      "comments.content": 1
    }
  }

Playground

---

Suggestions:

I am not sure, you have already done or not, but try to define object id type in reference field instead of string type, like I have updated your schema, this will add a default index in object id and this will increase a speed of fetching data,

const answerSchema = new mongoose.Schema({
    questionId: mongoose.Types.ObjectId,
    content: String,
})

const commentSchema = new mongoose.Schema({
    idQuestion: mongoose.Types.ObjectId, // nullable
    idAnswer: mongoose.Types.ObjectId, // nullable
    content: String
})