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