how to spring Jpa.unsafe to sort a json field in postgresql

Question

In spring jpa doc, the example shows a way to sort by a sql function like Length(field).

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

  @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
  List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
}

repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)")); 

So I try to sort a json field in postgres, the code is like

@Query("select u from User u where u.loginName like ?1%")
List<User> findByAndSort(String loginName, Sort sort);

repo.findAllAndSort("jack", JpaSort.unsafe("extra ->> 'info'"));

extra is the name of the field in postgres and it is a jsonb type.

Unluckily, it returns error :

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: > near line 1, column 88 [select u from com.xx.user.model.User u where u.loginName like ?1 order by u.extra ->> 'info' asc]

it seems that error happens in process of hibernate.

following sql works, so I want to generate it through jpa.

SELECT * from tbl_user order by extra ->> 'info' desc;

Answer 1

I will put my solution here.

Hibernate just doesn't understand that the ->> is json/jsonb operator. We need to put the method name bound to the operator. We could get it in PSQL by the request:

select * from pg_operator where oprname = '->>'

For my PSQL 9.4, it shows that the method name is the json_array_element_text in the oprcode column.

So I just can replace it with the following:

JpaSort.unsafe("json_array_element_text(extra, 'info')")

It does work for me.