Multiple group by in mongodb

Question

I have a documents like that at my mongo database:

{
{"_id" : NumberLong(50),
"offerId" : NumberLong(88),
"isInTryPeriod" : false,
"state" : {
    "currentState" : "INACTIVE",
    "oldState" : "INACTIVE"
},
 ......
 ......
 ......
}

And I want to group them under "offerId" but also i want to see the count isInTryPeriod (for true and false) and currentState (active, inactive etc) number under specific states like as follows:

{{
_id: offerId
states: {
isInTryperiodCountForThatOfferId: X
activeForThatOfferId: X
inactiveForThatOfferId: X
},
{
_id: offerId
states: {
isInTryperiodCountForThatOfferId: X
activeForThatOfferId: X
inactiveForThatOfferId: X
}}

What i tried for that is, I write two query for them they work separately but i want to compose them.

To get count for sate.currentState field

db.subscriptions.aggregate(
  {
    $group: {
      _id:   { offerId: "$offerId", state: "$state.currentState" },
      "state": { "$push": "$state.currentState" },
      "total": { "$sum": 1 }
    }
  },
  {
    $group: {
      _id: { offerId: "$_id.offerId" },
      state: { $addToSet: { state: "$_id.state", sum:"$total" } }
    }
  }
);

To get the count for isInTrpPreiod field

db.subscriptions.aggregate([
  { 
    $match : {isInTryPeriod : true}
  },
  {
    $group: {
      _id:   { offerId: "$offerId", "isInTryPeriod" : "$isInTryPeriod"},
      "isInTryPeriod": { "$push": "$isInTryPeriod" },
      "total": { "$sum": 1 }
    }
  },
  {
    $group: {
      _id: "$_id.offerId",
      isInTryPeriod: { $addToSet: { isInTryPeriod: "$_id.isInTryPeriod", sum:"$total" } }
    }
  }
]);

user_11028863 · Answer

You should be able to group by offerId and use $cond to sum fields based on a condition.

db.getCollection('subscriptions').aggregate([
{
  $group: {
    _id: { offerId : "$offerId" },
    "totalActiveState": { 
      "$sum": {
        "$cond": [
           { "$eq": ["$state.currentState", "ACTIVE"] }, 1, 0
         ]
       }
     },
    "totalInactiveState": { 
      "$sum": {
        "$cond": [
           { "$eq": ["$state.currentState", "INACTIVE"] }, 1, 0
         ]
       }
     },
    "totalIsInTryPeriod": { 
      "$sum": {
        "$cond": [
           { "$eq": ["$isInTryPeriod", true] }, 1, 0
         ]
       }
     }     
    }
  }
])

This will return sth like the following:

[
{
    "_id" : {
        "offerId" : 77.0
    },
    "totalActiveState" : 1.0,
    "totalInactiveState" : 0.0,
    "totalIsInTryPeriod" : 1.0
},
{
    "_id" : {
        "offerId" : 88.0
    },
    "totalActiveState" : 1.0,
    "totalInactiveState" : 2.0,
    "totalIsInTryPeriod" : 2.0
}
]

Not sure about the performance, but it should give you the results..

Multiple group by in mongodb

1 Answers