0

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

San G
  • 27
  • 3

1 Answers1

0

One option is:

  1. $match and $unwind as you did.
  2. calculate the duration per each item using $dateDiff
  3. $group by both status and displayName, and use $sum to sum the duration per status.
  4. $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

nimrod serok
  • 14,151
  • 2
  • 11
  • 33
  • I'm using mongodb ver 4.2, which I don't think has a $dateDiff aggregation operator, I think it's included from ver 5 only. Any alternate suggestions than using $dateDiff to get the same result? – San G Jul 26 '22 at 14:23
  • Sure. Try [this](https://stackoverflow.com/questions/56101773/how-to-find-the-hours-difference-between-two-dates-in-mongodb) – nimrod serok Jul 26 '22 at 14:35