CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkaggregate
norganorga
norganorga

Reputation: 63

MongoDB lookup when foreign field and local field are array of objects

I have two collections 'questions' and 'answers'. questions document is like this: 

      [
        {
          _id: "8326ccbn73487290nc",
          questions: [
           {id: "12345", question: "Test", correct_answer: '0'},
           {id: "123456", question: "Test 2", correct_answer: '1'},
           {id: "1234567", question: "Test 3", correct_answer: '1'},
        ]
       },
       {
          _id: "8326ccbn734872ytruy90nclk",
          questions: [
           {id: "345", question: "Test", correct_answer: '0'},
           {id: "3456", question: "Test 2", correct_answer: '1'},
           {id: "34567", question: "Test 3", correct_answer: '1'},
        ]
       }
     ]

answers document is like this:

{
  id: '327rhrne7fr873',
  user_id: '43757fn574057fnf',
  question_id: '8326ccbn73487290nc',
  answers: [
    { id: '12345', student_answer: '1'},
    { id: '123456', student_answer: '0'},
  ]
}

so i want to return something like this:

[
    {
        _id: '8326ccbn73487290nc',
        questions: [
          {id: "12345", question: "Test", correct_answer: '0', student_answers: '1'},
          {id: "123456", question: "Test 2", correct_answer: '1', , student_answers: '0'},
          {id: "1234567", question: "Test 3", correct_answer: '1'},
        ]
    },
    {
          _id: "8326ccbn734872ytruy90nclk",
          questions: [
           {id: "345", question: "Test", correct_answer: '0'},
           {id: "3456", question: "Test 2", correct_answer: '1'},
           {id: "34567", question: "Test 3", correct_answer: '1'},
        ]
       }
]

Can anyone help me how can i do something like that.

Upvotes: 1

Views: 1462

Answers (2)

thammada.ts
thammada.ts

Reputation: 5245

Although it's possible to get your desired structure in MongoDB. If you do not need extra aggregation steps, I would suggest to query only the data you need, and do the data mapping in your application layer, because it's much easier and we don't want to put too much work on the database.

However, you can use the following approach to get (close to) your expected output, I have kept the field student_answer, from answers collection and did not rename it to student_answers, you can do the extra steps if needed.

db.questions.aggregate([
  {
    $lookup: {
      from: "answers",
      let: {
        question_id: "$_id"
      },
      as: "answer",
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$question_id",
                    "$$question_id"
                  ]
                },
                {
                  $eq: [
                    "$user_id",
                    userId // insert user id variable here
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    $unwind: {
      path: "$answer",
      preserveNullAndEmptyArrays: true
    }
  }, // up until this step, you already got all the data you need. You can stop here and do the extra steps in your application layer
  {
    $set: {
      questions: {
        $map: {
          input: "$questions",
          in: {
            $mergeObjects: [
              "$$this",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$answer.answers",
                      as: "answer",
                      cond: {
                        $eq: [
                          "$$this.id",
                          "$$answer.id"
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: "answer" // cleanup by removing temporary field answer
  }
])

Mongo Playground

Upvotes: 1

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

You can use this pipeline under the assumption that an answer object is matched to a single question object.

db.answers.aggregate([
    {
        $lookup: {
            from: "questions",
            let: {answerIds: {$map: {input: "$answers", as: "answer", in: "$$answer.id"}}},
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $gt: [
                                {
                                    $size: {
                                        $filter: {
                                            input: "$questions",
                                            as: "question",
                                            cond: {
                                                $setIsSubset: [["$$question.id"], "$$answerIds"]
                                            }
                                        }
                                    }
                                },
                                0
                            ]
                        }
                    }
                }
            ],
            as: "questions"
        }
    },
    {
        $unwind: "$questions"
    },
    {
        $project: {
            _id: "$questions._id",
            questions: {
                $map: {
                    input: "$questions.questions",
                    as: "question",
                    in: {
                        $mergeObjects: [
                            "$$question",
                            {
                                $ifNull: [
                                    {
                                        $arrayElemAt: [
                                            {
                                                $filter: {
                                                    input: "$answers",
                                                    as: "answer",
                                                    cond: {$eq: ["$$answer.id", "$$question.id"]}
                                                }
                                            }
                                            , 0
                                        ]
                                    },
                                    {}
                                ]
                            }
                        ]
                    }
                }
            }
        }
    }
])

Upvotes: 0

Related Questions