mongoose aggregation $avg $or returns null value

Question

I want to calculate an average of the measurements between dates. This is the code of the aggregation

db.measurements.aggregate([{
  $match: {
    $or: [{
      date: {
        '$gte': ISODate('2016-06-01T22:52:46Z'),
        '$lte': ISODate('2016-06-02T22:52:46Z')
      }
    }]
  }
}, {
  $group: {
    _id: "$workplace",
    avgTemperature: {
      $avg: "$temperature"
    }
  }
}]);

It returns this the first one has an null value but it's inpossible to have an null average

{ "_id" : ObjectId("5761df5981fb3799edbeda88"), "avgTemperature" : null }
{ "_id" : ObjectId("5761d3c681fb3799edbeda83"), "avgTemperature" : 16.323529411764707 }

To prove it i run this aggegration

db.measurements.aggregate([{
  $match: {
     workplace: {
        $in: [ObjectId("5761df5981fb3799edbeda88")]
      },
    $or: [{
      date: {
        '$gte': ISODate('2016-06-01T22:52:46Z'),
        '$lte': ISODate('2016-06-02T22:52:46Z')
      }
    }]
  }
}]);

The response data

{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74773"), "date" : ISODate("2016-06-02T11:28:56Z"), "temperature" : "11.6", "noise" : "19.0", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74785"), "date" : ISODate("2016-06-02T03:49:59Z"), "temperature" : "12.2", "noise" : "17.7", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74795"), "date" : ISODate("2016-06-02T01:39:06Z"), "temperature" : "12.6", "noise" : "55.6", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74797"), "date" : ISODate("2016-06-02T04:07:47Z"), "temperature" : "11.8", "noise" : "21.5", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf747d8"), "date" : ISODate("2016-06-02T10:30:24Z"), "temperature" : "6.4", "noise" : "47.6", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf7480b"), "date" : ISODate("2016-06-02T02:12:27Z"), "temperature" : "12.2", "noise" : "39.3", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74811"), "date" : ISODate("2016-06-02T10:01:12Z"), "temperature" : "23.8", "noise" : "29.3", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74816"), "date" : ISODate("2016-06-02T22:42:44Z"), "temperature" : "3.8", "noise" : "5.7", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74889"), "date" : ISODate("2016-06-02T17:16:12Z"), "temperature" : "4.3", "noise" : "67.4", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf7488c"), "date" : ISODate("2016-06-02T01:59:37Z"), "temperature" : "24.4", "noise" : "55.2", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74899"), "date" : ISODate("2016-06-02T22:31:13Z"), "temperature" : "9.5", "noise" : "66.4", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf748a9"), "date" : ISODate("2016-06-02T15:44:03Z"), "temperature" : "24.8", "noise" : "54.5", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf748af"), "date" : ISODate("2016-06-02T16:46:51Z"), "temperature" : "2.7", "noise" : "14.8", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf748d6"), "date" : ISODate("2016-06-02T19:37:01Z"), "temperature" : "13.0", "noise" : "22.2", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf748f2"), "date" : ISODate("2016-06-02T07:30:30Z"), "temperature" : "5.7", "noise" : "16.9", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74912"), "date" : ISODate("2016-06-02T17:28:55Z"), "temperature" : "10.9", "noise" : "13.0", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74940"), "date" : ISODate("2016-06-02T12:07:37Z"), "temperature" : "18.4", "noise" : "64.4", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74946"), "date" : ISODate("2016-06-02T19:14:08Z"), "temperature" : "12.4", "noise" : "69.5", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf74950"), "date" : ISODate("2016-06-02T22:19:55Z"), "temperature" : "16.3", "noise" : "22.4", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
{ "_id" : ObjectId("576f1a7ab1a9cb96dbf7495d"), "date" : ISODate("2016-06-02T06:23:00Z"), "temperature" : "16.2", "noise" : "51.8", "workplace" : ObjectId("5761df5981fb3799edbeda88") }
Type "it" for more

How can i fix it to calculate an average without problems. Or is the code fine, and the problem is not code related? because i did import mock data...

Answer 1

As far I know aggregate can't change existing data type and $avg works with numeric value (like: int, long, float, double, number). Currently MongoDB has no operator to parse a string into a number.

So you need to convert your schema String to Number for temperature field and write a script to convert your database temperature value for string to number and then your code will return perfect result. your query is perfect.