Select latest document after grouping them by a field in MongoDB

user_428173 0
Viewed 237

I got a question that I would expect to be pretty simple, but I cannot figure it out. What I want to do is this:

  • Find all documents in a collection and:
    • sort the documents by a certain date field
    • apply distinct on one of its other fields, but return the whole document

Best shown in an example.

This is a mock input:

[
  {
    "commandName" : "migration_a",
    "executionDate" : ISODate("1998-11-04T18:46:14.000Z")
  },
  {
    "commandName" : "migration_a",
    "executionDate" : ISODate("1970-05-09T20:16:37.000Z")
  },
  {
    "commandName" : "migration_a",
    "executionDate" : ISODate("2005-11-08T11:58:52.000Z")
  },
  {
    "commandName" : "migration_b",
    "executionDate" : ISODate("2016-06-02T19:48:34.000Z")
  }
]

The expected output is:

[
  {
    "commandName" : "migration_a",
    "executionDate" : ISODate("2005-11-08T11:58:52.000Z")
  },
  {
    "commandName" : "migration_b",
    "executionDate" : ISODate("2016-06-02T19:48:34.000Z")
  }
]

Or, in other words:

  • Group the input data by the commandName field
  • Inside each group sort the documents
  • Return the newest document from each group

My attempts to write this query have failed:

  • The distinct() function will only return the value of the field I am distinct-ing on, not the whole document. That makes it unsuitable for my case.

  • Tried writing an aggregate query, but ran into an issue of how to sort-and-select a single document from inside of each group? The sort aggreation stage will sort the groups among one other, which is not what I want.

I am not too well-versed in Mongo and this is where I hit a wall. Any ideas on how to continue?

For reference, this is the work-in-progress aggregation query I am trying to expand on:

db.getCollection('some_collection').aggregate([
{ $group: { '_id': '$commandName', 'docs': {$addToSet: '$$ROOT'} } }, 
{ $sort: {'_id.docs.???': 1}}
])

Post-resolved edit

Thank you for the answers. I got what I needed. For future reference, this is the full query that will do what was requested and also return a list of the filtered documents, not groups.

db.getCollection('some_collection').aggregate([
{ $sort: {'executionDate': 1}},
{ $group: { '_id': '$commandName', 'result': { $last: '$$ROOT'} } },
{ $replaceRoot: {newRoot: '$result'} }
])

The query result without the $replaceRoot stage would be:

[
  {
    "_id": "migration_a",
    "result": {
      "commandName" : "migration_a",
      "executionDate" : ISODate("2005-11-08T11:58:52.000Z")
    }
  },
  {
    "_id": "migration_b",
    "result": {
      "commandName" : "migration_b",
      "executionDate" : ISODate("2016-06-02T19:48:34.000Z")
    }
  }
]

The outer _id and _result are just "group-wrappers" around the actual document I want, which is nested under the result key. Moving the nested document to the root of the result is done using the $replaceRoot stage. The query result when using that stage is:

[
  {
    "commandName" : "migration_a",
    "executionDate" : ISODate("2005-11-08T11:58:52.000Z")
  },
  {
    "commandName" : "migration_b",
    "executionDate" : ISODate("2016-06-02T19:48:34.000Z")
  }
]
aggregation-framework mongodb-query mongodb
3 Answers

Try this:

db.getCollection('some_collection').aggregate([
 { $sort: {'executionDate': -1}},
 { $group: { '_id': '$commandName', 'doc': {$first: '$$ROOT'} } }
])
user_9036540

I believe this will result in what you're looking for: 

db.collection.aggregate([
  {
    $group: {
      "_id": "$commandName",
      "executionDate": {
        "$last": "$executionDate"
      }
    }
  }
])

You can check it out here

Of course, if you want to match your expected output exactly, you can add a sort (this may not be necessary since your goal is to simply return the newest document from each group):

{
 $sort: {
  "executionDate": 1
 }
}

You can check this version out here.

user_90408660

The use-case the question presents is nearly covered in the $last aggregation operator documentation.

Which summarises:

the $group stage should follow a $sort stage to have the input documents in a defined order. Since $last simply picks the last document from a group.

Query: Link

db.collection.aggregate([
  {
    $sort: {
      executionDate: 1
    }
  },
  {
    $group: {
      _id: "$commandName",
      executionDate: {
        $last: "$executionDate"
      }
    }
  }
]);
user_60822800
Related