MongoDB collect / aggregate time series into an array-CodePudding

Following the examples I have two types of data in the same time series

db.weather.insertMany( [
{
  "metadata": { "sensorId": 5578, "type": "temperature" },
  "timestamp": ISODate("2021-05-18T00:00:00.000Z"),
  "temp": 72
},//....

and..

db.weather.insertMany([
{
 "metadata": {"sensorId": 5578, "type": "humidity" },
 "timestamp": ISODate("2021-05018T00:00:001Z"),
 "humpercent": 78
 },//...

and I want to be able to serve simple requests by aggregating the data as:

{
 sensorId: 5578,
 humidityData: [78, 77, 75 ...],
 tempData: [72, 72, 71...]
}

which seems like the obvious use case, but the

db.foo.aggregate([{$group: {_id: "$sensorId"}}])

function on sensorId only returns the ids with no other fields. am i missing a simple identity aggregation function or a way to collect into an array?

CodePudding user response：

What you are looking for is the $addToSet Operator:

db.foo.aggregate([{
  $group: {
      _id: "$metadata.sensorId",
      temp: {
        $addToSet: "$temp"
      },
      humidity: {
        $addToSet: "$humpercent"
      }
    }
  }])

Note that the order of elements in the returned array is not specified.

CodePudding user response：

If all you have is two categories, you can simply $push them:

db.collection.aggregate([
  {$sort: {timestamp: 1}},
  {$group: {
      _id: {sensorId: "$metadata.sensorId"},
      temp: {$push: "$temp"},
      humidity: {$push: "$humpercent"}
    }
  }
])

See how it works on the playground example - small

But if you want the generic solution for multiple measurements you need something like:

db.collection.aggregate([
  {$sort: {timestamp: 1}},
  {$set: {m: "$$ROOT"}},
  {$unset: ["m.metadata", "m.timestamp", "m._id"]},
  {$set: {m: {$first: {$objectToArray: "$m"}}}},
  {$group: {
      _id: {type: "$metadata.type", sensorId: "$metadata.sensorId"},
      data: {$push: "$m.v"}}
  },
  {$project: {_id: 0,  data: 1, type: {k: "type", v: "$_id.type"}, sensorId: "$_id.sensorId"}},
  {$group: {
      _id: "$sensorId",
      data: {$push: {k: "$type.v", v: "$data"}}
  }},
  {$project: {_id: 0, data: {"$mergeObjects": [{$arrayToObject: "$data"}, {sensorId: "$_id"}]}
  }},
  {$replaceRoot: {newRoot: "$data"}}
])

See how it works on the playground example - generic