8

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
Keerthi
  • 83
  • 1
  • 1
  • 5
  • Possible duplicate of [check if a value exists in json encode array in mysql](http://stackoverflow.com/questions/41132714/check-if-a-value-exists-in-json-encode-array-in-mysql) – e4c5 May 11 '17 at 13:39
  • @e4c5, I am looking at the JPA point of view. My query works in MySQL – Keerthi May 11 '17 at 13:41
  • that does not make any difference. This is a situation that's not suited for JSON – e4c5 May 11 '17 at 13:43
  • I have a doubt. What if I need to check if there is any key called status, then how to check that? – Chetan Oswal Jul 20 '20 at 07:28

1 Answers1

10

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

Community
  • 1
  • 1
Laurent Grousset
  • 393
  • 3
  • 6
  • When I ran using this statement, I get back an error saying `org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that position [2] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that position [2] did not exist` – Keerthi May 12 '17 at 06:25
  • you must modify your query, it must become a native one – Laurent Grousset May 12 '17 at 07:50
  • 1
    What if I don't want to use native query? Is there any solution? – Chetan Oswal Jul 29 '20 at 08:03
  • Hi @LaurentGrousset... What if I want to pass a parameter here - `json_contains(status, '{\"status\": \"delete\"}')`? Instead of static string **delete**, I want some dynamic string? Is that possible? – Chetan Oswal Feb 17 '22 at 14:15