Home > other >  Distinct query but selecting multiple fields on Spring MongoDB
Distinct query but selecting multiple fields on Spring MongoDB

Time:02-01

I have a User model on a Mongo collection that looks like this:

User.class

@Id
private String userId;
private String userName;
private String organizationId;
private String organizationName;
private String status

I need to get a list of OrganizationDTO to send as a response to the frontend. The DTO is shaped as below:

OrganizationDTO.class

private String organizationId;
private String organizationName;

But in the collection is obviously possible that different Users have the same organizationId and organizationName, so I want to list them only once. The list of OrganizationDTO should contain every distinct organizationId/Name that has a status included in a set I choose.

I'll be glad to add everything that could be helpful if needed.

I tried using mongoTemplate.findDistinct() but it clearly isn't the solution I'm looking for. The over-complicated "solution" I found is this:

Query orgIdListQuery = new Query().addCriteria(
  Criteria.where("status").in(statusList)
);

List<String> organizationIdList = mongoTemplate.findDistinct(orgIdListQuery, "organizationId", User.class, String.class);

List<String> organizationNameList = mongoTemplate.findDistinct(orgIdListQuery, "organizationName", User.class, String.class);

// Map the two lists to get a single one with both fields, obtaining a list of OrganizationDTO

but I didn't like it at all, so I tried with aggregations:

MatchOperation match = new MatchOperation(getCriteria(statusList));  //getCriteria returns the Criteria as above
GroupOperation group = new GroupOperation(Fields.fields("organizationId", "organizationName"));
Aggregation aggregation = Aggregation.newAggregation(match, group);
AggregationResults<OrganizationDTO> results = mongoTemplate.aggregate(aggregation, User.class, OrganizationDTO.class);
return results.getMappedResults();

It seems that I'm near the right implementation, but at the moment the result is a List with some empty objects. Is this the right solution once the aggregation is correct? Or maybe there's something better?

CodePudding user response:

I think the problem can be the result can't be serialized into your OrganizationDTO so you can try to add a $project stage into aggregation, something like this (not tested):

ProjectionOperation project = Aggregation.project().andExclude("_id").and("organizationId").nested(Fields.fields("_id.organizationId")).and("organizationName").nested(Fields.fields("_id.organizationName"));
Aggregation aggregation = Aggregation.newAggregation(match, group, project);

And now the result is like the DTO and can be mapped so now should not be empty objects.

  • Related