Distinct on multiple columns in MongoDb using Spring data Mongo aggregation API

Question

I am new to Mongo db and aggregation query. I want to perform distinct on 2 fields i.e. userId and user in mongo db collection with name "userDetails". I am able to execute below query and get the distinct documents from Mongo db using Robo 3T. But, I am trying to execute the same query using spring data MongoTemplate but getting empty result object in java. Need help to translate the below query in Java using Mongo template.

  db.userDetails.aggregate([
    {"$match" : {zip:"52034", city:"Delhi"}}, 
    // group by userId, userName to get distinct document
    {"$group" : {_id : {userId:"$userId", user:"$user"}}}, 
    // Clean up the output
    {"$project" : {_id:0, userId:"$_id.userId", user:"$_id.user"}}
])

Below is the java code for above query using Spring data MongoTemplate, but the result object is empty during execution. UserDetails model class represent the Mongo document and UserDTO is the final response that need to be returned.

MatchOperation matchOperation = Aggregation.match(
          new Criteria("zip").is(zipCode).and("city").is(cityName));
GroupOperation groupOperation = new GroupOperation(Fields.fields("userId", "user"));
ProjectionOperation projectionOperation = Aggregation.project().andExclude("_id").and("userId").nested(
        Fields.fields("_id.userId"))
        .and("user").nested(Fields.fields("_id.user"));
Aggregation  aggregation = Aggregation.newAggregation(matchOperation, groupOperation, projectionOperation);
AggregationResults<UserDTO> result = mongoTemplate.aggregate(aggregation, UserDetails.class, UserDTO.class);

Spring data mongodb - 3.0.7 Driver 4.0.4

Charchit Kapoor · Accepted Answer · 2023-09-12 13:17:56Z

0

Your query seems fine. Try this as well:

MatchOperation matchOperation = Aggregation.match(
          new Criteria("zip").is(zipCode).and("city").is(cityName));
GroupOperation groupOperation = Aggregation.group("userId", "user");
ProjectionOperation projectionOperation = Aggregation.project().andExpression("_id.userId").as("userId").andExpression("_id.user").as("user");
Aggregation  aggregation = Aggregation.newAggregation(matchOperation, groupOperation, projectionOperation);
AggregationResults<UserDTO> result = mongoTemplate.aggregate(aggregation, UserDetails.class, UserDTO.class);

Also check, what is the value of result.getUniqueMappedResult() or result.getMappedResults().

edited Sep 12, 2023 at 13:17

answered Sep 12, 2023 at 13:09

Charchit Kapoor

9,3242 gold badges11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Distinct on multiple columns in MongoDb using Spring data Mongo aggregation API

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related