How to query with Spring JPA on jsonb columns?

Question

I'm using spring JPA with PostgreSQL database. I have an Entity as follow:

@Entity
@TypeDef(name="json_binary", typeClass = com.vladmihalcea.hibernate.type.json.JsonBinaryType.class)
public class LiveTokens {

   @Id
   @GeneratedValue()
   private Long id;

   private String username;

   @Type(type="json_binary")
   @Column(columnDefinition = "jsonb")
   private Token token
}

and Token:

public class Token {
   private Long expireTime;
   private String accessToken;
}

For saving object to column with Hibernate, I use Hibernate Types project. Now I want to get All LiveTokens that expired. I can't do it with Spring JPA. How do I query to posgresql jsonb column with Spring data JPA?

Vlad Mihalcea · Answer 1 · 2021-01-10T13:13:42.797

10

SQL JSON functions

If you want to call a SQL function that processes the JSON column, then you just need to make sure you register the function with Hibernate prior to using it.

JSON operators

For JSON operators, like the PostgreSQL ones (e.g., ->>), the only option you have is to use a native SQL query. JPQL or HQL don't support database-specific JSON operators.

edited Jan 10 '21 at 13:13

answered Sep 14 '19 at 05:24

Vlad Mihalcea

142,745
71
566
911

Georgios Syngouroglou · Answer 2 · 2019-09-17T09:47:36.387

Using EclipseLink and spring data jpa, if your data in db is something like: {"expireTime":102020230201, "accessToken":"SOMETHING" }, my first question is why to use long numbers for your dates instead of timestamps (ex '2019-09-14 12:05:00'). If you use timestamps there are also options to manage timezones (either from postgresql or from you source code).
Regarding your issue you may use the FUNC JPQL keyword of EclipseLink (Hibernate may have something similar) in order to run a database specific function. In the example below I use FUNC('jsonb_extract_path_text', lt.token, 'expireTime') to get the values of the json for token.expireTime.
PostgreSql method jsonb_extract_path_text returns text, thus you cannot do a less that condition, so I cast the output of the function using JPQL CAST keyword with (CAST -data- TO -type-).

@Repository
public interface MyRepository extends JpaRepository<LiveTokens, Integer> {

    @Query(value = "SELECT lt FROM LiveTokens lt WHERE CAST(FUNC('jsonb_extract_path_text', lt.token, 'expireTime') AS LongType) < :expirirationThreshold")
    List<LiveTokens> findByExpireTime(@Param("expirirationThreshold") Long expirirationThreshold);
}

Again, this is not tested.

this error `Could not resolve requested type for CAST : int8[select lt from ...]` — Morteza Malvandi, Sep 15 '19 at 04:02
The error you get is about the `INT8` type the `CAST` uses. You may replace the INT8 with a different type. You may also try LongType or Long. — Georgios Syngouroglou, Sep 17 '19 at 09:53

score 1 · Answer 3 · edited Jul 11 '23 at 11:00

This is how a native query using Postgres JSON query operators would look like, incorporating your example:

@Query(value="SELECT t.* FROM LiveTokens t WHERE CAST(t.token ->> 'expireTime' AS LONG) < now()", native=true)

Assuming your real table name is LiveTokens, native queries no longer use the JPA translations, and the table name has to match the one in the DB. (You may also need to specify its schema.)

score -3 · Answer 4 · edited Sep 12 '19 at 08:50

-3

Try it:

Service class:

Long currentTime = new Date().getTime();

Repository:

@Query("SELECT lt FROM LiveTokens lt WHERE lt.token.expireTime <= :currentTime")

List<LiveTokens> findExpiredLiveTokens(@Param("currentTime") long currentTime)

edited Sep 12 '19 at 08:50

answered Sep 12 '19 at 08:49

David

78
3

How to query with Spring JPA on jsonb columns?

4 Answers4

SQL JSON functions

JSON operators

Linked