Why does the following mongodb group query return always 0?

Question

I have the following sample json, it doesn't seem to be too hard. Basically I'm trying to group all objects by the algorithm field, and then calculate the average from the $difference.close field.

The following is a sample object

{
    "_id" : ObjectId("56b0c702c783c42f504f006c"),
    "symbol" : "ALSK",
    "algorithm" : "AvgForecaster",
    "date" : "2016-02-01",
    "real_quote" : {
        "trade_date" : "2016-02-01",
        "open" : "1.6",
        "close" : "1.59",
        "low" : "1.57",
        "high" : "1.66",
        "volume" : "65600",
        "symbol" : "ALSK",
        "adjusted_close" : "1.59"
    },
    "forecasted_quote" : {
        "trade_date" : "2016-02-01",
        "open" : "0.0",
        "close" : "1.92",
        "low" : "0.0",
        "high" : "0.0",
        "volume" : "0",
        "symbol" : "ALSK",
        "adjusted_close" : "0.0"
    },
    "difference" : {
        "trade_date" : "2016-02-01",
        "open" : "1.6",
        "close" : "-0.33",
        "low" : "1.57",
        "high" : "1.66",
        "volume" : "65600",
        "symbol" : "ALSK",
        "adjusted_close" : "1.59"
    }
}

Then the query is as follows:

db.getCollection('Accuchecks').aggregate([
{
    $group: {
        _id: "$algorithm",
        accuracy: {$avg: "$difference.close"}
     }
}])

And then I get the following result:

{
    "result" : [ 
        {
            "_id" : "DeltaForecaster",
            "accuracy" : 0.0000000000000000
        }, 
        {
            "_id" : "AvgForecaster",
            "accuracy" : 0.0000000000000000
        }
    ],
    "ok" : 1.0000000000000000
}

Could the query is not picking the $difference.close field? In the full collection that value almost never 0.

Thanks

No, it's picking the `$difference.close` field fine but because it holds string values, the `$avg` operator will not work, only does so on numerical fields. So change your schema so that the `$difference.close` field holds numerical values. — chridam, Feb 03 '16 at 17:02

score 1 · Accepted Answer · edited May 23 '17 at 11:59

You are getting back a 0, as you are storing your close values as strings instead of floats. There are no type-conversions functions available in the aggregation pipeline, so you will have to store your values as numerical values instead, see this post on how to change the type of your existing data.

{
    "_id" : ObjectId("56b0c702c783c42f504f006c"),
    "symbol" : "ALSK",
    "algorithm" : "AvgForecaster",
    "date" : "2016-02-01",
    "real_quote" : {
        "trade_date" : "2016-02-01",
        "open" : 1.6,
        "close" : 1.59,
        "low" : 1.57,
        "high" : 1.66,
        "volume" : 65600,
        "symbol" : "ALSK",
        "adjusted_close" : 1.59
    },
    "forecasted_quote" : {
        "trade_date" : "2016-02-01",
        "open" : 0.0,
        "close" : 1.92,
        "low" : 0.0,
        "high" : 0.0,
        "volume" : 0,
        "symbol" : "ALSK",
        "adjusted_close" : 0.0
    },
    "difference" : {
        "trade_date" : "2016-02-01",
        "open" : 1.6,
        "close" : -0.33,
        "low" : 1.57,
        "high" : 1.66,
        "volume" : "65600",
        "symbol" : "ALSK",
        "adjusted_close" : .59
    }
}

Thanks so much... that was easy: db.getCollection('Accuchecks').find({}).forEach( function(obj) { obj.difference.close = parseFloat( obj.difference.close ); db.getCollection('Accuchecks').save(obj); } ); — Tomasso, Feb 04 '16 at 14:32

Why does the following mongodb group query return always 0?

1 Answers1