java mongodb分组排序,Mongodb选择所有字段按一个字段分组并按另一个字段排序

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

distict by field 'chainId'

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?

解决方案

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.