i need some help grouping by date and by other column, at the moment i got:

[
    {
        '$project': {
            'date': 1, 
            'source': 1, 
            'callDirection': 1, 
            'status': 1
        }
    }, {
        '$match': {
            '$or': [
                {
                    'source': '501'
                }, {
                    'source': '555'
                }
            ]
        }
    }, {
        '$group': {
            '_id': 0, 
            'total': {
                '$sum': 1
            }, 
            'answered': {
                '$sum': {
                    '$cond': [
                        {
                            '$eq': [
                                '$status', 'ANSWERED'
                            ]
                        }, 1, 0
                    ]
                }
            }, 
            'no answer': {
                '$sum': {
                    '$cond': [
                        {
                            '$eq': [
                                '$status', 'NO ANSWER'
                            ]
                        }, 1, 0
                    ]
                }
            }
        }
    }
]

the result i got now is the totals:

_id:0
total:591
answered:443
no answer:129

what i need is to split the data by source and by date so i get the data return like this

date => 2022-01-23 , source => 501, answered => 12, noanswer => 2
date => 2022-01-23 , source => 555, answered => 5, noanswer => 5
date => 2022-01-24 , source => 501, answered => 6, noanswer => 3 
date => 2022-01-24 , source => 555, answered => 22, noanswer => 6 

example data:

 "date": "2021-12-23 10:25:59","source": "501","callDirection": "Outgoing","status": "ANSWERED"
  "date": "2021-12-23 11:21:19","source": "501","callDirection": "Outgoing","status": "NO ANSWER"
  "date": "2021-12-24 01:21:19","source": "501","callDirection": "Outgoing","status": "ANSWERED"
  "date": "2021-12-24 10:25:59","source": "555","callDirection": "Outgoing","status": "ANSWERED"
  "date": "2021-12-25 12:55:19","source": "555","callDirection": "Outgoing","status": "ANSWERED"

im new to mongoDb and i need some help ,thanks a lot

CodePudding user response：

Perhaps Something like this:

 db.collection.aggregate([
 {
$addFields: {
  date: {
    $substr: [
      "$date",
      0,
      10
    ]
  }
}
},
{
$group: {
  _id: {
    da: "$date",
    so: "$source",
    cd: "$callDirection"
    },
   answer: {
    "$sum": {
      "$cond": [
        {
          "$eq": [
            "ANSWERED",
            "$status"
          ]
        },
        1,
        0
      ]
     }
    },
    noanswer: {
    "$sum": {
      "$cond": [
        {
          "$eq": [
            "ANSWERED",
            "$status"
          ]
        },
        0,
        1
      ]
     }
    }
   }
 },
 {
  $project: {
  date: "$_id.da",
  source: "$_id.so",
  callDirection: "$_id.cd",
  answer: 1,
  noanswer: 1
  }
 }
 ])

Explained:

1. Replace the datetime string with date only string
2. Group by date,source & callDirection generating two new counting fields answer and noanswer from the status field.
3. Project only the necessary fields are needed

playground