How to get distinct Items by one field in MongoDB using @Query annotation with pagination enabled

Question

I'm trying to get the list of distinct items by a specific field (userId). Currently I'm using this kind of approach to get records from MongoDB using ReactiveCrudRepository. Additionally, I want this result to be further filtered and get only the distinct items.

How to do this?

@Query(value = "{$and :[{'submitTime':{$ne:null}}, {'gameId': :#{#gameId}} ]}", sort = "{'score': -1, 'timeTaken': 1, 'submitTime': 1}")
Flux<Play> getWinners(@Param("gameId") String gameId, Pageable pageable);

My Play object is like this:

@Document(value = "play")
@Builder
public class Play {
    @Id
    private String id;
    private String gameId;
    private int score;
    private String userId;
    private LocalDateTime submitTime;
    private long timeTaken;
}

score 1 · Answer 1 · answered Dec 02 '21 at 15:51

At the moment @Query annotation doesn't provide a possibility to execute the distinct command. As a workaround you can

implement a custom method with find, sort and distinct actions
use .distinct() operator after calling your repo method getWinners() to distinct items by key selector, but in this case this operation will not be performed in MongoDB

How to get distinct Items by one field in MongoDB using @Query annotation with pagination enabled

1 Answers1