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;

that is how postgresql gets value with key from jsonb type. for example: "extra" is jsonb type and the value is {"info": "jsoncontext"}. "extra ->> 'info'" can return "jsoncontext". — Tttttsing
– Tttttsing, Commented Oct 22, 2019 at 5:39
@MuhammadWaqasDilawar you can create a function used to extract value from json. and call the function instead of using ->> — Tttttsing
– Tttttsing, Commented Aug 7, 2020 at 8:03

androberz · Accepted Answer · 2021-02-11 09:41:01Z

2

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.

answered Feb 11, 2021 at 9:41

androberz

95414 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related