Aggregate via Spring MongoTemplate to get max value of a collection

Question

I want to find users by most recent date (Assume the User object has a date field). The data is stored in MongoDB and accessed via a Spring MongoTemplate.

Example of the raw data:

{userId:1, date:10}
{userId:1, date:20}
{userId:2, date:50}
{userId:2, date:10}
{userId:3, date:10}
{userId:3, date:30}

The query should return

 {{userId:1, date:20}, {userId:2, date:50}, {userId:3, date:30}}

The aggregation method Ï am using is

db.table1.aggregate({$group:{'_id':'$userId', 'max':{$max:'$date'}}}, 
{$sort:{'max':1}}).result

In your expected results, you have two entries for userId 3 - is this intentional? Also, the aggregate you use would sort the results by date, but your expected results are sorted by ID - which one do you want? — Trisha, Sep 02 '13 at 14:44

score 7 · Accepted Answer · edited Sep 12 '18 at 14:38

7

You could Sort it first by date DESC and select the first while grouping by userID

final Aggregation aggregation = newAggregation(
    Aggregation.sort(Sort.Direction.DESC, "date"),
    Aggregation.group("userId").first("date").as("Date")
);

final AggregationResults<User> results = mongoTemplate.aggregate(aggregation, "user", User.class);

edited Sep 12 '18 at 14:38

Blake

986
9
24

answered May 10 '14 at 16:47

Lealem Admassu

458
4
10

the newAggregation is a static factory method to which we pass a list of aggregation operations. – Lealem Admassu May 26 '14 at 16:44

Aggregate via Spring MongoTemplate to get max value of a collection

1 Answers1