I have a collection with documents like this...

I want to group items by event_datum.message and add a column called avgLoadTime where I compute the average of event_datum.params[0].total

So I did this...

[
    { // filter rows by type
        "$match": {
            "event_datum.type": "page_load_time"
        }
    },
    { // add a column called firstParam with the first array element of params[]
        $addFields: {
            firstParam: { $arrayElemAt: ["$event_datum.params",0] }
        }
    },
    { // group rows by message and compute the average using the added field
        $group: {
            _id: "$event_datum.message",
            avgLoadTime: {
                $avg: "$firstParam.total"
            }
        }
    },
    { // output both columns
        $project: {
            firstParam: true,
            avgLoadTime: true
        }
    }
]

I get the list of grouped messages, but the column avgLoadTime is empty. What I'm doing wrong?

Thanks

CodePudding user response：

This is because total is saved as string, so $avg just ignores it. you just need to cast it to number , for example using $toInt:

{
    // group rows by message and compute the average using the added field
    $group: {
        _id: "$event_datum.message",
            avgLoadTime: {
            $avg: {
                "$toInt": "$firstParam.total"
            }
        }
    }
}

Mongo Playground