spring data sort by map-value within a given key

Question

I would like to sort a Map of by the value. For example I have Person class which has a map of details that are stored in a map with key-value Map<String, String>.

I am using springboot with hibernate5. This is the mapping.

public class Person implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @DocumentId
    @Column(name = "personid")
    private Integer id;

    @Column(name = "name")
    private String name;

    // this is a collection of person details
    @ElementCollection()
    @MapKeyColumn(name = "detailkey")
    @Column(name = "detailvalue")
    @CollectionTable(name = "details", joinColumns = @JoinColumn(name = "personid"))
    Map<String, String> details = new HashMap<>();

    //getter and setters omitted

So far I am able to retrieve a person with some specific detailskey and specific detail value. So for example a person table in the DB has eyecolor as detail attribute and as value can have "green", "blue", "brown". Note this is not a real example, just for clarity purposes.

So for example I can get the list of persons and sort them by their name, in the controller I can do

Sort sort = new Sort(Sort.Direction.ASC, "name");

and the opposite direction

Sort sort = new Sort(Sort.Direction.DESC, "name");
Pageable pageable = new PageRequest(1, 10, sort);
pageResult = personRepository.findAll(
            "eyecolor", "green", pageable
        );

and this one will return the list of persons that have "eyecolor" as green. So far so good and this is working as expected. Now I would like to define a sorting on the detailvalue.

For example I would like to get a list of person sorted by their eyecolor. So first I should have the persons that have "blue", "brown", "green".

how can the Sort be specified in this case ?

In standard SQL it would be something like this:

SELECT p.* from persons p LEFT JOIN details d ON p.personid = d.personid AND p.detailkey='eyercolor' ORDER BY p.detailvalue ASC;

Answer 1

The following query worked for me:

SELECT p FROM Person p JOIN p.details d WHERE KEY(d) = 'eyecolor' ORDER BY d

(note that ORDER BY VALUE(d) would fail since VALUE(d) still seems to behave as described here: JPA's Map<KEY, VALUE> query by JPQL failed)

Now, I'm not particularly well versed with Spring Data, but I suppose you should be able to use the above query (without the ORDER BY part) with the @Query annotation on your PersonRepository.findAll method (I'm assuming that's a custom method) and provide the sorting using JpaSort.unsafe("d").