'How can I generate report from collection on daily, weekly and monthly basis MongoDB?

This is the structure of my collection

  {"_id":{
       "$oid":"61a5f45e7556f5670e50bd25"
    },
    "agent_id":"05046630001",
    "c_id":null,
    "agentName":"Testing",
    "agent_intercom_id":"4554",
    "campaign":[
       "Campaig227"
    ],
    "first_login_time":"28-12-2021  10":"55":42 AM,
    "last_logout_time":"21-01-2022  2":"20":10 PM,
    "parent_id":4663,
    "total_call":2,
    "outbound_call":1,
    "iinbound_call":1,
    "average_call_handling_time":56,
    "logged_in_duration":2,
    "total_in_call_time":30,
    "total_break_duration":10,
    "total_ring_time":2,
    "available_time":40,
    "ideal_time":0,
    "occupancy":0,
    "inbound_calls_missed":0,
    "created_at":{
       "$date":"2021-11-29T18:30:00.000Z"
    }
    }

I want to generate monthly result like this:

Agent Campaign Total call Outgoing Incoming Average Call Total Time Idle Time
Agent 1 Campaig227 148 38 62 12:00:18 12:46:45 0:23:57
Agent 2 Campaig227 120 58 62 16:00:18 16:46:45 0:23:57

and daily report like:

Agent Date Campaign Total call Outgoing Incoming Average Call Total Time Idle Time
Agent 1 1/1/22 Campaig2 14 10 4 4:00:18 4:46:45 0:46:26
Agent 1 2/1/22 Campaig2 24 15 9 10:00:18 9:46:45 0:15:26
Agent 2 1/1/22 Campaig1 16 10 6 4:00:18 4:46:45 0:46:26
Agent 2 2/1/22 Campaig1 30 15 15 10:00:18 9:46:45 0:15:26

Please note that this is only sample data; the actual figure is different.

I tried to do this using aggregate and Pipeline but as I am new to MongoDB so find difficulty in generating query.

mongodbmongodb-queryaggregation-frameworkpipeline


Solution 1:[1]

On proposal would be this one:

db.collection.aggregate([
   {
      $group: {
         _id: {
            agent_id: "$agent_id",
            campaign: "$campaign",
            date: {
               $dateTrunc: {
                  date: "$created_at",
                  unit: "week",
                  timezone: "Europe/Zurich",
                  startOfWeek: "monday"
               }
            }
         },
         "Total call": { $sum: "$total_in_call_time" },
         Outgoing: { $sum: "$outbound_call" },
         Incoming: { $sum: "$iinbound_call" },
         "Average Call": { $avg: "$total_in_call_time" },
         "Total Time": { $sum: "$total_call" },
         "Idle Time": { $sum: "$ideal_time" }
      }
   },
   {
      $set: {
         "Average Call": { $dateToString: { date: { $toDate: { $multiply: ["$Average Call", 1000] } }, format: "%H:%M:%S" } },
         "Total Time": { $dateToString: { date: { $toDate: { $multiply: ["$Total Time", 1000] } }, format: "%H:%M:%S" } },
         "Idle Time": { $dateToString: { date: { $toDate: { $multiply: ["$Idle Time", 1000] } }, format: "%H:%M:%S" } }
      }
   },
   { $replaceWith: { $mergeObjects: ["$_id", "$$ROOT"] } },
   { $unset: "_id" }
])

Note, $dateToString: {format: "%H:%M:%S"} works for periods up to 24 hours.

Mongo Playground

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Wernfried Domscheit

Related Questions