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
CodePudding user response:
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",
},
},
},
},