how to group array of objects, and total up hours using timestamp difference-CodePudding

I have the documents in following structure saved in mongodb.

{ 
    "_id" : ObjectId("62debcb8666e8a64c2ae5e18"), 
    "attendanceDate" : "07/25/2022", 
    "displayName" : "John, Doe", 
    "signInDate" : ISODate("2022-07-25T15:54:32.117 0000"), 
    "currentStatus" : "Training", 
    "currentStatusTime" : ISODate("2022-07-25T18:45:23.574 0000"), 
    "history" : [
        {
            "status" : "Training", 
            "startTime" : ISODate("2022-07-25T18:45:23.573 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-25T17:56:08.236 0000"), 
            "endTime" : ISODate("2022-07-25T18:45:23.574 0000")
        }, 
        {
            "status" : "Training", 
            "startTime" : ISODate("2022-07-25T16:25:25.133 0000"), 
            "endTime" : ISODate("2022-07-25T17:56:08.238 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-25T16:05:57.791 0000"), 
            "endTime" : ISODate("2022-07-25T16:25:25.134 0000")
        }, 
        {
            "status" : "Meeting", 
            "startTime" : ISODate("2022-07-25T16:02:43.956 0000"), 
            "endTime" : ISODate("2022-07-25T16:05:57.796 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-25T16:00:07.308 0000"), 
            "endTime" : ISODate("2022-07-25T16:02:43.956 0000")
        }, 
        {
            "status" : "Break", 
            "startTime" : ISODate("2022-07-25T15:58:12.798 0000"), 
            "endTime" : ISODate("2022-07-25T16:00:07.309 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-25T15:55:00.489 0000"), 
            "endTime" : ISODate("2022-07-25T15:58:12.798 0000")
        }
    ]
}

I would like to group by history status, then for each status, total how many hours user spent by subtracting the timestamp difference between startTime and endTime. How can I write a query that would show query result like this:

user attendance report

I tried to unwind history array to group by status, but not sure how to proceed from here.

db.collectionname.aggregate(
    [
        { 
            "$match" : { 
                "attendanceDate" : "07/25/2022"
            }
        }, 
        { 
            "$unwind" : "$history"
        }, 
        { 
            "$group" : { 
                "_id" : "$_id", 
                "history" : { 
                    "$push" : "$history"
                }
            }
        }
    ]
);

mongodb ver 4.2

CodePudding user response：

One option is:

$match and $unwind as you did.
calculate the duration per each item using $dateDiff
$group by both status and displayName, and use $sum to sum the duration per status.
$group by the displayName to collect all the activities of this person to one document.

db.collection.aggregate([
  {$match: {attendanceDate: "07/25/2022"}},
  {$unwind: "$history"},
  {$set: {timeDiff: {
       $dateDiff: {
          startDate: "$history.startTime",
          endDate: "$history.endTime",
          unit: "minute"
        }
      }
    }
  },
  {$group: {
      _id: {status: "$history.status",  displayName: "$displayName"},
      duration: {$sum: "$timeDiff"}}
  },
  {$group: {
      _id: "$_id.displayName",
      durations: {$push: {key: "$_id.status", value: "$duration"}}}
  }
])

See how it works on the playground example