CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
ccarnley7
ccarnley7

Reputation: 441

Get Most Recent Document By Type With Mongo Aggregation Query

I have a mongo collection that I want to get some specific data out of. I need the most recent document of each type. The data looks like this:

{"id":"100", "type":"A", "date":"2018-01-15T22:19:07.017", "firstName":"Jack", "lastName":"Miller", "age":34}
{"id":"101", "type":"B", "date":"2017-12-04T23:28:49.889", "firstName":"Bob", "lastName":"Jackson", "age":43}
{"id":"102", "type":"B", "date":"2017-12-11T22:06:33.150", "firstName":"Karen", "lastName":"Decker", "age":12}
{"id":"103", "type":"A", "date":"2017-12-18T21:27:31.847", "firstName":"Sally", "lastName":"Rich", "age":19}
{"id":"104", "type":"A", "date":"2017-12-19T23:07:01.292", "firstName":"Joe", "lastName":"Hunk", "age":11}
{"id":"105", "type":"C", "date":"2018-01-08T21:25:25.715", "firstName":"Tallia", "lastName":"Hope", "age":86}

So the most recent of each type would look like this:

{"id":"100", "type":"A", "date":"2018-01-15T22:19:07.017", "firstName":"Jack", "lastName":"Miller", "age":34}
{"id":"102", "type":"B", "date":"2017-12-11T22:06:33.150", "firstName":"Karen", "lastName":"Decker", "age":12}
{"id":"105", "type":"C", "date":"2018-01-08T21:25:25.715", "firstName":"Tallia", "lastName":"Hope", "age":86}

I thought that the aggregation operation found here (https://docs.mongodb.com/manual/aggregation/) was the way to go. I tried using the $group accumulator to group on the type and then get the $max date, but didn't know how to get the rest of the document. I could easily do this with multiple queries but I'd like to use one query if possible.

Is the aggregation operation is the right option? Maybe I'm missing something simple here.

Upvotes: 8

Views: 2124

Answers (1)

Subhashree Pradhan
Subhashree Pradhan

Reputation: 812

You can try this solution :

db.test.aggregate([
    { $sort: { "date": -1 } },
    { $group: { _id: "$type", latest: { $first: "$$ROOT" } }},
    { $project : {_id : 0, id : "$latest.id", type : "$latest.type", date : "$latest.date", firstName : "$latest.firstName", lastName : "$latest.lastName", }},
    { $sort: { "type": 1 } }
])

If you provide this input :

{"id":"100", "type":"A", "date":"2018-01-15T22:19:07.017", "firstName":"Jack", "lastName":"Miller", "age":34}
{"id":"101", "type":"B", "date":"2017-12-04T23:28:49.889", "firstName":"Bob", "lastName":"Jackson", "age":43}
{"id":"102", "type":"B", "date":"2017-12-11T22:06:33.150", "firstName":"Karen", "lastName":"Decker", "age":12}
{"id":"103", "type":"A", "date":"2017-12-18T21:27:31.847", "firstName":"Sally", "lastName":"Rich", "age":19}
{"id":"104", "type":"A", "date":"2017-12-19T23:07:01.292", "firstName":"Joe", "lastName":"Hunk", "age":11}
{"id":"105", "type":"C", "date":"2018-01-08T21:25:25.715", "firstName":"Tallia", "lastName":"Hope", "age":86}

This is the desired output that is being produced by the query :

{ "id" : "100", "type" : "A", "date" : "2018-01-15T22:19:07.017", "firstName" : "Jack", "lastName" : "Miller" }
{ "id" : "102", "type" : "B", "date" : "2017-12-11T22:06:33.150", "firstName" : "Karen", "lastName" : "Decker" }
{ "id" : "105", "type" : "C", "date" : "2018-01-08T21:25:25.715", "firstName" : "Tallia", "lastName" : "Hope" }

Upvotes: 6

Related Questions