Got aggregation per hour working, but now to sort it

[Peter_Kaagman]

Absolute beginners by Bowie comes to mind…

Started myself a project to learn mongoDB: Got an arduino uploading temperature, pressure and humidity every minute. Could have done that to a SQL database… but want to get aquanted with mongoDB… so the data lives on a mongoDB server.

Plain old data dumps, filtered on sensor type, go quite wel. But a measurement every minute accumulates a lot of datapoints real soon. So I wanted to do some aggregation. Average per hour to start with.
Created this piece of code which I load into mongsh:

result = db.sensordatas.aggregate(
 {
  $match: {sensorType: "temp"}
 },
 {
  $sort: {timeStamp: 1}
 },
 { "$project": {
      "y":{"$year":"$timestamp"},
      "m":{"$month":"$timestamp"},
      "d":{"$dayOfMonth":"$timestamp"},
      "h":{"$hour":"$timestamp"},
      "sensorValue":1,
      "timestamp":1,
      "sensorType": 1 }
 },
 { "$group":{
       "_id": {  "type": "$sensorType","year":"$y","month":"$m","day":"$d","hour":"$h"},
       "gemiddeld":{ "$avg": "$sensorValue"}
   }
 }
)

which yields the following result:

  {
    _id: { type: 'temp', year: 2022, month: 11, day: 25, hour: 13 },
    gemiddeld: Decimal128("19.6745")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 26, hour: 4 },
    gemiddeld: Decimal128("17.774")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 25, hour: 5 },
    gemiddeld: Decimal128("19.15593220338983050847457627118644")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 28, hour: 9 },
    gemiddeld: Decimal128("15.35220338983050847457627118644068")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 28, hour: 14 },
    gemiddeld: Decimal128("16.77283333333333333333333333333333")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 27, hour: 8 },
    gemiddeld: Decimal128("15.89933333333333333333333333333333")
  }

I was pretty pleased with myself getting this far. But kinda anoyed when I noticed the result come in random order. Allready tried to do a sort on it… at the begining and at the end of the chain… but to no result (I could observe)

What am I doing wrong?

Peter

[Jason_Tran]

Hi Peter - Welcome to the community!

Would the following work for you? I only tested it on your current output as I am not sure what the data would look like at each stage of the pipeline.

db.testcoll.aggregate(
{
  '$sort': { '_id.year': -1, '_id.month': -1, '_id.day': -1, '_id.hour': -1 }
})
[
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 28, hour: 14 },
    gemiddeld: Decimal128("16.77283333333333333333333333333333")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 28, hour: 9 },
    gemiddeld: Decimal128("15.35220338983050847457627118644068")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 27, hour: 8 },
    gemiddeld: Decimal128("15.89933333333333333333333333333333")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 26, hour: 4 },
    gemiddeld: Decimal128("17.774")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 25, hour: 13 },
    gemiddeld: Decimal128("19.6745")
  },
  {
    _id: { type: 'temp', year: 2022, month: 11, day: 25, hour: 5 },
    gemiddeld: Decimal128("19.15593220338983050847457627118644")
  }
]

Although i’m not sure what the original data looks like, perhaps another idea would be to use $addFields and have a Date value field and sort on that. Perhaps other community members have other suggestions as well

Regards,
Jason

[Peter_Kaagman]

Thanks Jason… I’ll give it a try

Tried it… did the trick… thank you for learning me something.