title: Spring Boot JPA: how do query a JSON column in a table
body: <p>I have a table <code>casemessage</code> and has following columns. And I am trying to search/query <code>JSON</code> column using <code>Spring Framework JPA</code>..</p>
  
  <ol>
  <li>id</li>
  <li>created_by</li>
  <li>created_utc</li>
  <li>from_id</li>
  <li>message</li>
  <li>status</li>
  <li>case_id</li>
  </ol>
  
  <p>Here the <code>status</code> column stores list of <code>JSON</code> strings. For example:</p>
  
  <pre><code> 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"}]
  </code></pre>
  
  <p>I am trying to query the <code>casemessage</code> table to get all the rows where <code>user_id</code> is <code>1</code> and <code>status</code> is not <code>delete</code></p>
  
  <p>Using <code>MySQL</code> query, I am able to query the table and get back expected results. </p>
  
  <p>Here is the query, which I tried:</p>
  
  <pre><code> 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}'));
  </code></pre>
  
  <p>When I tried this using <code>Spring Framework JPA</code> (<code>Spring Boot</code>), I got back an exception when running the application. Here is the statement that I tied:</p>
  
  <pre><code>    @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&lt;CaseMessageResponse&gt; getAllCaseMessages(long caseId, long userId);
  </code></pre>
  
  <p>The error that I am getting back is:</p>
  
  <pre><code> 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}'))]
  </code></pre>
  
  <p>Can some one help me with this.?</p>
  
  <p>Any help is really appreciated. </p>