JPA Query for PostgreSQL

Question

I have the below query which works when running through Postgres-

select * from my_schema.my_table where (jsonb_1 @> '"789"' or jsonb_2 @> '"789"') and (status != 'BAD_STATUS');

It checks if there's a value of "789" in jsonb_1 (list of strings) example: ["456", "789"] or "789" in jsonb_2 (list of strings) example: ["123", "456"] and with status not BAD_STATUS. I'm trying to represent the same query in Spring Data JPA (version 1.5.6.RELEASE) and tried the below two but getting exceptions.

Query1:

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> :jsonbValue or jsonb_2 @> :jsonbValue) and (status != :badStatus)", nativeQuery = true)
List<MyEntity> findAllByJsonbValueAndStatusNot(@Param("jsonbValue") String jsonbValue, @Param("badStatus") String badStatus);

Exception:

2018年10月12日 07:41:18,750 DEBUG http-nio-9030-exec-1 - [correlationId:, errorCode:] - org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions.122 - could not extract ResultSet [n/a]
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.
 Position: 60
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
 at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
 at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
 at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
 at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
 at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
 at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
 at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
 at org.hibernate.loader.Loader.doQuery(Loader.java:919)[...]

Query2:

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> ?1 or jsonb_2 @> ?1) and (status != ?2)", nativeQuery = true)
List<MyEntity> findAllByJsonbValueAndStatusNot(String jsonbValue, String badStatus);

Exception:

2018年10月12日 07:29:41,126 ERROR http-nio-9030-exec-1 - [correlationId:, errorCode:] - com.my.services.common.exception.ExceptionHandler.handleAppErrors.39 - Internal server error occurred
org.springframework.dao.InvalidDataAccessApiUsageException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.; nested exception is java.lang.IllegalArgumentException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
Caused by: java.lang.IllegalArgumentException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.
at org.springframework.util.Assert.hasText(Assert.java:181)
at [...]`

• Tim Biegeleisen: Please format your question properly.
• Jens Schauder: Please post the complete stack trace and the SQL statements that actually get executed. And the version of Spring Data JPA you are using.
• Niv: @JensSchauder, done

Answer 1

Seems like a typecasting issue. Try this

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> :jsonbValue\:\:jsonb or jsonb_2 @> :jsonbValue\:\:jsonb) and (status != :badStatus)", nativeQuery = true)

or the alternative syntax

@Query(value = "select * from my_schema.my_table where (jsonb_1 @> cast(:jsonbValue as jsonb) or jsonb_2 @> cast(:jsonbValue as jsonb)) and (status != :badStatus)", nativeQuery = true)

Answer 2

It's saying you can't use the operator @> to compare a string and a jsonb (which must be one of your column types in the DB). You'll have to cast the string as jsonb or the jsonb to string to do the comparison.

Postgres Docs on Type Casts

You can use the LIKE operator to compare strings on the cast string:

WHERE jsonb::varchar LIKE 'jsonbValue'

LIKE operator

or use the @> operator on cast jsonb:

WHERE jsonb @> :jsonbValue::jsonb

JSONb functions in postgres

EDIT:

For the second query, you are using named parameters that don't exist in your named query. Either replace ?1 with :jsonbValue or remove the @Param annotations.

• Niv: I don't think the problem is with the postgres query. I'm having issues representing the same query in JPA, the query works fine while executing directly on the table.
• Niv: my bad the second query never had the @Param annotations, copy paste error. :)