Spring Boot JPA：如何在表中查询JSON列

Question

我有一张名为casemessage的表格，其中包含以下列。我正在尝试使用Spring Framework JPA查询/搜索JSON列。

1. id（编号）
2. created_by（创建者）
3. created_utc（创建日期）
4. from_id（来源编号）
5. message（消息）
6. status（状态）
7. case_id（案例编号）

在这里，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不是delete的行。

使用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）执行此操作时，运行应用程序时出现了异常。这是我尝试的语句：

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

有人可以帮我吗？

非常感谢任何帮助。

- Keerthi

---

可能是在MySQL中检查JSON编码数组中是否存在值的重复问题。 - e4c5

@e4c5，我正在从JPA的角度来看待这个问题。我的查询在MySQL中可以正常工作。 - Keerthi

这并没有任何区别。这是一个不适合使用JSON的情况。 - e4c5

我有一个疑问。如果我需要检查是否有名为“状态”的键，该如何检查？ - Chetan Oswal

Answer 1

您必须使用本机查询才能使用数据库函数（如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注释

了解更多信息：

查询，本地查询，命名查询和类型化查询之间的区别

- Laurent Grousset

---

当我使用这个语句运行时，我收到一个错误，说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

你需要修改你的查询，使其成为本地化的查询。 - Laurent Grousset

如果我不想使用本地查询，有什么解决方案吗？ - Chetan Oswal

嗨@LaurentGrousset... 如果我想在这里传递一个参数 - json_contains(status, '{\"status\": \"delete\"}')，而不是静态字符串delete，我想要一些动态字符串，这是否可能？ - Chetan Oswal