mysql - Spring 启动 JPA : how do query a JSON column in a table

Question

我有一个表 casemessage 并且有以下列。我正在尝试使用 Spring Framework JPA 搜索/查询 JSON 列..

1. 编号
2. 创建者
3. created_utc
4. 来自_id
5. 留言
6. 状态
7. 案例编号

status 列存储了 JSON 字符串列表。例如:

 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"}]

我正在尝试查询 casemessage 表以获取 user_id 为 1 和 status 的所有行不是删除

使用 MySQL 查询，我能够查询表并得到预期的结果。

这是我试过的查询:

 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}'));

当我尝试使用 Spring Framework JPA (Spring Boot) 时，我在运行应用程序时返回了一个异常。这是我绑定(bind)的声明:

    @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);

我返回的错误是:

 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}'))]

有人可以帮我解决这个问题吗？

非常感谢任何帮助。

Answer 1

您必须使用 native 查询才能使用 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);

或使用@NativeQuery 注解

更多信息:

Difference between query, native query, named query and typed query