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

I have a table `casemessage` and has following columns. And I am trying to search/query `JSON` column using `Spring Framework JPA`.. - id - created_by - created_utc - from_id - message - status - 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.