Spring JPA Sorting and Paging with PostgreSQL JSONB

Question

I am using Spring JPA in order to manage my PostgreSQL data. This data makes heavy use of the jsonb data type in PostgreSQL 9.4.

My table (called jobtable), simplified looks something like this:

id, bigint | data, jsonb
--------------------------------
1          | {"name": "Hello"}
2          | {"name": "Testing"}

Using Spring JPA, I am defining a CrudRepository interface in order to make some queries for this table. For jsonb specific things, I am using nativeQuery = true so that I can make use of this PostgreSQL type.

For example, I can query my property like so:

@Query(
 value = "select * from jobtable where data ->> 'name' = ?1",
 nativeQuery = true)
JobEntity getJobByName(String name);

This query works just fine.

My problem occurs when I try to use Pagination with native queries using jsonb. My query is as such:

@Query(
 value = "select * from jobtable \n#pageable\n", 
 countQuery = "select count(*) from jobtable", 
 nativeQuery = true)
Page<JobEntity> getJobList(Pageable pageable);

I include the Pageable parameter and call the function as such:

Pageable pageable = new PageRequest(0, 10, Direction.DESC, "data ->> 'name'");
Page<JobEntity> results = myDao.getJobList(pageable);

This code does not work, and produces the following error:

org.springframework.dao.InvalidDataAccessApiUsageException: 
 Sort expression 'data ->> 'name': DESC' must only contain property references or aliases used in the select clause. If you really want to use something other than that for sorting, please use JpaSort.unsafe(…)!

I am not sure what to make of this error. I think it has to do with improperly understanding my sortBy parameter in the PageRequest object, but I am not sure how else to structure that PageRequest object when I am intending to sort on a key within my jsonb object.

I can construct Raw SQL to PostgreSQL that looks something like select * from job order by data ->> 'jobId' desc limit 10 but I would rather use the Pageable interface with Spring JPA so that I can use the @Query notation and not have to define anything explicitly in code myself.

Answer 1

Try to create the Pageable as follows:

Pageable p = PageRequest.of(1,10,
            JpaSort.unsafe(Direction.DESC, "data->>'name'"));

This should get rid of the exception.

Answer 2

For those finding this question where you are using the org.springframework.data.jpa.domain.Specification here is how I was able to get it to work inside of the toPredicate method

Expression exp = criteriaBuilder.function("jsonb_extract_path_text", 
    String.class, 
    root.get("data"), 
    criteriaBuilder.literal("name"));

switch (direction){
    case ASC:
        criteriaQuery.orderBy(criteriaBuilder.asc(exp));
        break;
    case DESC:
    default:
        criteriaQuery.orderBy(criteriaBuilder.desc(exp));
        break;

}