CODEWITHSUNDEEP
postgresqlspring-data-jpa
TommyW
TommyW

Reputation: 570

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?

Upvotes: 4

Views: 6132

Answers (2)

marcus
marcus

Reputation: 11

Answer didn't work for me so i wrote the one below. Instead of numerics, I use the @Param annotation and referenced the values with it.

@Query(value = "SELECT * FROM genie_entities WHERE metadata ->> :key = :value", nativeQuery = true)  
Optional<GenieEntity> findByMetadataValue(@Param("key") String key, @Param("value") String value);

Upvotes: 0

TommyW
TommyW

Reputation: 570

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

Upvotes: 4

Related Questions