CODEWITHSUNDEEP
mongodbmongodb-query
bhanu
bhanu

Reputation: 258

Mongo aggregate collection and project fields

I Have 2 collections.

collection 1 model:

{
    "_id" : "abcdefgh",
    "questionType" : "multiselect",
    "question" : "how do you feel",
    "options" : [
        {
            "option" : "Good ",
            "additionalTextRequired" : false
        },
        {
            "option" : "Bad",
            "additionalTextRequired" : false
        }
    ],
    "active" : false,
}

collection 2 model:

{
    "_id" : "bhanu",
    "someunrelatedfield":"dasf",
    "responses" : [
        {
            "questionId" : "abcdefgh",
            "response" : [
                "Good"
            ],
            "valid" : true,
            "unrelatedfield":"dontprojectthese",
        },
        {
            "questionId" : "someotherid",
            "response" : [
                "cool"
            ],
            "valid" : true,
        }
    ],
}

I want to get the following result after query,

{
    "_id":"bhanu",
    "responses":[
        {
             "question": "how do you feel",
             "response": [
                    "good"
               ]
              "valid":true,
       }
    ]
}

Basically i want to replace "questionId" with "question" in collection 2 and project specified fields. How can i write query for it?

Upvotes: 1

Views: 82

Answers (1)

Valijon
Valijon

Reputation: 13103

You need to perform MongoDB aggregation with $lookup operator like this:

db.collection2.aggregate([
  {
    $lookup: {
      from: "collection1",
      localField: "responses.questionId",
      foreignField: "_id",
      as: "tmp"
    }
  },
  {
    $addFields: {
      responses: {
        $map: {
          input: "$responses",
          as: "response",
          in: {
            $mergeObjects: [
              "$$response",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$tmp",
                      cond: {
                        $eq: [
                          "$$response.questionId",
                          "$$this._id"
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "responses.questionId"
      //put here all fields to be removed
    ]
  }
])

MongoPlayground

Upvotes: 1

Related Questions