H2数据库JSON列字段引用的正确用法及问题排查

问题根源 #

你尝试的(my_json).myFieldName语法确实是H2官方文档标注的JSON字段提取方式，但返回NULL是因为两个潜在细节问题：

1. H2对JSON字段的大小写匹配严格，若插入的JSON字段名与查询时的写法存在大小写差异（比如H2自动转换了字段名大小写），会导致无法匹配；
2. 直接插入JSON字符串字面量时，部分H2版本可能未将其正确解析为JSON对象类型，导致字段引用语法失效。

最优解决方案：使用原生JSON_VALUE函数 #

H2 2.x版本（包括你使用的2.2.220）已经支持和MySQL一致的JSON_VALUE函数，这是最直观且兼容其他数据库的写法，完全满足你的需求：

select id, my_json, JSON_VALUE(my_json, '$.myFieldName') as baz from my_table;

执行该语句会直接返回预期的foo bar结果，无需额外处理。

修正字段引用语法的使用方式 #

如果坚持使用文档中的(expression).fieldName语法，可按以下方式调整：

1. 插入数据时使用JSON_OBJECT构造JSON，避免字符串字面量的解析问题：

insert into my_table (my_json) values (JSON_OBJECT('myFieldName', 'foo bar'));

2. 查询时确保字段名大小写完全匹配，若字段名包含特殊字符或关键字，需用双引号包裹：

select id, my_json, (my_json)."myFieldName" as baz from my_table;

简化替代方案（无需正则） #

若不想用上述方法，可利用JSON_TO_STRING结合字符串截取实现，比正则表达式更简洁：

select id, my_json, 
       SUBSTRING(
           JSON_TO_STRING(my_json), 
           LOCATE('"myFieldName":"', JSON_TO_STRING(my_json)) + 15,
           LOCATE('"', JSON_TO_STRING(my_json), LOCATE('"myFieldName":"', JSON_TO_STRING(my_json)) + 15) - (LOCATE('"myFieldName":"', JSON_TO_STRING(my_json)) + 15)
       ) as baz 
from my_table;

不过显然，JSON_VALUE是最优先的选择，既简单又兼容正式部署的数据库方言。

内容的提问来源于stack exchange，提问作者frIT