MongoDB - Aggregate max/min/average for multiple variables at once

Question

I am logging data into MongoDB in the following format:

{ "_id" : ObjectId("54f2393f80b72b00079d1a53"), "outT" : 10.88, "inT3" : 22.3, "light" : 336, "humidity" : 41.4, "pressure" : 990.31, "inT1" : 22.81, "logtime" : ISODate("2015-02-28T21:55:11.838Z"), "inT2" : 21.5 }
{ "_id" : ObjectId("54f2394580b72b00079d1a54"), "outT" : 10.88, "inT3" : 22.3, "light" : 338, "humidity" : 41.4, "pressure" : 990.43, "inT1" : 22.75, "logtime" : ISODate("2015-02-28T21:55:17.690Z"), "inT2" : 311.72 }
...

As you can see there is a single time element and multiple readings logged. I want to aggregate across all of the readings to provide a max min and average for each variable grouped by hour of day. I have managed to do this for a single variable using the following aggregation script:

db.logs.aggregate(
    [ 
        { 
            $match: { 
                logtime: { 
                    $gte: ISODate("2015-03-01T00:00:00.000Z"), 
                    $lt: ISODate("2015-03-03T00:00:00.000Z") 
                }
            }
        }, 
        { 
            $project: {_id: 0, logtime: 1, outT: 1}
        }, 
        {
            $group: {
                _id: {
                    day: {$dayOfYear: "$logtime"},
                    hour: {$hour: "$logtime"}
                }, 
                average: {$avg: "$outT"}, 
                max: {$max: "$outT"}, 
                min:{$min: "$outT"} 
            }
        }
    ]
)

which produces:

{ "_id" : { "day" : 61, "hour" : 22 }, "average" : 3.1878750000000116, "max" : 3.44, "min" : 3 }
{ "_id" : { "day" : 61, "hour" : 14 }, "average" : 13.979541666666638, "max" : 17.81, "min" : 8.81 }
...

I would like to produce output which looks like:

{"outT": { output from working aggregation above },
 "inT1": { ... },
 ...
}

Everything I try seems to throw an error in the mongo console. Can anyone help?

Thanks

Clear as mud to me, but are you perhaps looking for "statistics" over your aggregation result? And return those along with the aggregation result? — Neil Lunn, Mar 05 '15 at 13:11

score 2 · Accepted Answer · answered Mar 05 '15 at 14:22

You can do this by including each statistic in your $group with a different name and then following that with a $project stage to reshape it into your desired format:

db.logs.aggregate([
    { 
        $match: { 
            logtime: { 
                $gte: ISODate("2015-02-28T00:00:00.000Z"), 
                $lt: ISODate("2015-03-03T00:00:00.000Z") 
            }
        }
    }, 
    { 
        $project: {_id: 0, logtime: 1, outT: 1, inT1: 1}
    }, 
    {
        $group: {
            _id: {
                day: {$dayOfYear: "$logtime"},
                hour: {$hour: "$logtime"}
            }, 
            outT_average: {$avg: "$outT"}, 
            outT_max: {$max: "$outT"}, 
            outT_min:{$min: "$outT"},
            inT1_average: {$avg: "$inT1"}, 
            inT1_max: {$max: "$inT1"}, 
            inT1_min:{$min: "$inT1"}
        }
    },
    {
        $project: {
            outT: {
                average: '$outT_average',
                max: '$outT_max',
                min: '$outT_min'
            },
            inT1: {
                average: '$inT1_average',
                max: '$inT1_max',
                min: '$inT1_min'
            }
        }
    }        
])

This gives you output that looks like:

{
    "_id" : {
        "day" : 59,
        "hour" : 21
    },
    "outT" : {
        "average" : 10.88,
        "max" : 10.88,
        "min" : 10.88
    },
    "inT1" : {
        "average" : 22.78,
        "max" : 22.81,
        "min" : 22.75
    }
}

score -1 · Answer 2 · edited Aug 08 '19 at 12:19

-1

$max in Mongodb gets the maximum of the corresponding values from all documents in the collection. $min gets the minimum values from all documents in the collection. $avg gets the average value from the collection.

you must go through the Mongodb link for sample examples.

edited Aug 08 '19 at 12:19

Samuel Liew

76,741
107
159
260

answered Nov 17 '15 at 08:38

Venki

535
2
8
21

MongoDB - Aggregate max/min/average for multiple variables at once

2 Answers2

Linked