Reputation: 441
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
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