CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Paul Grove
Paul Grove

Reputation: 248

MongoDB aggregating the $max and selecting from $max's document

I want to aggregate finding the max of one field, while including other fields from the document of whichever $max value was select.

I may be going about this wrong, but here is an example:

Sample Data:

{
  utime: 1,
  device: "host1",
  latest_events: ['that']
},{
  utime: 2,
  device: "host1",
  latest_events: ['this', 'that']
},{
  utime: 3,
  device: "host1",
  latest_events: ['that', 'something']
},{
  utime: 1,
  device: "host2",
  latest_events: ['this', 'that']
},{
  utime: 2,
  device: "host2",
  latest_events: ['that']
},{
  utime: 3,
  device: "host2",
  latest_events: ['this', 'the_other']
}

This is my desired outcome:

[
  {
    _id: 'host1',
    utime: 3,
    latest_events: ['that', 'something']
  },{
    _id: 'host2',
    utime: 3,
    latest_events: ['this', 'the_other']
  }
]

So this is my closest guess so far:

db.data.aggregate([
  {
    $group: {
      _id: '$device',
     utime: {'$max': '$utime'},
     latest_events: {/* I want to select the latest_events based on the max utime*/}
    }
  }
]);

This could be summarised as saying "I want the latest latest_events for each device".

I've been trying to work out how I might do this with multiple aggregate stages or using project or something, but so far my only working solution is use multiple queries.

Upvotes: 2

Views: 115

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151092

You were quite close in what you were basically saying, but you seemed to have missed the documentation for the $last operator which would be used like this:

db.data.aggregate([
    // Sort in host and utime order
    { "$sort": { "host": 1, "utime": 1 } },

    // Group on the "last" item on the boundary
    { "$group": {
        "_id": "$device",
        "utime": { "$last": "$utime" },
        "latest_events": { "$last": "$latest_events" }
    }}
])

You essentially $sort in the order that you require and then the $last is used on the fields in the $group you return to be the "last" items occurring on the grouping boundary from the sort order you have done.

Which produces:

{ "_id" : "host2", "utime" : 3, "latest_events" : [ "this", "the_other" ] }
{ "_id" : "host1", "utime" : 3, "latest_events" : [ "that", "something" ] }

You can optionally add an additional $sort at the end if you want the "host" values in order.

Upvotes: 1

Related Questions