Keerthi Keerthi - 6 months ago 24
JSON Question

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
..


  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 Source

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