How to aggregate documents with mongodb driver for nodejs with time interval

Question

I'm currently stuck on this. I have collection like:

{ 
    "_id": ObjectId("55820292e3dc84aa0c9c63bc"), 
    "creation_at": ISODate("2015-06-17T23:28:18.896Z"), 
    "cpu": 36, 
    "mem": "1.08" 
}

And puts some logs into it each 30 sec. I want to get summary info by period of time, like:

[
 {
   'interval': ['2015-06-07 13:00:00', '2015-06-07 13:59:59'],
   'cpu': {
      'max': 20,
      'min': 1,
      'avg': 3
   },
   'memory': {
      'max': 40,
      'min': 35,
      'avg': 38
   }
 },

 {
   'interval': ['2015-06-07 14:00:00', '2015-06-07 14:59:59'],
   'cpu': { ... },
   'memory': { ... }
 },

  ...
  ...
  ...
]

From mongo docs I found that this can be achieved with aggregate framework, but I'm stuck with the generation of time interval. Could you please advice me?

Answer 1

Well memory appears to a "string" in your data so that is going to be a problem unless you convert it. With numeric values this is easy for the aggregation framework with Date Aggregation Operators:

db.collection.aggregate([
    { "$group": {
        "_id": {
            "year": { "$year": "$creation_at" },
            "month": { "$month": "$creation_at" },
            "day": { "$day": "$creation_at" },
            "hour": { "$hour": "$creation_at" }
        },
        "minCpu": { "$min": "$cpu" },
        "maxCpu": { "$max": "$cpu" },
        "avgCpu": { "$avg": "$cpu" },
        "minMem": { "$min": "$mem" },
        "maxMem": { "$max": "$mem" },
        "avgMem": { "$avg": "$mem" }
    }}
])

That is with intervals of one hour but of course there are finer grained operators. And of course the aggregation fields cannot come out as "nested" as you have them without further projection. But you can do that as well if you want.

If the date aggregation operators seem too terse or "timestamp" values are more your thing then you can use date math instead:

db.collection.aggregate([
    { "$group": {
        "_id": {
            "$subtract": [
                { "$subtract": [ "$creation_at", new Date("1970-01-01") ] },
                { "$mod": [
                    { "$subtract": [ "$creation_at", new Date("1970-01-01") ] },
                    1000 * 60 * 60
                ]}
            ]
        },
        "minCpu": { "$min": "$cpu" },
        "maxCpu": { "$max": "$cpu" },
        "avgCpu": { "$avg": "$cpu" },
        "minMem": { "$min": "$mem" },
        "maxMem": { "$max": "$mem" },
        "avgMem": { "$avg": "$mem" }
    }}
])

Since "subtracting" the "epoch date" value from a date returns the current time in milliseconds as a number.

And where your "string" is an issue here you can always fall back to mapReduce:

db.collection.mapReduce(
    function() {
        var date = this.creation_at.valueOf()
            - ( this.creation_at.valueOf % ( 1000 * 60 * 60 ) );

        emit( new Date(date), {
            cpu: {
                min: parseInt(this.cpu),
                max: parseInt(this.cpu),
                avg: parseInt(this.cpu)
            },
            mem: {
                min: parseFloat(this.mem),
                max: parseFloat(this.mem),
                avg: parseFloat(this.mem)
            }
        })
    },
    function(key,values) {
        var result = {
            cpu: { min: 0, max: 0, avg: 0 },
            mem: { min: 0, max: 0, avg: 0 }
        };

        result.cpu.min = Math.min.apply( null, values.map(function(el) { 
             return el.cpu.min;
        }));
        result.cpu.max = Math.max.apply( null, values.map(function(el) { 
             return el.cpu.max;
        }));
        result.cpu.avg = Array.sum(values.map(function(el) { 
             return el.cpu.avg
        })) / values.length;

        result.mem.min = Math.min.apply( null, values.map(function(el) { 
             return el.mem.min;
        }));
        result.mem.max = Math.max.apply( null, values.map(function(el) { 
             return el.mem.max;
        }));
        result.mem.avg = Array.sum(values.map(function(el) { 
             return el.mem.avg
        })) / values.length;

        return result;
    },
    { "out": { "inline": 1 } }
)

And possibly clean up that reducer logic better than that quick hacky approach.

But the basic principle is extract the interval from the date to group by and then do the math on the result fields.