Spring Boot JPA:如何查询表中的JSON列 [英] Spring Boot JPA: how do query a JSON column in a table

问题描述

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

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. 消息
6. 状态
7. case_id

此处status列存储JSON字符串列表.例如:

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

我试图查询casemessage表以获取user_id是1而status不是delete

I am trying to query the casemessage table to get all the rows where user_id is 1 and status is not delete

使用MySQL查询，我可以查询表并获取预期结果.

Using MySQL query, I am able to query the table and get back expected results.

这是我尝试的查询:

 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)尝试此操作时，在运行应用程序时出现异常.这是我绑的声明:

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

我回来的错误是:

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

有人可以帮我吗??

我们非常感谢您的帮助.

Any help is really appreciated.

推荐答案

您必须使用本机查询才能使用json_contains之类的数据库功能:

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

或带有@NativeQuery批注

or with the @NativeQuery annotation

更多信息:

查询，本机查询，命名查询之间的差异并输入查询

这篇关于Spring Boot JPA:如何查询表中的JSON列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！