CODEWITHSUNDEEP
mongodbtime-series
puttputt
puttputt

Reputation: 1269

Mongodb aggregate for timeseries data

I have a timeseries dataset with a few hundred thousand records in it. I am trying to create an aggregate query in mongo to group this data in intervals all while averaging the price.

Ideally I would want 10minute intervals (600000ms) and the price averages. I'm not too sure how to carry on from where I am at.

Data ~a few hundred thousand records:

  {
    "time" : 1391485215000,
    "price" : "0.00133355",
    }

query = [
    {   
        "$project": {
            "_id":"$_id",
            "price":"$price",
            "time": {
                xxxx
            }
        }
    },
    {   
        "$group": {xxxx}
    }
]

Upvotes: 1

Views: 817

Answers (1)

puttputt
puttputt

Reputation: 1269

So it would appear that I had a fundamental flaw in my Schema. I was using an epoch timestamp instead of mongo's Date type, as well as storing the other numbers as strings instead of doubles. I tried a few workarounds but it doesn't look like you are able to use the built in aggregate functions unless they are of the correct type.

    $project: {
        year: { $year: '$time'},
        month: { $month: '$time'},
        day: { $dayOfMonth: '$time'},
        hour: { $hour: '$time'},
        price: 1,
        total: 1,
        amount: 1
    }
},
{
    $group : {
        _id: { year: '$year', month: '$month', day: '$day', hour: '$hour' },
        price:{
            $avg: "$price"
        },
        high:{
            $max: "$price"
        },
        low:{
            $min: "$price"
        },
        amount:{
            $sum: "$amount"
        },
        total:{
            $sum: "$total"
        }
    }

Upvotes: 4

Related Questions