2

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.

  • It runs. What doesn't work is the transformation of the result set into a List, becase your query doesn't return all the columns that constitute an OnboardingCases. It only returns one field of a JSON document stored in one column of the table. You can't build a whole Car with only one wheel. – JB Nizet Sep 30 '19 at 06:36
  • What should i change the return type into? JSONObject?? – Ananya Tyagi Sep 30 '19 at 06:37
  • I don't know what your JSON look like. But even if I knew, I've never uses JSON support in PostgreSQL, so I don't know. – JB Nizet Sep 30 '19 at 06:39
  • I don't think it's a return type error.Since it says case_id column name does not exist.I tried many return types but it gives 1111 error then. – Ananya Tyagi Sep 30 '19 at 09:25
  • it does not exist **in the result set returned by your query**, since you're not selecting any column named that way. – JB Nizet Sep 30 '19 at 09:41
  • but a column named case_id exists :( – Ananya Tyagi Sep 30 '19 at 10:01
  • In your table, yes, it exists. But not in the result set of the query, since all your query is selecting is case_data->'business'. JPA can't possibly create an instance of OnboardingCases from the result of your query, since it would need all the columns of the corresponding table, and your query only selects case_data->'business'. – JB Nizet Sep 30 '19 at 10:12

1 Answers1

0

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.

tobsob
  • 602
  • 9
  • 22