golang 中的 mongodb 聚合管道用于 $divide $subtract

各位小伙伴们，大家好呀！看看今天我又给各位带来了什么文章？本文标题是《golang 中的 mongodb 聚合管道用于 $divide $subtract》，很明显是关于Golang的文章哈哈哈，其中内容主要会涉及到等等，如果能帮到你，觉得很不错的话，欢迎各位多多点评和分享！

在golang中，如何为以下mongodb聚合查询编写管道？

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"
                    }
                }
            }
        }
    ],

mongodb 版本 4.2 中的 json 文档

{ 
    "_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")
        }
    ]
}

使用mongodb 4.2版本，聚合查询返回结果如下

{ 
    "_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
        }
    ]
}

在 golang 中，让它一直工作到 $unwind 管道。我不确定如何进一步进行 $divide、$subract 并获得与聚合查询结果相同的结果。

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
}

@钱丹

修改后的管道查询有效

pipeline := []bson.M{
        {
            "$match": bson.M{
                "attendanceDate": "07/26/2022",
            },
        },
        {
            "$unwind": "$history",
        },
        {
            "$set": bson.M{
                "timeDiff": bson.M{
                    "$subtract": bson.A{
                        "$history.endTime",
                        "$history.startTime",
                    },
                },
            },
        },
        {
            "$set": bson.M{
                "timeDiff": bson.M{
                    "$divide": bson.A{
                        "$timeDiff",
                        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",
                    },
                },
            },
        },
    }

正确答案

bson.A可以用作mongo管道的数组

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",
        },
      },
    },
  },
}

今天带大家了解了的相关知识，希望对你有所帮助；关于Golang的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~