0

I am new to Mongo aggregation and I am trying to get the following to work.

I have the following document:

{
  "gatewayId" : 3,
  "gatewayReadings" : [ {
  "timestamp" : 1486570609479,
  "temperature_1" : 28.5625,
  "temperature_2" : 30.375,
  "battery_voltage" : 3.525
 }, {
  "timestamp" : 1486570624479,
  "temperature_1" : 29.75,
  "temperature_2" : 30.375,
  "battery_voltage" : 3.525
 }, {
  "timestamp" : 1486570639479,
  "temperature_1" : 33.0625,
  "temperature_2" : 32.0,
  "battery_voltage" : 3.525
}, {
  "timestamp" : 1486570669481,
  "temperature_1" : 30.375,
  "temperature_2" : 29.75,
  "battery_voltage" : 3.525
}
...
}

I am trying to get the averages of each gatewayReadings field base on a timeslice of 1 min, 15min, 1 hour, etc.

I use morphia but thought using Jongo for the aggregation would be easier.

Here is the code I currently have (for a 15 min timeslice) which I can't get to work:

Aggregate.ResultsIterator<Object> readings = 
            gatewayComputed.aggregate(
                "{ $match: { 'gatewayId': " + gatewayId + " }}," +
                "{ $unwind: '$gatewayReadings' }," +
                "{ $group: {"+ 
                    "_id:  { " + 
                        "$subtract: [" +
                        "{ $subtract: [ '$timestamp', new Date('1970-01-01') ] }," +
                        "{ $mod: [ " +
                            "{ $subtract: [ '$timestamp', new Date('1970-01-01') ] }," +
                            "1000 * 60 * 15" +
                            "]}" +                          
                        "]" +
                    "}," +                  //end of _id
                    "temperature_1 : { $avg : '$temperature_1' }," +
                    "temperature_2 : { $avg : '$temperature_2' }," +
                    "battery_voltage : { $avg : '$battery_voltage' }" +
                "}}"                        //end of group
            ).as(Object.class);

What am I doing wrong? Any help would be appreciated (Jongo or morphia).

Update: I eventually got this working with:

database.aggregate(
"{ $match: { '_id': " + gatewayId + " }}")  

.and("{ $facet: 
    { 'Averages': [
        { $unwind: '$gatewayReadings' },
        { $sort: {'gatewayReadings.timestamp': 1}},
        { $group: { _id: {
            year: { $year: '$gatewayReadings.timestamp' },
            dayOfYear: { $dayOfYear: '$gatewayReadings.timestamp' },
            interval: { $subtract: [ 
                { $minute: '$gatewayReadings.timestamp' },
                { $mod: [{ $minute: '$gatewayReadings.timestamp'}, 15] }
            ]}},
            timestamp: { $last: '$gatewayReadings.timestamp'},
            temperature_1: { $avg: '$gatewayReadings.temperature_1'},
            temperature_2: { $avg: '$gatewayReadings.temperature_2'}, 
            battery_voltage: { $avg: '$gatewayReadings.battery_voltage'},
            count: { $sum: 1} } 
        }]
    }
}").as(Object.class)
MarkH
  • 1
  • 1
  • Change your references to use `gatewayReadings`. For example `$timestamp` to `$gatewayReadings.timestamp`. Btw you can use [$facet](https://docs.mongodb.com/manual/reference/operator/aggregation/facet/) to get all the time slices at once. – s7vr Feb 10 '17 at 21:22
  • @Veeram, thanks for the reply. I modified the code and it works but, like you said, I'm only getting a single entry back. I've searched for examples using $facet but can't find any using time slice. Could you perhaps provide an example? Thanks for your help. – MarkH Feb 13 '17 at 08:41
  • I'm not sure if morphia supports 3.4 aggregation stages. Please take at look at this example. http://stackoverflow.com/questions/40864007/how-to-join-and-sort-few-collections-in-one-pipeline-in-mongo/40900971#40900971. All you need different time slices as a input in a facet. – s7vr Feb 14 '17 at 01:08
  • @Veeram thanks for your help on this. You pointed me in the right direction. – MarkH Feb 16 '17 at 08:26

0 Answers0