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.

Evergreen

Possible duplicate of check if a value exists in json encode array in mysql – Saurel

@e4c5, I am looking at the JPA point of view. My query works in MySQL – Evergreen

that does not make any difference. This is a situation that's not suited for JSON – Saurel

I have a doubt. What if I need to check if there is any key called status, then how to check that? – Tropic

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

Bucovina

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 – Evergreen

you must modify your query, it must become a native one – Bucovina

What if I don't want to use native query? Is there any solution? – Tropic

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? – Tropic

Answer 2

One way I was able to solve this was by using JSON_CONTAINS(json, JSON_QUOTE(?1)) along with nativeQuery=true

That way, I was able to add the additional double quotes required for querying

Fumarole