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.
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.