I have a mongo collection which has many documents containing information about credits / gifts given to our users.

I would like to be able to run an Aggregated Query to get all the users who have received a credit / gift within the last x number of days and sum the total value of those credits for each user.

I think I have the correct Aggregation, group and sum code but I'm getting back more than one object per user, which shouldn't be the case since I'm grouping and then summing the values(?)

Here is what my document structure looks like:

    _id: ObjectId("61c36a8a21047124c4181271"),
    transactionId: UUID("6fbf536e-7a53-442c-9615-53e32362608b"),
    userId: 'xxxxx',
    transactionMessage: 'Account credited with: 1',
    transactionType: 'CREDIT',
    transactionAction: 'GIFT',
    inComingPaymentFromUserId: 'xxxx',
    transactionAmount: Decimal128("1"),
    customMessage: "blah'",
    createdDate: ISODate("2021-12-22T18:12:26.812Z"),
    lastUpdatedAt: ISODate("2021-12-22T18:12:26.812Z"),

And here is my query code:

    @Override
    public List<RecentlyRewardedUsers> findRecentlyRewardedUsers(Integer range, Pageable pageable) {
        Aggregation agg = Aggregation.newAggregation(
                match(Criteria.where("createdDate").gt(LocalDate.now().minusDays(range))
                        .andOperator(Criteria.where("transactionAction").is("GIFT"))), sort(Sort.Direction.DESC, "createdDate"),
                group("userId", "_id").sum("transactionAmount").as("totalValueAwarded"),
                project("userId", "totalValueAwarded"),
                skip((long) pageable.getPageNumber() * pageable.getPageSize()),
                limit(pageable.getPageSize()));
        try {
            return mongoTemplate.aggregate(agg, "Transactions", RecentlyRewardedUsers.class).getMappedResults();
        } catch (Exception ex) {
            throw new ServiceUnavailableException("Unable to perform Mongo Operation {" + ex.getLocalizedMessage() + "}", "User");
        }
    }

Here is also my mapped class:

@Getter
@Setter
public class RecentlyRewardedUsers {

    @Field("userId")
    private String userId;
    private String totalValueAwarded;
    private String userDisplayName;
    private String profilePicUrl;
}

Like I said above, when the the results of the query are mapped to the RecentlyRewardedUsers class, I see multiple entries for the same userId (see image below). I would have thought it should be all rolled up / summed up into one entry for that userId

If anyone can shed some light on what I've done wrong that would be great!!

Thank you

** EDIT ** Based on the answer below from user Eskandar Abedini, I've updated my code to be the following:

Criteria operator = new Criteria();
operator.andOperator(
        Criteria.where("createdDate").gt(LocalDate.now().minusDays(2))
                .andOperator(Criteria.where("transactionAction").is("GIFT")));

MatchOperation matchOperation = Aggregation.match(operator);


GroupOperation groupOperation = Aggregation.group("userId").sum("transactionAmount").as("totalValueAwarded");
List<String> sortOptions = new ArrayList<>();
sortOptions.add("createdDate");
Sort sort = new Sort(Sort.Direction.DESC, sortOptions);
SortOperation sortOperation = Aggregation.sort(sort);

Aggregation aggregation = Aggregation.newAggregation(Transaction.class, matchOperation, groupOperation, sortOperation);

AggregationResults<RecentlyRewardedUsers> aggregationResults = mongoTemplate.aggregate(aggregation, Transaction.class, RecentlyRewardedUsers.class);

When I now execute the above I get this error:

java.lang.IllegalArgumentException: Invalid reference 'createdDate'!

If for the moment I remove the sort options. The code will execute. However I'm not getting the userId mapped into my RecentlyRewarded class.

Criteria operator = new Criteria();
operator.andOperator(
        Criteria.where("createdDate").gt(LocalDate.now().minusDays(2))
                .andOperator(Criteria.where("transactionAction").is("GIFT")));

MatchOperation matchOperation = Aggregation.match(operator);


GroupOperation groupOperation = Aggregation.group("userId").sum("transactionAmount").as("totalValueAwarded");
Aggregation aggregation = Aggregation.newAggregation(Transaction.class, matchOperation, groupOperation);

AggregationResults<RecentlyRewardedUsers> aggregationResults = mongoTemplate.aggregate(aggregation, Transaction.class, RecentlyRewardedUsers.class);

Any help would be amazing.

Thank you

Edit Adding image to show what I mean by userId not being mapped in the MappedResponse. However in the Raw response of the query the userId is getting mapped to the documentId _id

Remove _id from group("userId", "_id")

This is somehow similar to yours, its implementation is clear too.

public BaseResponse getErrorLogGroupByAppName() throws BaseException {

    Criteria operator = new Criteria();
    operator.andOperator(
            Criteria.where("execDate").is(DateUtil.today())
    );

    MatchOperation matchOperation = Aggregation.match(operator);


    GroupOperation groupOperation = Aggregation.group("serUrl", "serPort", "appName", "appSer", "reqUrl", "execDate").count().as("count");

    Sort sort = new Sort(new Sort.Order(Sort.Direction.DESC, "count"));
    SortOperation sortOperation = Aggregation.sort(sort);

    Aggregation aggregation = Aggregation.newAggregation(ErrorInfo.class, matchOperation, groupOperation, sortOperation);

    AggregationResults<Map> aggregationResults = template.aggregate(aggregation, ErrorInfo.class, Map.class);

    return new BaseResponse(aggregationResults.getMappedResults());
}

GitHub Source