英文:
mongodb aggregation query in golang
问题
我能够运行以下的MongoDB聚合查询,并在MongoDB中获得所需的结果。我想在Golang中运行相同的聚合查询时获得相同的结果。然而,除了查询中的两个字段(即displayName和msId)显示为空字符串值之外,我得到了所有字段值的预期结果(即durations和totalTime)。它应该包含非空字符串值,就像在MongoDB查询结果中一样。我在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",
"msId" : "$msId"
},
"duration" : {
"$sum" : "$timeDiff"
}
}
},
{
"$group" : {
"_id" : {
"displayName" : "$_id.displayName",
"msId" : "$_id.msId"
},
"durations" : {
"$push" : {
"key" : "$_id.status",
"value" : "$duration"
}
}
}
},
{
"$addFields" : {
"displayName" : "$_id.displayName",
"msId" : "$_id.msId",
"totalTime" : {
"$sum" : "$durations.value"
}
}
}
]
);
MongoDB查询结果:
{
"_id" : {
"displayName" : "John, Doe",
"msId" : "abcd"
},
"durations" : [
{
"key" : "Other",
"value" : NumberInt(0)
},
{
"key" : "Break",
"value" : 1.9216166666666668
},
{
"key" : "Lunch",
"value" : 0.9956666666666667
},
{
"key" : "In",
"value" : 9.3131
},
{
"key" : "Training",
"value" : 0.9886666666666667
}
],
"displayName" : "John, Doe",
"msId" : "abcd",
"totalTime" : 13.219050000000001
}
Golang代码:
type AttendanceAggregate struct {
DisplayName string `json:"displayName" bson:"displayName"`
ID string `json:"msId" bson:"msId"`
TotalTime float64 `json:"totalTime" bson:"totalTime"`
Duration []AttendanceAggregateItem `json:"durations" bson:"durations"`
}
type AttendanceAggregateItem struct {
Key string `json:"key,omitempty" bson:"key,omitempty"`
Value float64 `json:"value,omitempty" bson:"value,omitempty"`
}
func (r *repo) Find() ([]domain.AttendanceAggregate, error) {
ctx, cancel := context.WithCancel(context.Background())
defer cancel()
var attendances []domain.AttendanceAggregate
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",
"msId": "$msId",
},
"duration": bson.M{
"$sum": "$timeDiff",
},
},
},
{
"$group": bson.M{
"_id": bson.M{
"displayName": "$displayName",
"msId": "$msId",
},
"durations": bson.M{
"$push": bson.M{
"key": "$_id.status",
"value": "$duration",
},
},
},
},
{
"$addFields": bson.M{
"displayName": "$_id.displayName",
"msId": "$_id.msId",
"totalTime": bson.M{
"$sum": "$durations.value",
},
},
},
}
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.AttendanceAggregate
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
}
Golang查询结果截图:
英文:
I'm able to run the following mongoDB aggregation query and get the desired result in Mongo DB. I want to get the same result when I run the aggregation query in Golang. However, I'm getting all field values as expected (i.e. durations, totalTime) except for the two fields in the query (i.e. displayName, msId) which shows empty string values. It should contain non-empty string values like in Mongo DB query result. What am I missing here in the golang query?
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",
"msId" : "$msId"
},
"duration" : {
"$sum" : "$timeDiff"
}
}
},
{
"$group" : {
"_id" : {
"displayName" : "$_id.displayName",
"msId" : "$_id.msId"
},
"durations" : {
"$push" : {
"key" : "$_id.status",
"value" : "$duration"
}
}
}
},
{
"$addFields" : {
"displayName" : "$_id.displayName",
"msId" : "$_id.msId",
"totalTime" : {
"$sum" : "$durations.value"
}
}
}
]
);
Result from query above in Mongo -
{
"_id" : {
"displayName" : "John, Doe",
"msId" : "abcd"
},
"durations" : [
{
"key" : "Other",
"value" : NumberInt(0)
},
{
"key" : "Break",
"value" : 1.9216166666666668
},
{
"key" : "Lunch",
"value" : 0.9956666666666667
},
{
"key" : "In",
"value" : 9.3131
},
{
"key" : "Training",
"value" : 0.9886666666666667
}
],
"displayName" : "John, Doe",
"msId" : "abcd",
"totalTime" : 13.219050000000001
}
Golang code -
type AttendanceAggregate struct {
DisplayName string `json:"displayName" bson:"displayName"`
ID string `json:"msId" bson:"msId"`
TotalTime float64 `json:"totalTime" bson:"totalTime"`
Duration []AttendanceAggregateItem `json:"durations" bson:"durations"`
}
type AttendanceAggregateItem struct {
Key string `json:"key,omitempty" bson:"key,omitempty"`
Value float64 `json:"value,omitempty" bson:"value,omitempty"`
}
func (r *repo) Find() ([]domain.AttendanceAggregate, error) {
ctx, cancel := context.WithCancel(context.Background())
defer cancel()
var attendances []domain.AttendanceAggregate
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",
"msId": "$msId",
},
"duration": bson.M{
"$sum": "$timeDiff",
},
},
},
{
"$group": bson.M{
"_id": bson.M{
"displayName": "$displayName",
"msId": "$msId",
},
"durations": bson.M{
"$push": bson.M{
"key": "$_id.status",
"value": "$duration",
},
},
},
},
{
"$addFields": bson.M{
"displayName": "$_id.displayName",
"msId": "$_id.msId",
"totalTime": bson.M{
"$sum": "$durations.value",
},
},
},
}
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.AttendanceAggregate
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
}
golang query result screenshot -
golang query result screenshot
答案1
得分: 0
在Go语言中的最后一个$group
阶段中,displayName
和msId
字段缺少_id
前缀:
原来的写法是:
{
"$group": bson.M{
"_id": bson.M{
"displayName": "$displayName",
"msId": "$msId",
},
"durations": bson.M{
"$push": bson.M{
"key": "$_id.status",
"value": "$duration",
},
},
},
},
应该改为:
{
"$group": bson.M{
"_id": bson.M{
"displayName": "$_id.displayName",
"msId": "$_id.msId",
},
"durations": bson.M{
"$push": bson.M{
"key": "$_id.status",
"value": "$duration",
},
},
},
},
英文:
The last $group
stage in Go misses the _id
prefix from the displayName
and msId
fields:
Instead of:
{
"$group": bson.M{
"_id": bson.M{
"displayName": "$displayName",
"msId": "$msId",
},
"durations": bson.M{
"$push": bson.M{
"key": "$_id.status",
"value": "$duration",
},
},
},
},
Use:
{
"$group": bson.M{
"_id": bson.M{
"displayName": "$_id.displayName",
"msId": "$_id.msId",
},
"durations": bson.M{
"$push": bson.M{
"key": "$_id.status",
"value": "$duration",
},
},
},
},
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论