How to execute a postgresql json function query in jpa nativequery?

Question

Here i am trying to fetch an object named business from my jsonb column case_data where case_id is set through parameter.

@Query(value="Select case_data->'business' from onboarding_cases where case_id=?1",nativeQuery=true)
 List<OnboardingCases> findByCaseAttrib(BigInteger caseId);

But it gives error: ERROR 26044 --- [nio-8091-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : The column name case_id was not found in this ResultSet.

Even though this column exists in the table.I tried using simple query but it also gives error.

   @Query("Select caseData->'business' from OnboardingCases where caseId=?1")
List<OnboardingCases> findByCaseAttrib(BigInteger caseId);

it gives error:Unexpected token at >

The above query runs perfectly on pgAdmin4 but not in jpa.

Answer 1

Finally did it. Answering to help anyone seeking answer to this. Changed the return type to List<String>.

@Query(value = "Select case_data->'business' from onboarding.onboarding_cases where case_id=?1", nativeQuery = true)
List<String> findByCaseAttrib(BigInteger case_id);

Had to add a special dependency so that jsonb data type can be mapped to hibernate. Since hibernate has no such datatype by default.

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.5.0</version>
</dependency>

Special thanks to vladmihalcea for writing a jar for mapping jsonb datatypes in hibernate. For more data see here.