We have IoT telemetry data coming through into 1 hour buckets (documents with many samples).
Example document:
{
"_id" : ObjectId("5f5024314a74f35fc6fb37e1"),
"date" : ISODate("2020-09-02T23:00:00.000+0000"),
"device" : ObjectId("5dd7596761ced7001253aab3"),
"telemetry" : "total-active-power",
"max" : 17175070000.0,
"min" : 17174710000.0,
"nsamples" : NumberInt(4),
"samples" : [
{
"date" : ISODate("2020-09-02T23:00:07.194+0000"),
"data" : {
"samples" : NumberInt(12),
"latest" : 17174710000.0
}
},
{
"date" : ISODate("2020-09-02T23:01:07.328+0000"),
"data" : {
"samples" : NumberInt(12),
"latest" : 17174730000.0
}
},
{
"date" : ISODate("2020-09-02T23:02:07.500+0000"),
"data" : {
"samples" : NumberInt(12),
"latest" : 17174760000.0
}
},
{
"date" : ISODate("2020-09-02T23:03:07.751+0000"),
"data" : {
"samples" : NumberInt(12),
"latest" : 17174790000.0
}
}
],
"sum" : 68699160000.0
}
It is easy to create an aggregate query that finds the latest value for 1 device and 1 telemetry ObjectId, but it seems impossible to get the latest value for MANY devices and telemetry ObjectIds.
Can do: Latest value for device ObjectId(“5dd7596761ced7001253aab3”) and telemetry “total-active-power”
Can’t do: Latest value for device ObjectId array (many) and “total-active-power”
In SQL I would do it like this (timescaledb):
SELECT data.* FROM vehicles v
INNER JOIN LATERAL (
SELECT * FROM location l
WHERE l.vehicle_id = v.vehicle_id
ORDER BY time DESC LIMIT 1
) AS data
ON true
ORDER BY v.vehicle_id, data.time DESC;
See Timescale Documentation | Querying data
How would I do this in a Mongo aggregation?
Thanks
!