CODEWITHSUNDEEP
javaspringpostgresqlspring-data-jpajsonb
B B
B B

Reputation: 149

Injecting JSON parameter in nativeQuery

This works when 

@Query(
  value = "SELECT * FROM person WHERE school = :schoolId AND details @> '{\"id\":\"1234\",\"name\":\"John\"}'", 
  nativeQuery = true
)

I am passing @Param("schoolId") String schoolId

But when I pass the JSON as a param, it fails with 

org.springframework.dao.InvalidDataAccessResourceUsageException, could not extract ResultSet; SQL [n/a]; nested exception is 
org.hibernate.exception.SQLGrammarException: could not extract ResultSet

org.postgresql.util.PSQLException: ERROR: operator does not exist: jsonb @> character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

@Query(value = "SELECT * FROM person WHERE school = :schoolId AND details @> :details", nativeQuery = true)

@Param("schoolId") String schoolId, @Param("details") String details

Upvotes: 2

Views: 3352

Answers (2)

renanreismartins
renanreismartins

Reputation: 309

Thanks for this.

Just in addition to the solution, you can also do the cast as:

details @> :details::jsonb

I'm using version 11 of postgres, not sure when it was introduced.

Upvotes: 1

pozs
pozs

Reputation: 36214

Spring+JDBC binds Strings as VARCHAR by default. The cheap solution here is to use cast(s):

details @> CAST(:details AS jsonb)

But, if you have a lot of queries, in which some non-standard types are used as parameters & you want to bind their String representation, you can use the

stringtype=unspecified

JDBC DSN parameter in your connection string. That way, every parameter, which is bound by setString() will have an unknown type in PostgreSQL, thus it will try to infer their actual types.

Upvotes: 5

Related Questions