Mongodb select all fields group by one field and sort by another field

Question

We have collection 'message' with following fields

_id |   messageId |  chainId | createOn

1   |       1     |    A     | 155
2   |       2     |    A     | 185
3   |       3     |    A     | 225
4   |       4     |    B     | 226
5   |       5     |    C     | 228
6   |       6     |    B     | 300

We want to select all fields of document with following criteria

1. distict by field 'chainId'
   
2. order(sort) by 'createdOn' in desc order

so, the expected result is

_id |   messageId |  chainId | createOn

3   |       3     |    A     | 225
5   |       5     |    C     | 228
6   |       6     |    B     | 300

We are using spring-data in our java application. I tried to go with different approaches, nothing helped me so far.
Is it possible to achieve above with single query?

Answer 1

What you want is something that can be achieved with the aggregation framework. The basic form of ( which is useful to others ) is:

db.collection.aggregate([

    // Group by the grouping key, but keep the valid values
    { "$group": {
        "_id": "$chainId",
        "docId": { "$first": "$_id" },
        "messageId": { "$first": "$messageId" },
        "createOn": { "$first": "$createdOn" }
    }},

    // Then sort
    { "$sort": { "createOn": -1 } }

])

So that "groups" on the distinct values of "messageId" while taking the $first boundary values for each of the other fields. Alternately if you want the largest then use $last instead, but for either smallest or largest by row it probably makes sense to $sort first, otherwise just use $min and $max if the whole row is not important.

See the MongoDB aggregate() documentation for more information on usage, as well as the driver JavaDocs and SpringData Mongo connector documentation for more usage of the aggregate method and possible helpers.