Home > Net >  mongodb aggregation query in golang
mongodb aggregation query in golang

Time:08-15

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