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" } }
}
}
]);
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..