mongodb - mongodb collection aggregate with group to list secondary group

Question

For mongodb, my attempt is to create a group on event, then create a secondary group by state. I have this basic start working. Need to also consider how to add more listed attributes to the grouped data, such as perhaps a subgroup by state or another attribute, such as month, etc.

In mongodb, is there a way to create sub-groups? What options are available in db.collection.aggregate() for sub-groups or listing more field attributes in the results. Such as listing a sub-group under events by a count of injuries or deaths, etc.

For example, the following sum (or avg) by multiple damage fields, and group then under / inside a higher level group (such as the code below)? I'm not familiar with the mongo aggregation setup for other than basic group aggregations. Looking for solution ideas.

    {
        $group: {
            _id: "$EVENT_TYPE",
            events: {
                $push: {
                    eventName: "$EVENT_TYPE",
                    registeredDate: "$BEGIN_DATE_TIME",
                    stateName: "$STATE",
                    damageCosts: "$DAMAGE_PROPERTY",
                    peopleCosts: "$DEATHS_DIRECT",
                    injuriyCosts: "$INJURIES_DIRECT"
                }
            }
        },
        count: {
            $sum: ("$DAMAGE_PROPERTY" + "$DEATHS_DIRECT" + "$INJURIES_DIRECT")
        }
    }
])

OR instead of "count", use $sum to total multiple attribute fields, e.g,:

    "$sum": "total" {
      ("$DAMAGE_PROPERTY" + "$DEATHS_DIRECT" + "$INJURIES_DIRECT")
    }

db.collection.aggregate([
    {
        $group: {
            _id: "$EVENT_TYPE",
            books: {
                $push: {
                    eventName: "$EVENT_TYPE",
                    registeredDate: "$BEGIN_DATE_TIME",
                    stateName: "$STATE"
                }
            }
        }
    },
    {
        $project:{
            _id: 0,
            eventNo: "$_id",
            events: 1
        }
    }
])

Here is two rows of sample data: I have the this output result working:

[
  {
    "eventNo": "Heat"
  },
  {
    "eventNo": "Heavy Snow"
  }
]

For this data in the aggregate query above.

[
  {
    "BEGIN_YEARMONTH": 201007,
    "BEGIN_DAY": 7,
    "END_YEARMONTH": 201007,
    "END_DAY": 7,
    "END_TIME": 1630,
    "STATE": "NEW HAMPSHIRE",
    "YEAR": 2010,
    "EVENT_TYPE": "Heat",
    "BEGIN_DATE_TIME": "07-JUL-10 12:51:00",
    "END_DATE_TIME": "07-JUL-10 16:30:00",
    "INJURIES_DIRECT": 0,
    "DEATHS_DIRECT": 0,
    "DAMAGE_PROPERTY": "0.00K",
    "DAMAGE_CROPS": "0.00K"
  },
  {
    "BEGIN_YEARMONTH": 201001,
    "BEGIN_DAY": 17,
    "END_YEARMONTH": 201001,
    "END_DAY": 18,
    "END_TIME": 1500,
    "STATE": "NEW HAMPSHIRE",
    "YEAR": 2010,
    "MONTH_NAME": "January",
    "EVENT_TYPE": "Heavy Snow",
    "BEGIN_DATE_TIME": "17-JAN-10 23:00:00",
    "END_DATE_TIME": "18-JAN-10 15:00:00",
    "INJURIES_DIRECT": 0,
    "DEATHS_DIRECT": 0,
    "DAMAGE_PROPERTY": "0.00K",
    "DAMAGE_CROPS": "0.00K"  
  }
]

标签： mongodbmongodb-queryaggregate