Query a postgres jsonb using spring jpa @query annotation

Question

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?

If you have a spring-data method parameter, you should use a parameter placeholder, inside the query string (:temp, or ?). — pozs
– pozs, Commented Mar 8, 2017 at 9:40

TommyW · Accepted Answer · 2017-03-09 07:04:54Z

4

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

answered Mar 9, 2017 at 7:04

TommyW

5701 gold badge6 silver badges22 bronze badges

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

Comments

marcus · Accepted Answer · 2023-10-12 13:24:37Z

0

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);

answered Oct 12, 2023 at 13:24

marcus

115 bronze badges

Collectives™ on Stack Overflow

Query a postgres jsonb using spring jpa @query annotation

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related