Search code examples
javaspringpostgresqlspring-data-jpajsonb

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
Solution

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