javascript - MongoDB Aggregate Sum Each Key on a Subdocument

Question

I have multiple documents with this schema, each document is per product per day:

{
    _id:{},
    app_id:'DHJFK67JDSJjdasj909',
    date:'2014-08-07',
    event_count:32423,
    event_count_per_type: {
        0:322,
        10:4234,
        20:653,
        30:7562
    }
}

I would like to get the sum of each event_type for a particular date_range.
This is the output I am looking for where each event type has been summed across all the documents. The keys for event_count_per_type can be anything, so I need something that can loop through each of them as opposed to be having to be implicit with their names.

{
    app_id:'DHJFK67JDSJjdasj909',
    event_count:324236456,
    event_count_per_type: {
        0:34234222,
        10:242354,
        20:456476,
        30:56756
    }
}

I have been trying several queries so far, this is the best I have got so far but the sub document values are not summed:

db.events.aggregate(
{
    $match: {app_id:'DHJFK67JDSJjdasj909'}
},
{
    $group: {
        _id: {
            app_id:'$app_id',
        },
        event_count: {$sum:'$event_count'},
        event_count_per_type: {$sum:'$event_count_per_type'}
    }
},
{
    $project: {
        _id:0,
        app_id:'$_id.app_id',
        event_count:1,
        event_count_per_type:1
    }
}
)

The output I am seeing is a value of 0 for the event_count_per_type key, instead of an object. I could modify the schema so the keys are on the top level of the document but that will still mean that I need to have an entry in the group statement for each key, which as I do not know what the key names will be I cannot do.

Any help would be appreciated, I am willing to change my schema if need be and also to try mapReduce (although from the documentation it seems like the performance is bad.)

Answer 1

As stated, processing documents like this is not possible with the aggregation framework unless you are actually going to supply all of the keys, such as:

db.events.aggregate([
   { "$group": {
       "_id": "$app_id",
       "event_count": { "$sum": "$event_count" },
       "0": { "$sum": "$event_count_per_type.0" },
       "10": { "$sum": "$event_count_per_type.10" }
       "20": { "$sum": "$event_count_per_type.20" }
       "30": { "$sum": "$event_count_per_type.30" }
   }}
])

But you do of course have to explicitly specify every key you wish to work on. This is true of both the aggregation framework and general query operations in MongoDB, as to access elements notated in this "sub-document" form you need to specify the "exact path" to the element in order to do anything with it.

The aggregation framework and general queries have no concept of "traversal", which mean they cannot process "each key" of a document. That requires a language construct in order to do which is not provided in these interfaces.

Generally speaking though, using a "key name" as a data point where it's name actually represents a "value" is a bit of an "anti-pattern". A better way to model this would be to use an array and represent your "type" as a value by itself:

{
    "app_id": "DHJFK67JDSJjdasj909",
    "date: ISODate("2014-08-07T00:00:00.000Z"),
    "event_count": 32423,
    "events": [
        { "type": 0,  "value": 322  },
        { "type": 10, "value": 4234 },
        { "type": 20, "value": 653  },
        { "type": 30, "value": 7562 }
    ]
}

Also noting that the "date" is now a proper date object rather than a string, which is also something that is good practice to do. This sort of data though is easy to process with the aggregation framework:

db.events.aggregate([
    { "$unwind": "$events" },
    { "$group": {
        "_id": { 
            "app_id": "$app_id",
            "type": "$events.type"
        },
        "event_count": { "$sum": "$event_count" },
        "value": { "$sum": "$value" }
    }},
    { "$group": {
        "_id": "$_id.app_id",
        "event_count": { "$sum": "$event_count" },
        "events": { "$push": { "type": "$_id.type", "value": "$value" } }
    }}
]) 

That shows a two stage grouping that first gets the totals per "type" without specifying each "key" since you no longer have to, then returns as a single document per "app_id" with the results in an array as they were originally stored. This data form is generally much more flexible for looking at certain "types" or even the "values" within a certain range.

Where you cannot change the structure then your only option is mapReduce. This allows you to "code" the traversal of the keys, but since this requires JavaScript interpretation and execution it is not as fast as the aggregation framework:

db.events.mapReduce(
    function() {
        emit(
            this.app_id,
            {
                "event_count": this.event_count,
                "event_count_per_type": this.event_count_per_type
            }
        );
    },
    function(key,values) {

        var reduced = { "event_count": 0, "event_count_per_type": {} };

        values.forEach(function(value) {
            for ( var k in value.event_count_per_type ) {
                if ( !redcuced.event_count_per_type.hasOwnProperty(k) )
                    reduced.event_count_per_type[k] = 0;
                reduced.event_count_per_type += value.event_count_per_type;
            }
            reduced.event_count += value.event_count;
        })
    },
    {
        "out": { "inline": 1 }
    }
)

That will essentially traverse and combine the "keys" and sum up the values for each one found.

So you options are either:

1. Change the structure and work with standard queries and aggregation.
2. Stay with the structure and require JavaScript processing and mapReduce.

It depends on your actual needs, but in most cases restructuring yields benefits.