Mongodb aggregate group by array elements-CodePudding

I have a mongodb document that contains customer id, status (active, deactivate) and date.

[
{
 id:1,
 date:ISODate('2022-12-01'),
 status:'activate'
},
{
 id:2,
 date:ISODate('2022-12-01'),
 status:'activate'
},
{
 id:1,
 date:ISODate('2022-12-02'),
 status:'deactivate'
},
{
 id:2,
 date:ISODate('2022-12-21'),
 status:'deactivate'
}
]

I need to get daywise customer status count. I came up with below aggregation.

db.collection.aggregate([
  {
    $addFields: {
      "day": {
        "$dateToString": {
          "format": "%Y-%m-%d",
          "date": "$date"
        }
      }
    }
  },
  {
    $group: {
      _id: "$day",
      type: {
        $push: "$status"
      }
    }
  }
])

this way I can get status in a array. like below.

[
{
 _id:"2022-12-01",
 type:[
 0:"activate",
 1:"activate"
 ]
},
{
 _id:"2022-12-02",
 type:[
 0:"deactivate"
 ]
},
{
 _id:"2022-12-21",
 type:[
 0:"deactivate"
 ]
}
]

now it's working as intended. but I need the output like below.

[
{
 _id:"2022-12-01",
 type:{
 "activate":2,
 }
},
{
 _id:"2022-12-02",
 type:{
 "deactivate":1
 }
},
{
 _id:"2022-12-21",
 type:{
 "deactivate":1
 }
}
]

this table has around 100,000 documents and doing this programmatically will take about 10 seconds. that's why I'm searching a way to do this as a aggregation

CodePudding user response：

One option is to group twice and then use $arrayToObject:

db.collection.aggregate([
  {$group: {
      _id: {day: "$date", status: "$status"},
      count: {$sum: 1}
  }},
  {$group: {
      _id: {$dateToString: {format: "%Y-%m-%d", date: "$_id.day"}},
      data: {$push: {k: "$_id.status", v: "$count"}}
  }},
  {$project: {type: {$arrayToObject: "$data"}}}
])

See how it works on the playground example