MongoDB Time Series Aggregation

Question

I'm reading sensor data at configurable intervals, but for this example let's say every 30 seconds. I want to be able to group the data by hour, day, week, month and year intervals. I also want to be able to aggregate averages over a group of sensors for the same intervals.

Example Use Cases:

1. Get the last 4 month totals for sensor id: x

2. Get the last 4 month average totals for sensors with group_id: y

Use Case 2 Clarified

The following all have the same group_id

sensor_id | month 1 | month 2 | month 3 | month 4
     1    |   10    |   15    |   5     |    10
     2    |   20    |   30    |   30    |    5
     3    |   5     |   20    |   40    |    20

Output

month1 : 11.67, month2: 21.67, month3: 25, month4: 11.67

I have seen lots of approaches to storing time-series data in MongoDB. I'm thinking of having a collection for each interval, including the raw time values and having each document expire after a certain time period.

MonthPoint example document

{
    "_id": {
        "$oid": "55270059a791051d4a4e0e41"
    },
    "sensor_id": "1",
    "group_id" : "4",
    "timestamp": {
        "$date": "2015-04-01T00:00:00.000Z"
    },
    "sum": 40
    "count": 200
}

For each point that comes in I would have to perform a write to each collection, but reading the data would be quick.

Use Case 1, would be a very simple query:

MonthPoints.find({
    sensor_id : x,
    timestamp : {
        $gte: startDate,
        $lt: currentDate
    }
});

But how would I aggregate for Use Case 2? Is it possible to achieve this in one aggregation? I see how it could be achieved using 4 separate aggregations, getting the average for each month across a group_id.

Sundar Sundar · Accepted Answer · 2015-07-21T15:30:08

I have the following collection:

> db.mpoints.find()
{ "_id" : ObjectId("55ae6d35931d911f97d09977"), "g" : 55, "s" : 1, "v" : 10, "d" : ISODate("2015-03-31T00:00:00Z") }
{ "_id" : ObjectId("55ae6d3d931d911f97d09978"), "g" : 55, "s" : 2, "v" : 20, "d" : ISODate("2015-03-31T00:00:00Z") }
{ "_id" : ObjectId("55ae6d42931d911f97d09979"), "g" : 55, "s" : 3, "v" : 5, "d" : ISODate("2015-03-31T00:00:00Z") }
{ "_id" : ObjectId("55ae6d49931d911f97d0997a"), "g" : 11, "s" : 3, "v" : 5, "d" : ISODate("2015-03-31T00:00:00Z") }
{ "_id" : ObjectId("55ae6d54931d911f97d0997b"), "g" : 55, "s" : 3, "v" : 20, "d" : ISODate("2015-04-30T00:00:00Z") }
{ "_id" : ObjectId("55ae6d8a931d911f97d0997c"), "g" : 55, "s" : 2, "v" : 20, "d" : ISODate("2015-04-30T00:00:00Z") }
{ "_id" : ObjectId("55ae6dc2fc560cbbe2b22400"), "g" : 55, "s" : 1, "v" : 50, "d" : ISODate("2015-04-30T00:00:00Z") }

The mapping of the fields are:

d - Is the date - I have it as the last date of the month. You can have it also as first date of the month. It does not matter.
g - your group id
s - your sensor id
v - your value

The aggregate call is

db.mpoints.aggregate([
{
    $match: {
        "g": 55,
        "d": {
            "$gte": ISODate("2015-03-31T00:00:00Z"),
            "$lte": ISODate("2015-04-30T00:00:00Z")
        }
    }
},
{
    $group: {
        "_id": "$d",
        total: {
            $sum: '$v'
        },
        count: {
             $sum: 1
        }
    }
}
])

And the result is

{ "_id" : ISODate("2015-04-30T00:00:00Z"), "total" : 90, "count" : 3 }
{ "_id" : ISODate("2015-03-31T00:00:00Z"), "total" : 35, "count" : 3 }

You get the total for each month and number of entries for that month.

MongoDB Time Series Aggregation

1 Answers