JPA原生查询JSON列时动态指定键名与值的问题排查及解决

刚好碰到和你一样的需求场景：用JPA原生查询操作存储JSON字符串的数据库列，还要能动态指定要查询的JSON键名和对应的值，折腾了好一会儿才搞定，分享一下整个过程和最终解决方案。

场景背景 #

我有一张jsondata表，其中jsonstr列存的是JSON格式的数据，示例内容如下：

{
  "key1": "value1",
  "key2": "value2",
  "key3": "value3"
}

对应的实体类JsondataEntity用Hashtable<String, Object>映射这个JSON列，搭配自定义的HashMapConverter实现JSON字符串与Hashtable的双向转换——这么做就是为了灵活存储任意JSON结构，同时支持动态查询不同的键值对。

初始尝试：硬编码键名的查询（正常工作） #

一开始先写了个硬编码键名的测试查询，这个是能正常跑通的：

@Query(value = "SELECT j.id, j.jsonstr, JSON_UNQUOTE(j.jsonstr-> '$.key1') AS key1 FROM jsondata j " +
        "WHERE j.jsonstr-> '$.key1' = :value", nativeQuery = true)
List<Map<String, Object>> findAllByKeyValue(@Param("value") String value);

问题出现：动态传键名的查询（报错） #

但当我尝试把键名做成参数动态传入时，直接拼接参数的写法触发了报错：

@Query(value = "SELECT j.id, j.jsonstr, JSON_UNQUOTE(j.jsonstr-> '$." + ":keyName"+ "') AS :keyName FROM jsondata j WHERE j.jsonstr -> '$." + ":keyName" + "' = :value", nativeQuery = true)
List<Map<String, Object>> findAllByKeyValues(@Param("keyName") String keyName, @Param("value") String value);

报错信息如下：

2023-10-31T16:14:23.277+01:00 ERROR 15448 --- [nio-8090-exec-1] o.a.c.c.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT j.id, j.jsonstr, JSON_UNQUOTE(j.jsonstr-> '$.:keyName') AS ? FROM jsondata j WHERE j.jsonstr -> '$.:keyName' = ?] [Invalid JSON path expression. The error is around character position 10.] [n/a]; SQL [n/a]] with root cause

问题根源很清晰：JDBC的参数绑定机制会把:keyName当成占位符处理，最终生成的SQL里JSON路径变成了$.:keyName，这完全不符合MySQL的JSON路径语法，自然触发了语法错误。

最终解决方案 #

后来在Bill Karwin的提示下，我改用CONCAT动态拼接合法的JSON路径，再配合JSON_EXTRACT函数实现动态查询，修改后的语句就能正常工作了：

@Query(value = "SELECT j.id, j.jsonstr, JSON_UNQUOTE(JSON_EXTRACT(j.jsonstr, CONCAT('$.', :keyName))) AS :keyName FROM jsondata j WHERE JSON_EXTRACT(j.jsonstr, CONCAT('$.', :keyName)) = :value", nativeQuery = true)
List<Map<String, Object>> findAllByKeyValues(@Param("keyName") String keyName, @Param("value") String value);

核心思路是：用CONCAT('$.', :keyName)动态生成符合语法的JSON路径字符串，再通过JSON_EXTRACT函数提取对应的值，既避开了参数绑定破坏JSON路径的问题，又完美实现了动态指定键名的需求。

备注：内容来源于stack exchange，提问作者davidvera