Get related fields of max value when grouping in MongoDB

Question

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();

Your schema is normalized I think while it should not be in case of mongo. — Yashdeep Hinge
– Yashdeep Hinge, Commented Apr 11, 2017 at 6:55

Abdul Manaf · Accepted Answer · 2017-04-12 11:35:52Z

1

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"}}}
    ]);

answered Apr 12, 2017 at 11:35

Abdul Manaf

4,8883 gold badges29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get related fields of max value when grouping in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related