I'm trying to write an aggregation in Mongo which would result as shown below: Please Suggest me how to build Mongo Aggregation in order to achieve my output.

Collection (input): here module_details.module_child_id is related to module_child.module_child_id, I would like to group module_child.name with module_details.value as Key value pair in my output.

[
  {
    "_id": "12345679",
    "module_id": "9898",
    "module": {
      "module_details": {
        "data": [
          {
            "module_id": "9898",
            "module_child_id": "2106123",
            "value": "Username",
            
          },
          {
            "module_id": "9898",
            "module_child_id": "2109909",
            "value": "DOB",
            
          },
          {
            "module_id": "9898",
            "module_child_id": "2110899",
            "value": "Surname",
            
          }
        ]
      },
      "module_child": {
        "data": [
          {
            "name": "Field Element 1",
            "module_child_id": "2106123"
          },
          {
            "name": "Field Element 2",
            "module_child_id": "2109909"
          },
          {
            "name": "Field Element 3",
            "module_child_id": "2110899"
          }
        ]
      }
    }
  }
]

Intended Result:

[
  {
    "module_id:": "9898",
    "Field Element 1": "Username",
    "Field Element 2": "DOB",
    "Field Element 3": "Surname"
  }
  
]

CodePudding user response：

db.collection.aggregate([
  {
    "$unwind": "$module.module_details.data" //Reshape
  },
  {
    "$unwind": "$module.module_child.data" //Reshape
  },
  {
    $match: {
      $expr: {
        "$eq": [ //Get the matched data
          "$module.module_child.data.module_child_id",
          "$module.module_details.data.module_child_id"
        ]
      }
    }
  },
  {
    $group: {
      "_id": "$_id",
      data: { //group again
        $push: {
          "k": "$module.module_child.data.name",
          "v": "$module.module_details.data.value"
        }
      }
    }
  },
  {
    "$project": { //Reshape the data
      _id: 1,
      child: {
        "$arrayToObject": "$data"
      }
    }
  }
])

Playground

You can add another $project to get the desired shape.