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")); )

if there are multiple documents with the same `operationId` which one do you want to return? — herman, Jun 23 '20 at 16:03
So, you want to group them by `operationId`, sort each group descending by `amount`, then get the first of each group. — herman, Jun 23 '20 at 16:07
I recommend creating the appropriate query in mongodb shell (or equivalent tool) first and then translating that to Spring Data Mongo. — herman, Jun 23 '20 at 16:08
What do you need in the resulting `OperationDataVO`? Only `amount` and `operationId` ? And everything sorted ascending by `amount`? — herman, Jun 23 '20 at 16:41
Yes, I only need to have these two fields inside OperationDataVO — , Jun 23 '20 at 16:46

herman · Accepted Answer · 2020-06-23T20:43:00.547

0

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).

edited Jun 23 '20 at 20:43

answered Jun 23 '20 at 18:55

herman

11,740
5
47
58

Thank you. Using this query in "Studio 3T for MongoDB" though, returns only the OperationDataVO, derived by the Operation object with the highest "amount" value – Jun 23 '20 at 19:27
It was the $ missing from the "operationId" in group – Jun 23 '20 at 20:41
Indeed I just adapted it. – herman Jun 23 '20 at 20:44
Does "g" stand for something specific? – Jun 24 '20 at 07:43
1

@MilanPopescu I chose it as abbreviation for "group" but it's just the name for a field that won't end up in the final document, so you can choose anything (but adapt both lines where it's used of course). – herman Jun 24 '20 at 07:45

Improve aggregation query to get distinct results

1 Answers1