MongoDB Aggregation, group by subobject keys

Question

I have a mongo collection whose schema looks like this:

_id: ObjectId(),
segments: {
  activity: 'value1',
  activation: 'value2',
  plan: 'value3'
}

I'm trying to use the aggregation framework to find out how many of my documents have the value1 for the segment activity for instance.

The problem is that I want to do that for every segment in the same request if possible, and that I don't know how many segments I'll have or even their name.

Basically here's what I'd like to do:

If I have these two documents:

{ _id: 1, segments: { activity: 'active', activation: 'inactive', plan: 'free' }
{ _id: 2, segments: { activity: 'inactive', activation: 'inactive', plan: 'free' }

I want to be able to see that two of them have the activation segment to inactive and the free plan, and that activity have 1 inactive and 1 active values. Here is what I want to get:

{ 
  activity: {
    active: 1,
    inactive: 1
  },
  activation: {
    inactive: 2
  },
  plan: {
    free: 2
  }
}

So basically, if you could just $group by key it would be great! Something like this:

{
  $group: {
    _id: { $concat: [ '$segments.$key', '-', '$segments.$key.$value' ],
    count: { $sum: 1 }
  }
}

Or if I could unwind on each key...

Answer 1

To get the counts, take advantage of the $cond operator in the $group pipeline step to evaluate the counts based on the subdocuments value, something like the following:

db.collection.aggregate([    
    { 
        "$group": { 
            "_id": "$_id",             
            "activity_active": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$segment.activity", "active" ] }, 1, 0 ]
                }
            },
            "activity_inactive": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$segment.activity", "inactive" ] }, 1, 0 ]
                }
            },
            "activation_active": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$segment.activation", "active" ] }, 1, 0 ]
                }
            },
            "activation_inactive": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$segment.activity", "inactive" ] }, 1, 0 ]
                }
            },
            "plan_free": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$segment.plan", "free" ] }, 1, 0 ]
                }
            } 
        }  
    },
    {
        "$project": {
            "_id": 0,            
            "activity": {
                "active": "$activity_active",
                "inactive": "$activity_inactive"
            },
            "activation": {
                "active": "$activation_active",
                "inactive": "$activation_inactive"
            },
            "plan": {
                "free": "$plan_free"
            }
        }
    }
])