在Golang中的MongoDB聚合查询

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

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查询结果截图:

在Golang中的MongoDB聚合查询

英文:

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阶段中,displayNamemsId字段缺少_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",
},
},
},
},

huangapple
  • 本文由 发表于 2022年8月13日 07:18:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/73340529.html
匿名

发表评论

匿名网友

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

确定