Mongo aggregation count the occurrence of keys using multiple group by

aggregation-framework, mongodb

Solution

The aggregation query for this, is as simple as:

db.test.aggregate( [
    { $group: {
        '_id' : { user_id: '$user_id', product_id: '$product_id' },
        view_count: { $sum: {
            $cond: [ { $eq: [ '$event_name', 'view' ] }, 1, 0 ]
        } },
        like_count: { $sum: {
            $cond: [ { $eq: [ '$event_name', 'like' ] }, 1, 0 ]
        } },
        buy_count: { $sum: {
            $cond: [ { $eq: [ '$event_name', 'buy' ] }, 1, 0 ]
        } },
    } },
    { $project: {
        _id: 0,
        user_id: '$_id.user_id',
        product_id: '$_id.product_id',
        view_count: 1,
        like_count: 1,
        buy_count: 1
    } }
] );

There is however no method to have an arbitrary number of keys (actions) to group on—your aggregation query needs to specify them all.

Problem

Suppose my mongo schema looks like this: ``` db.events = [ { "_id" : ObjectId("528cb8f06e95520dd7000004"), "user_id" : "1", "event_name" : "view", "product_id" : 20 }, { "_id" : ObjectId("528cb8f06e95520dd7000004"), "user_id" : "1", "event_name" : "like", "product_id" : 20 }, { "_id" : ObjectId("528cb8f06e95520dd7000004"), "user_id" : "2", "event_name" : "view", "product_id" : 20 }, { "_id" : ObjectId("528cb8f06e95520dd7000004"), "user_id" : "1", "event_name" : "buy", "product_id" : 21 } ] ``` I want to output the count for each event, grouped by user and product, e.g: ``` [ { "user_id" : 1, "product_id" : 20, "view_count" : 1, "buy_count" : 0, "like_count" : 1, }, { "user_id" : 1, "product_id" : 21, "view_count" : 0, "buy_count" : 1, "like_count" : 0, }, { "user_id" : 2, "product_id" : 20, "view_count" : 1, "buy_count" : 0, "like_count" : 0, } ] ``` I'm stuck in the last step, i.e. "merge" the counts for each event in one row. My solution so far: ``` db.events.aggregate([ { $group: { // Group users and products, collects event names _id: { user_id: '$user_id', product_id: '$product_id' }, events: { $addToSet: "$event_name" } }}, { $unwind : "$events" }, // unwind the events as document stream so they can be counted { $group : { // group and count by event name _id : { user: "$_id.user_id", product: "$_id.product_id", action: "$events" }, count: { $sum: 1 } }}, { $project: { // rename some attributes... _id: 0, user_id: '$_id.user', product_id: '$_id.product', event_name: '$_id.action', event_count: '$count' }}, { $group : { // finally group everything in one row _id : { user: "$user_id", product: "$product_id"}, "$event_name" : { $sum: "$actions_with" } // ERROR group aggregate field name '$event_name' cannot be an operator name" }} ] ); ```

Original source