I need more help getting aggregated data from mongodb

Question

I have a table with documents that look like this:

{
    "_id" : ObjectId("bbbbbb1d9486c90479aaaaaa"),
    "record" : {
        "debug" : false
        "type" : "MX_GTI",
        "products" : [ 
            "DAM"
        ],
        "agents" : [ 
            {
                "services" : "mssql",
                "hpsAvg" : 772,
                "hpsMax" : 42901
            }, 
            {
                "services" : "mssql",
                "hpsAvg" : 95,
                "hpsMax" : 21631
            }, 
            {
                "services" : "oracle",
                "hpsAvg" : 0,
                "hpsMax" : 0
            }, 
            {
                "services" : "db2",
                "hpsAvg" : 0,
                "hpsMax" : 0
            }
        ]
    }
}

I need to find the average and max HPS per DB type (the field services) across all the agents in all records that match the condition ("type": "MX_GTI"), The max is the largest hpsMax across all agents with the database type, and the average is the average of all the non-zero values of hpsAvg. The output should look like this:

[
    {
        "dbtype": "oracle",
        "maxHPS": 123456,
        "avgHPS": 12345
    },…
]

Thank you

Answer 1

The difficult part is to make average to work if the value is 0. $avg aggregation ignores non numeric values, so we need to replace 0 values with null before applying average. We can use $cond to make this transformation.

Playground

db.collection.aggregate([
  {
    $match: {
      "record.type": "MX_GTI"
    }
  },
  {
    $unwind: "$record.agents"
  },
  {
    $addFields: {
      "record.agents.hpsAvg": {
        $cond: {
          if: {
            $eq: [
              "$record.agents.hpsAvg",
              0
            ]
          },
          then: null,
          else: "$record.agents.hpsAvg"
        }
      }
    }
  },
  {
    $group: {
      _id: "$record.agents.services",
      maxHPS: {
        $max: "$record.agents.hpsMax"
      },
      avgHPS: {
        $avg: "$record.agents.hpsAvg"
      }
    }
  },
  {
    $addFields: {
      dbType: "$_id"
    }
  },
  {
    $project: {
      _id: 0
    }
  }
])

Answer 2

You can start from here

db.collection.aggregate([
  {
    $match: {
      "record.type": "MX_GTI"
    }
  },
  {
    $unwind: "$record.agents"
  },
  {
    $group: {
      _id: "$record.agents.services",
      "maxHPS": {
        $max: "$record.agents.hpsMax"
      },
      "avgHPS": {
        $avg: "$record.agents.hpsMax"
      }
    }
  }
])

Sample Playground