Spring Boot JPA: how do query a JSON column in a table

Question

I have a table casemessage and has following columns. And I am trying to search/query JSON column using Spring Framework JPA..

1. id
2. created_by
3. created_utc
4. from_id
5. message
6. status
7. case_id

Here the status column stores list of JSON strings. For example:

 1. [{"user_id": 1, "status": "sent"}, {"user_id": 2, "status": "delete"}]
 2. [{"user_id": 3, "status": "delete"}, {"user_id": 2, "status": "sent"},{"user_id": 1, "status": "received"}]
 3. [{"user_id": 1, "status": "received"}, {"user_id": 2, "status": "sent"}]
 4. [{"user_id": 1, "status": "delete"}, {"user_id": 3, "status": "sent"}]

I am trying to query the casemessage table to get all the rows where user_id is 1 and status is not delete

Using MySQL query, I am able to query the table and get back expected results.

Here is the query, which I tried:

 select * from casemessage  where case_Id=1 and id not in(select id from cwot.casemessage where json_contains(status, '{"status" :"delete"}') and json_contains(status, '{"user_id" : 1}'));

When I tried this using Spring Framework JPA (Spring Boot), I got back an exception when running the application. Here is the statement that I tied:

    @Query("select c from CaseMessage c  where c.caseId=?1 and c.id not in(select cm.id from CaseMessage cm where json_contains(status, '{\"status\": \"delete\"}') and json_contains(status, '{\"user_id\": ?2}'))")
    List<CaseMessageResponse> getAllCaseMessages(long caseId, long userId);

The error that I am getting back is:

 Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 172 [select c from com.cwot.domain.CaseMessage c  where c.caseId=?1 and c.id not in(select cm.id from com.cwot.domain.CaseMessage cm where json_contains(status, '{"status": "delete"}') and json_contains(status, '{"user_id": ?1}'))]

Can some one help me with this.?

Any help is really appreciated.

Answer 1

You must use native query to use database functions like json_contains:

@Query("select c from CaseMessage c  where c.caseId=?1 and c.id not in(select cm.id from CaseMessage cm where json_contains(status, '{\"status\": \"delete\"}') and json_contains(status, '{\"user_id\": ?2}'))", nativeQuery = true)
    List<CaseMessageResponse> getAllCaseMessages(long caseId, long userId);

or with the @NativeQuery annotation

for more information :

Difference between query, native query, named query and typed query