Spring Data JPA: find by json property

user_1265205 0
Viewed 1799

I'm working with Spring Data JPA and one of the properties of my model is a jsonb column in PostgreSQL. This is how it's mapped:

@Type(type = "catalog.utils.model.JsonbType")
@Column(name = "marc", columnDefinition = "jsonb")
private Marc marc;

This object is saved, retrieved and updated just fine. Then in my repository I made a method to retrieve by a certain json property. This query works fine in PostgreSQL:

public interface AuthorityRepository  extends JpaRepository<Authority, Integer>, JpaSpecificationExecutor<Authority> {

    @Query(
            value = "SELECT * FROM my_table where marc -> 'controlNumber' = :controlNumber",
            nativeQuery = true
    )
    List<Authority> findAuthoritiesByControlNumber(@Param("controlNumber") String controlNumber);
}

When I try to use this method of my repository I get this error: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

In the console, I see the query executed just as I described and this error message, which I find confusing since the same query works fine when executed directly in PostgreSQL.

ERROR SqlExceptionHelper ERROR: operator does not exist: jsonb = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

By these pieces of code is it possible to identify what I'm doing wrong?

spring-data-jpa java
2 Answers

I found the fix. This is my repository :

public interface AuthorityRepository  extends JpaRepository<Authority, Integer>, JpaSpecificationExecutor<Authority> {

    @Query(
            value = "SELECT * FROM my_tables where marc @> CAST(:jsonString as jsonb)",
            nativeQuery = true
    )
    List<Authority> findAuthoritiesByJsonProperty(@Param("jsonString")  String jsonString);
}

And this is how I query it:

public List<Authority> getAuthoritiesByControlNumber(String controlNumber) {
    return this.repo.findAuthoritiesByJsonProperty("{\"controlNumber\": \""+ controlNumber +"\"}");
}
user_12652050

For me a similar query worked after replacing -> with ->>

So in your case it would be:

@Query(
        value = "SELECT * FROM my_table where marc ->> 'controlNumber' = :controlNumber",
        nativeQuery = true
)
List<Authority> findAuthoritiesByControlNumber(@Param("controlNumber") String controlNumber);
}

This was my query:

@Query(nativeQuery = true, value = "SELECT * FROM myschema.mytable WHERE myjsonbcolomn ->> 'order_number' = :orderNumber")
  List<InvoiceDao> findByOrderNumber(@Param("orderNumber") String orderNumber);
user_123201090
Related