Improve aggregation query to get distinct results

Question

I have this aggregation query that returns Operation objects with field amount:BigDecimal higher than minAmount and within a date range.I would like to get only distinct results (each Operation object has an operationId:String), based on operationId.

I have found a relevant example here, but it has not help me get through my issue: Get sorted distinct values with MongoTemplate

I understand that addToSet, or group can be used, but I am unclear on how exactly to incorporate it within the rest of the query

    private List<OperationDataVO> getInfoFromDB(BigDecimal minAmount,
                                                     Instant startDate, Instant endDate) {
        Criteria criterias = new Criteria()
            .andOperator(Criteria.where(WinningOperation.AMOUNT)
                    .gte(minAmount)
                    .and(Operation.TYPE).is(OperationTypeEnum.WINNING_TYPE)
                    .and("createdAt").gte(startDate).lte(endDate));

        MatchOperation matchOperation = Aggregation.match(criterias);

        ProjectionOperation projectionOperation = 
                Aggregation.project("amount", "operationId");

        Aggregation aggregation = Aggregation.newAggregation(matchOperation,
                projectionOperation, sort(direction, "amount"));

        AggregationResults<OperationDataVO> aggregate = mongoTemplate
                .aggregate(aggregation, COLLECTION, OperationDataVO.class);

        return aggregate.getMappedResults();
    }

Also, I have tried adding a group operation in the Aggregation pipeline, but when I do so, I get a list of OperationDataVOwhere both fields of every object are null

(Aggregation aggregation = Aggregation.newAggregation(matchOperation, projectionOperation, sort(direction, "amount"), group("operationId")); )

Answer 1

You need to sort descending by amount before doing the grouping. Grouping should be done using the '$first' accumulator. We retain the whole document using $$ROOT. You can then replace the root document with the document from the group.

Grouping doesn't preserve any order, since you want to have the end result sorted you need to sort again.

The mongo shell code to achieve this will look like this:

db.getCollection('operationData').aggregate([
{ $match: ... } ,
{ $project: { amount: 1, operationId: 1 } },
{ $sort: { amount: -1 } },
{ $group: { _id: '$operationId', g: { $first: {data: '$$ROOT'} }} },
{ $replaceRoot: { newRoot: '$g.data' }},
{ $sort: { amount: 1 } }
])

This will need to be translated to Spring Data Mongo (maybe I'll have time later to try this myself).