MongoDB: parseFloat for string field in aggregate function ($avg)

Question

I am storing some data in Mongo that contains an arbitrary field. This field is stored as a string, but could contain binary data, an integer, float, or whatever else (up to application and another field, "Type"). Anyway, assuming I know for sure that the documents I am querying store floating values in this field typed as a string, I want to know the average of those values over particular date range. Unfortunately, according to $avg documentation, it ignores any values that are non-numeric. Is there a way to force parseFloat or something similar to convert on the fly the document value to appease the $avg operator?

Sample document structure:

{
    "_id": ObjectId("540f4e29f287300b9097663d"),
    "DateReceived": ISODate("2014-09-09T18:59:53.717Z"),
    "Message": {
        "Version": 1,
        "Source": {
            "Device": "MS02166",
            "Application": "Performance Counter",
            "Category": "Disk - Reads/sec",
            "OriginDate": ISODate("2014-09-09T18:59:51.256Z"),
            "Level": 2
        },
        "PayloadText": "0"
    }
}

The field I am averaging on is Message.PayloadText (in this example its 0, but there are non-zero documents)

The aggregation pipeline query I was able to generate is:

[{
    "$match": {
        "Message.Source.Application": "Performance Counter"
    }
}, {
    "$match": {
        "Message.Source.Category": "Processor"
    }
}, {
    "$match": {
        "Message.Source.Level": 2
    }
}, {
    "$match": {
        "Message.Source.Device": "MS02166"
    }
}, {
    "$match": {
        "DateReceived": {
            "$gte": ISODate("2014-09-14T07:00:00Z"),
            "$lt": ISODate("2014-09-15T07:00:00Z")
        }
    }
}, {
    "$group": {
        "_id": {
            "hour": {
                "$hour": "$DateReceived"
            }
        },
        "AvgUsage": {
            "$avg": "$Message.PayloadText"
        },
        "MinUsage": {
            "$min": "$Message.PayloadText"
        },
        "MaxUsage": {
            "$max": "$Message.PayloadText"
        }
    }
}]

I tried doing something like "$avg": "parseFloat($Message.PayloadText)" and "$avg": "$parseFloat($Message.PayloadText)" to no avail. Strangely, the $min and $max operators DO produce a meaningful result (sample results below).

{ "_id" : { "hour" : 7 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 8 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 9 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 10 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.858578" }
{ "_id" : { "hour" : 11 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.999424" }
{ "_id" : { "hour" : 12 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.999424" }
{ "_id" : { "hour" : 21 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.999424" }
{ "_id" : { "hour" : 16 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 15 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 23 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 18 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "94.02982" }
{ "_id" : { "hour" : 0 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 20 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 22 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "98.48484" }
{ "_id" : { "hour" : 13 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 17 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "98.76543" }
{ "_id" : { "hour" : 14 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "97.18308" }
{ "_id" : { "hour" : 19 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 1 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }
{ "_id" : { "hour" : 4 }, "AvgUsage" : 0, "MinUsage" : "0", "MaxUsage" : "9.37442" }

Answer 1

You need to store a number if you want an average. There aren't any type-conversion functions available that could let you map from a string representation of a number to a number. $min and $max work because there is an order defined on each BSON type (including strings) and between different BSON types, so MongoDB can always find the maximum value of any field.