CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate
CCoder
CCoder

Reputation: 161

Aggregate & Sort by Inner Array in Spring Data MongoDB

I'm pretty new to mongoDB. Please bear with me.

I have a collection called Users which contains list of Roles. A user can have multiple roles, therefore roles are listed in an array. I want to sort the users by their role names.

User structure looks like this,

{
  "_id": ObjectId("5bc910a39e53b62c7d4c4e62"),
  "_class": "User",
  "userName": "John",
  "fullName": "Doe",
  "roles": [
    DBRef("roles",
    ObjectId("5d5cf8ceb3773255b54d18c6")),
    DBRef("roles",
    ObjectId("5d5cf8ceb3773255b54d18c7"))
  ]
}

Classes

@Document(collection = "users")
public class User {

    @Id
    private String id;
    private String username;
    private String fullName;
    private boolean active;
    @DBRef
    private List<Role> roles;
  //constructor, getter, setter
}



@Document(collection = "roles")
public class Role {
    @Id
    private String id;
    private String name;

//constructor, getter, setter

}

I have tried the following,

 Criteria criteria = new Criteria();
 setCriteriaReadOnlyIsNullOrReadOnlyIsFalse(criteria);
 criteria.andOperator(Criteria.where("<condition>").is(<"condition_data">));

 AggregationOperation userMatch = Aggregation.match(criteria);
 LookupOperation lookupOperation = LookupOperation.newLookup()
                .from("roles")
                .localField("roles.id")
                .foreignField("id")
                .as("rolesAgg");
 AggregationOperation sort = Aggregation.sort(Sort.Direction.DESC, "rolesAgg.name");
 AggregationOperation project = Aggregation.project("id", "userName", "fullName","roles");


TypedAggregation<User> aggregation = newAggregation(User.class, userMatch, lookupOperation, sort, project);

return mongoOperations.aggregate(aggregation, User.class).getMappedResults();

This produces results, but unable to sort since the rolesAgg is a array of objects. This is how the rolesAgg displays per user.

"rolesAgg": [
  {
    "_id": ObjectId("5d5cf8ceb3773255b54d18c3"),
    "name": "Super Admin"
  },
  {
    "_id": ObjectId("5d5cf8ceb3773255b54d18c5"),
    "name": "Customer Service"
  },
  {
    "_id": ObjectId("5d5cf8ceb3773255b54d18c4"),
    "name": "Admin"
  }
]

Is there a way to extract the rolesAgg.name into an array and they sort using that? I'm stuck. Thanks in advance for helping me.

Upvotes: 2

Views: 1284

Answers (1)

varman
varman

Reputation: 8894

You can't sort when objects are inside the array.

[
  {
    "$unwind": "$rolesAgg"
  },
  {
    "$sort": {
      "rolesAgg.name": -1
    }
  },
  {
    $group: {
      _id: "$_id",
      username: {
        "$first": "$username"
      },
      "fullname": {
        "$first": "$fullname"
      },
      rolesAgg: {
        $push: "$rolesAgg"
      }
    }
  }
]

So you need to do

To flat the array

Aggregation.unwind("$rolesAgg")

To sort by name

Aggregation.sort(Sort.Direction.DESC, "rolesAgg.name")

To group all back

Aggregation.group("_id)
    .first("username").as("username")
    .first("fullname").as("fullname")
    .push("rolesAgg").as("rolesAgg")

respectively.

Note : If you have more fields while grouping, consider it also

Working Mongo playground

Upvotes: 3

Related Questions