mongodb aggregation pipeline in golang for $divide $subtract

huangapple
go评论69阅读模式
英文:

mongodb aggregation pipeline in golang for $divide $subtract

问题

在golang中，您可以使用以下代码编写管道来执行上述mongodb聚合查询：

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

这是一个修改后的管道查询，可以与您提供的JSON文档一起使用。您可以将此代码添加到您的Find函数中以执行聚合查询。请确保导入了正确的包（如go.mongodb.org/mongo-driver/bson）。

希望这可以帮助到您！如果您有任何其他问题，请随时问我。

英文:

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

mongodb aggregation pipeline in golang for $divide $subtract

modified pipeline query that worked

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

答案1

得分: 0

bson.A 可以作为 MongoDB 管道中的数组使用。

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",
        },
      },
    },
  },
}
英文:

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

huangapple
  • 本文由 发表于 2022年8月9日 06:13:54
  • 转载请务必保留本文链接：https://go.coder-hub.com/73284567.html
  • go
  • mongodb
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定