H2数据库中模拟JSON_VALUE函数的替代方案

h2数据库原生不支持json_value函数，但可通过自定义函数或正则表达式提取json字段值，本文提供两种兼容性高、易于集成的解决方案。

在基于H2进行单元测试或本地开发时，若业务SQL中使用了标准SQL/MySQL/Oracle的 JSON_VALUE( json_doc, path ) 函数（如 JSON_VALUE(ADDR_JSON, '$.pinCode')），会因H2未内置该函数而报错：user lacks privilege or object not found: JSON_VALUE。由于H2 2.2.x及更早版本（截至2024年）仍未原生支持JSON路径查询函数，需采用以下两种经实践验证的替代方案：

✅ 方案一：注册Java自定义函数（推荐，语义清晰、健壮性强）

利用H2的用户自定义函数（UDF）机制，通过CREATE ALIAS将Java方法暴露为SQL函数。需确保测试类路径下已引入轻量级JSON库（如org.json:json或com.fasterxml.jackson.core:jackson-databind）。

示例（使用org.json）：

-- 在应用启动时（如Spring @PostConstruct 或 H2初始化脚本中）执行：
CREATE ALIAS IF NOT EXISTS JSON_VALUE AS $$
String jsonValue(String json, String path) {
    if (json == null || path == null) return null;
    try {
        // 解析JSON字符串（支持简单对象，如 {"pinCode":"390018"}）
        org.json.JSONObject obj = new org.json.JSONObject(json);
        // 简单路径支持 "$.pinCode" → 转换为 "pinCode"
        String key = path.startsWith("$.") ? path.substring(2) : path;
        Object val = obj.opt(key);
        return val == null ? null : String.valueOf(val);
    } catch (Exception e) {
        return null; // 或抛出 RuntimeException 触发SQL错误
    }
}
$$;

✅ 优点：语义准确、可处理null/异常、易于单元测试；
⚠️ 注意：仅支持一级键（如$.pinCode），不支持嵌套路径（如$.address.city）或数组索引；如需完整JSONPath支持，建议升级至H2 2.3.232+（实验性JSON_EXTRACT）或切换为Testcontainers + PostgreSQL。

✅ 方案二：正则提取（轻量、无依赖，适用于固定结构JSON）

对格式严格、无转义/嵌套的JSON，可用H2内置REGEXP_SUBSTR粗略提取：

MvMmall 网店系统

免费的开源程序长期以来，为中国的网上交易提供免费开源的网上商店系统一直是我们的初衷和努力奋斗的目标，希望大家一起把MvMmall网上商店系统的免费开源进行到底。2高效的执行效率由资深的开发团队设计，从系统架构,数据库优化，配以通过W3C验证的面页模板，全面提升页面显示速度和提高程序负载能力。3灵活的模板系统MvMmall网店系统程序代码与网页界面分离，灵活的模板方案，完全自定义模板，官方提供免费模

下载

-- 提取 "pinCode":"390018" 中的值（支持前后空格和双引号）
SELECT * FROM EMPLOYEE 
WHERE REGEXP_SUBSTR(ADDR_JSON, '"pinCode"\s*:\s*"([^"]*)"', 1, 1, 'i', 1) != '390018';

? 正则说明：

• '"pinCode"\s*:\s*"' 匹配键名及冒号；
• ([^"]*) 捕获双引号内非引号字符（即值）；
• 第6参数 1 表示返回第一个捕获组。

⚠️ 严重限制：无法处理换行、转义引号（如"name":"O\"Reilly"）、嵌套对象或数字/布尔值（需额外类型转换）；仅建议用于POC或结构极度简单的测试数据。

? 总结与建议

最佳实践：

• 在application-test.yml中配置H2初始化脚本（spring.sql.init.schema-locations=classpath:h2-init.sql），预先注册JSON_VALUE别名；
• 生产环境仍应使用真实数据库（如PostgreSQL的jsonb_path_query_first）做最终验证；
• 长期项目建议抽象JSON访问逻辑至DAO层，避免SQL硬编码JSON路径，提升数据库可移植性。