Slow query using aggregation framework mongodb

Admin 1
Viewed 36

I came up with this code to query a list of questions from db

question
    .aggregate([
      {
        $lookup: {
          from: "answers",
          localField: "_id",
          foreignField: "questionID",
          as: "answersArray",
        },
      },
      {
        $unwind: {
          path: "$answersArray",
          preserveNullAndEmptyArrays: true 
        },
      },
      {
        $lookup: {
          from: "sign-ups",
          localField: "answersArray.profileID",
          foreignField: "_id",
          as: "answersArray.profileID",
        },
      },
      {
        $unwind: {
          path: "$answersArray.profileID",
          preserveNullAndEmptyArrays: true 
        },
      },
      {
        $group: {
          _id: "$_id",
          answersArray: {
            $push: "$answersArray",
          },
        },
      },
      {
        $lookup: {
          from: "questions",
          localField: "_id",
          foreignField: "_id",
          as: "questionDetails",
        },
      },
      {
        $unwind: {
          path: "$questionDetails",
          preserveNullAndEmptyArrays: true 
        },
      },
      {
        $addFields: {
          "questionDetails.answersArray": "$answersArray",
        },
      },
      {
        $replaceRoot: {
          newRoot: "$questionDetails",
        },
      },
      { $sort: { _id: -1 } },
    ])

And my questions schema:

let questionSchema = mongoose.Schema({
    question : {type: String, require: true},
    question_link : {type: String},
    category : {type: String, require: true},
    userID : {type: String , require: true},
    dateTime : {type: String,require: true},
    likes : {type: Array},
    totalAnswers : {type: Number, default: 0},
})

What's happening here is, look in db for all questions, using foreign key relation find answers for that particular question and append it in the questions object as "answersArray". After that in every answer that's associated with a particular question populate the field profileID.

Result looks something like this:

[
  {
    "_id": "620b8180e2772e1b88b499e8",
    "question": "what is going on ?",
    "answersArray": [
      {
        "_id": "620b8180e2772e1b88b499k6",
        "answer": "it's going..",
        "questionId": "620b8180e2772e1b88b499e8",
        "userId": "620b8180e2772e1b88b499d48",
        "profileID" : {
          "_id": "620b8180e2772e1b88b499d4",
          "email": "abc@xyz.com"
        }
      }
    ]
  }
]

The actual question:

It works perfectly but I feel like I've overdone stuff and in the Chrome debug section I see this query takes about 2.5 sec on average. enter image description here right now the data is about 200 doc's including questions and answers I am new to MongoDB this is the best I could come up with so I want to know how I can improve this

mongoose aggregation-framework node.js mongodb
1 Answers

Few alterations are done to the pipeline

  • Moved $sort as the 1st stage
  • Since you need question field in the output, included that in the $group stage. And, in the process can strike-off the 4 stages ($lookup back to question collection, $unwind, $addFields to add back the answersArray, and $replaceRoot)

Altered Pipeline

var pipeline = [
    {
        $sort: { _id: -1 }
    },
    {
        $lookup: {
            from: "answers",
            localField: "_id",
            foreignField: "questionID",
            as: "answersArray",
        }
    },
    {
        $unwind: {
            path: "$answersArray",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $lookup: {
            from: "sign-ups",
            localField: "answersArray.profileID",
            foreignField: "_id",
            as: "answersArray.profileID",
        }
    },
    {
        $unwind: {
            path: "$answersArray.profileID",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $group: {
            _id: "$_id",
            question: { $first: '$question' },
            answersArray: {
                $push: "$answersArray",
            }
        }
    }
]
user_15712290
Related