Aggregate & Sort by Inner Array in Spring Data MongoDB

Question

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.

varman · Accepted Answer · 2020-08-14 12:22:01Z

3

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

answered Aug 14, 2020 at 12:22

varman

8,9226 gold badges25 silver badges56 bronze badges

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

4 Comments

CCoder Over a year ago

Hi varman, thank you for the detail explanation. It's sorts the roles inside the array now. But not the user. Is it also possible to sort the user based on the sorted role names?

varman Over a year ago

Yes you can, where does role name come from?

CCoder Over a year ago

rolesAgg.name is the role name. But sorting it doesn't sort the user. mongoplayground.net/p/H9kyJ0pyxE_

varman Over a year ago

try this mongoplayground.net/p/5XJL22KwLu7 U sorted the array and grouped, Username or full name is not in array, so you can do normal sorting

Collectives™ on Stack Overflow

Aggregate & Sort by Inner Array in Spring Data MongoDB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related