CODEWITHSUNDEEP
mongodb
Abdul Manaf
Abdul Manaf

Reputation: 4888

Get related fields of max value when grouping in MongoDB

What would be the corresponding mongo query for below SQL that gets the lat and long of the most recent doc for each did:

Query : what is the last location for all devices 

did  : device id
ts   : time stamp
lat  : latitude 
long : longitude

SQL Query 

  SELECT 
        did , ts, `lat`, `long`
    FROM
        points
            JOIN
        (SELECT 
            MAX(ts) AS maxTs, did
        FROM
            points
        GROUP BY did) MxData ON points.ts = MxData.maxTs
            AND MxData.did = points.did;

Below is the Mongo Query, I am able to get max time stamp for device but not sure how to print the corresponding lat and long 

db.points.aggregate( [{ $group:{_id: "$did", maxTs: { $max: "$ts" }}}]).pretty();

Upvotes: 1

Views: 84

Answers (1)

Abdul Manaf
Abdul Manaf

Reputation: 4888

I found the solution , the query is 

db.points.aggregate([
    {$sort:{"ts":1}} , 
    {$group:{"_id":"$did" , result:{$last:"$ts"}  , lat:{$last:"$lat"} , lon:{$last:"$lon"}}}
    ]);

Upvotes: 1

Related Questions