Search code examples
postgresqlspring-data-jpa

Query a postgres jsonb using spring jpa @query annotation

I have create an hibernate userTypeData and succesfully mapped my object (called "data") to jsonb data type in postgres table. now, im trying to use a JpaRepository to query that jsonb column but without success. this is the code i use for the query: 

public interface GenieEntityDao extends JpaRepository<GenieEntity, Long>, QueryByExampleExecutor<GenieEntity> {

  @Query(value = "SELECT e FROM from genie_entities e WHERE e.data ->> 'temp' = '30'", nativeQuery = true)
    public List<GenieEntity> findByTempBiggerThan(String temp);

}

this is the exception i got:

org.springframework.dao.InvalidDataAccessApiUsageException: Unknown parameter position: 1; nested exception is java.lang.IllegalArgumentException: Unknown parameter position: 1

anybody knows how to query the jsonb column using @Query annotation?

Solution

  • Answer: the query syntax for postgres jsonb column can go like this ->

    @Query(value = "SELECT * FROM genie_entities WHERE data ->> ?1 > ?2", nativeQuery = true)
public List<GenieEntity> findByDataFilterBigger(String key, String value );

    It works. :)