MongoDB aggregation group by query

Question

I have a mongoDB collection and I want to do an aggregation query.
I am grouping by alert_type field, but I also want the list of those alert_type as a separate field in the output.

Collection looks like this :

db.test.insertMany([
  {
    "output_data": {
      "alert_type": "UAlert",
      "overallImpact": {
        "margin": 0.1,
        "workingCapital": 3.33
      }
    }
  },
  {
    "output_data": {
      "alert_type": "CAlert",
      "overallImpact": {
        "margin": 0.1,
        "workingCapital": 3.33
      }
    }
  },
  {
    "output_data": {
      "alert_type": "UAlert",
      "overallImpact": {
        "margin": 0.1,
        "workingCapital": 3.33
      }
    }
  }
])

Query that I have tried :

db.test.aggregate([
  {$group: {
      "_id": "$output_data.alert_type",
      "alert_type": {
        "$first": "$output_data.alert_type"
      },
      "margin": {
        "$sum": "$output_data.overallImpact.margin"
      },
      "workingCapital": {
        "$sum": "$output_data.overallImpact.workingCapital"
      },
      "alert_types": {
        "$addToSet": "$output_data.alert_type"
      }
    }
  },
  {$project: {'_id': 0
    }
  }
])

Current output :

{
  "alert_type": "UAlert",
  "margin": 0.2,
  "workingCapital": 6.66,
  "alert_types": [
    "UAlert"
  ]
}
{
  "alert_type": "CAlert",
  "margin": 0.1,
  "workingCapital": 3.33,
  "alert_types": [
    "CAlert"
  ]
}

Required Output :

{
  "data": [
    {
      "alert_type": "UAlert",
      "margin": 0.2,
      "workingCapital": 6.66,
    },
    {
      "alert_type": "CAlert",
      "margin": 0.1,
      "workingCapital": 3.33,
    }
  ],
  "alert_types": [
    "UAlert",
    "CAlert"
  ]
}

Can anyone help me out with this?

Answer 1

You can try below aggregation query :

db.collection.aggregate([
    {
      $group: {
        "_id": "$output_data.alert_type",
        alert_type: { $first: "$output_data.alert_type" },
        margin: { $sum: "$output_data.overallImpact.margin" },
        workingCapital: { $sum: "$output_data.overallImpact.workingCapital" }
      }
    },
    /** Optional stage - Just to exclude `_id` inside each object of data array from final output */
    {
      $project: { _id: 0 }
    },
    /** Grouping on all docs, For this group stage we will have lesser docs compared to prior Group stage */
    {
      $group: {
        _id: "", // Group without any condition
        data: {  $push: "$$ROOT" }, // Pushing all docs into an array
        alert_types: { $addToSet: "$alert_type" } // Adding unique values
      }
    },
    /** Optional stage - Just to exclude `_id` final output doc */
    {
      $project: { _id: 0 }
    }
  ])

Test : mongoplayground

Answer 2

You have to use $facet to achieve this, in a stage you do your grouping stage to get data, and in the other you find all the alert types available.

db.collection.aggregate([
  {
    $facet: {
      data: [
        {
          $group: {
            "_id": "$output_data.alert_type",
            "alert_type": {
              "$first": "$output_data.alert_type"
            },
            "margin": {
              "$sum": "$output_data.overallImpact.margin"
            },
            "workingCapital": {
              "$sum": "$output_data.overallImpact.workingCapital"
            },
          }
        },
        {
          $project: {
            "_id": 0
          }
        }
      ],
      "alert_types": [
        {
          $group: {
            _id: null,
            "names": {
              "$addToSet": "$output_data.alert_type"
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      data: 1,
      alert_types: "$alert_types.names"
    }
  }
]) 

You can test it here