CODEWITHSUNDEEP
mongodbaggregation-framework
Maan
Maan

Reputation: 188

Mongodb: select the last event from a time series

I am trying to build a time series in a Mongo database, and based on advice from several SO posts, am going with one document for each event, such as:

{
  _id: ObjectId("51a67033868eda7666b0a49c"),
  station_id: 12,
  timestamp: ISODate("2013-05-29T21:16:35.929Z"),
  value: 50
}

Getting the history for a particular station is easy enough(db.mydata.find({station_id: 12}, {timestamp: 1})).

The bigger challenge is getting the latest value across all of my stations. (Note that the latest timestamp is going to be different for each station, so I can't do a naive query.) In SQL, I would do:

SELECT station_id, timestamp, value
FROM mydata
WHERE (station_id, timestamp) IN (
  SELECT station_id, MAX(timestamp)
  FROM mydata
  GROUP BY station_id
)

How would you do something similar in Mongodb? The aggregation framework is pretty powerful, but I can't figure out a way to do this type of analysis using it.

Thanks a lot for any help.

Upvotes: 0

Views: 1130

Answers (2)

Asya Kamsky
Asya Kamsky

Reputation: 42352

You can use the aggregation framework but it's a little bit tricky. Here is how:

db.mydata.aggregate( [
      { $sort : {timestamp:-1}},
      { $group : { _id : "$station_id",
                 timestamp : {$first:"$timestamp"},
                 value : {$first:"$value"}
      }  }
] )

Basically you are sorting your input by timestamp descending - and then you aggregate and for each unique station_id you keep the first timestamp and value you encounter (in sorted order!)

Upvotes: 3

Lucia Pasarin
Lucia Pasarin

Reputation: 2308

SELECT station_id, timestamp, value
FROM mydata
WHERE (station_id, timestamp) IN (
  SELECT station_id, MAX(timestamp)
  FROM mydata
  GROUP BY station_id
)

I think would be something similar to

db.aggregate( 
    { $group : { _id : "$station_id", max_timestamp : { $max : timestamp } } },
    { $project : {station_id : 1, max_timestamp : 1, value : 1 } }
)

Upvotes: 0

Related Questions