In golang, how do I write pipeline for the following mongodb aggregation query?

db.getCollection("db").aggregate(
    [
        { 
            "$match" : { 
                "attendanceDate" : "07/26/2022"
            }
        }, 
        { 
            "$unwind" : "$history"
        }, 
        { 
            "$set" : { 
                "timeDiff" : { 
                    "$divide" : [
                        { 
                            "$subtract" : [
                                "$history.endTime", 
                                "$history.startTime"
                            ]
                        }, 
                        60000.0
                    ]
                }
            }
        }, 
        { 
            "$group" : { 
                "_id" : { 
                    "status" : "$history.status", 
                    "displayName" : "$displayName"
                }, 
                "duration" : { 
                    "$sum" : "$timeDiff"
                }
            }
        }, 
        { 
            "$group" : { 
                "_id" : "$_id.displayName", 
                "durations" : { 
                    "$push" : { 
                        "key" : "$_id.status", 
                        "value" : "$duration"
                    }
                }
            }
        }
    ], 

JSON doc in mongodb ver 4.2

{ 
    "_id" : ObjectId("62e01543666e8a64c2aeec56"), 
    "attendanceDate" : "07/26/2022", 
    "displayName" : "John, Doe", 
    "signInDate" : ISODate("2022-07-26T16:24:35.488 0000"), 
    "currentStatus" : "Other", 
    "currentStatusTime" : ISODate("2022-07-26T16:37:54.890 0000"), 
    "history" : [
        {
            "status" : "Other", 
            "startTime" : ISODate("2022-07-26T16:37:54.890 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-26T16:33:00.655 0000"), 
            "endTime" : ISODate("2022-07-26T16:37:54.890 0000")
        }, 
        {
            "status" : "Training", 
            "startTime" : ISODate("2022-07-26T16:32:01.337 0000"), 
            "endTime" : ISODate("2022-07-26T16:33:00.657 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-26T16:31:00.764 0000"), 
            "endTime" : ISODate("2022-07-26T16:32:01.338 0000")
        }, 
        {
            "status" : "Lunch", 
            "startTime" : ISODate("2022-07-26T16:30:01.025 0000"), 
            "endTime" : ISODate("2022-07-26T16:31:00.765 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-26T16:27:33.789 0000"), 
            "endTime" : ISODate("2022-07-26T16:30:01.026 0000")
        }, 
        {
            "status" : "Break", 
            "startTime" : ISODate("2022-07-26T16:25:38.492 0000"), 
            "endTime" : ISODate("2022-07-26T16:27:33.789 0000")
        }, 
        {
            "status" : "In", 
            "startTime" : ISODate("2022-07-26T16:24:41.753 0000"), 
            "endTime" : ISODate("2022-07-26T16:25:38.493 0000")
        }
    ]
}

Using mongodb version 4.2, result from the aggregation query returns result as below

{ 
    "_id" : "John, Doe", 
    "durations" : [
        {
            "key" : "Other", 
            "value" : NumberInt(0)
        }, 
        {
            "key" : "Lunch", 
            "value" : 0.9956666666666667
        }, 
        {
            "key" : "In", 
            "value" : 9.3131
        }, 
        {
            "key" : "Training", 
            "value" : 0.9886666666666667
        }, 
        {
            "key" : "Break", 
            "value" : 1.9216166666666668
        }
    ]
}

In golang, got it working until $unwind pipeline. I'm unsure how to proceed further to $divide, $subract and get the same result as in aggregation query result.

 
type Attendance struct {
    ID                primitive.ObjectID `json:"_id" bson:"_id"`
    Date              time.Time          `json:"signInDate" bson:"signInDate"`
    DisplayName       string             `json:"displayName" bson:"displayName"`
    CurrentStatus     string             `json:"currentStatus,omitempty" bson:"currentStatus,omitempty"`
    CurrentStatusTime time.Time          `json:"currentStatusTime,omitempty" bson:"currentStatusTime,omitempty"`
    History           []AttendanceHistoryItem `json:"history" bson:"history"`
}

type AttendanceHistoryItem struct {
    Status       string    `json:"status,omitempty" bson:"status,omitempty"`
    StartTime    time.Time `json:"startTime,omitempty" bson:"startTime,omitempty"`
    EndTime      time.Time `json:"endTime,omitempty" bson:"endTime,omitempty"`
}

func (r *repo) Find() ([]domain.Attendance, error) {
    ctx, cancel := context.WithCancel(context.Background())
    defer cancel()

    var attendances []domain.Attendance

    pipeline := []bson.M{
        {
            "$match": bson.M{
                "attendanceDate": "07/26/2022",
            },
        },
        {
            "$unwind": "$history",
        },
    }

 
    cur, err := r.Db.Collection("db").Aggregate(ctx, pipeline)
    defer cur.Close(ctx)
    if err != nil {
        return attendances, err
    }

    for cur.Next(ctx) {
        var attendance domain.Attendance

        err := cur.Decode(&attendance)
        if err != nil {
            return attendances, err
        }
    
        attendances = append(attendances, attendance)
    }

    if err = cur.Err(); err != nil {
        return attendances, err
    }

    return attendances, nil
}

@Chandan

[syntax error screenshot for the query you posted][1]


  [1]: https://i.stack.imgur.com/bnjL4.jpg

CodePudding user response：

bson.A can be used as array for mongo pipeline

query := []bson.M{
  {
    "$match" : bson.M{
      "attendanceDate" : "07/26/2022",
    },
  },
  {
    "$unwind" : "$history",
  },
  {
    "$set" : bson.M{
      "timeDiff" : bson.M{
        "$divide" : bson.A{
          {
            "$subtract" : bson.M{
              "$history.endTime",
              "$history.startTime",
            },
          },
          60000.0,
        },
      }
    },
  },
  {
    "$group" : bson.M{
      "_id" : bson.M{
        "status" : "$history.status",
        "displayName" : "$displayName",
      },
      "duration" : bson.M{
        "$sum" : "$timeDiff",
      },
    },
  },
  {
    "$group" : bson.M{
      "_id" : "$_id.displayName",
      "durations" : bson.M{
        "$push" : bson.M{
          "key" : "$_id.status",
          "value" : "$duration",
        },
      },
    },
  },
}