H2数据库PostgreSQL模式下JSON_BUILD_OBJECT函数找不到的解决方法

在测试场景中使用H2数据库模拟PostgreSQL，配置如下：

private static final String JDBC_URL = "jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1";
Connection connection = DriverManager.getConnection(JDBC_URL);

尝试过H2 2.2.220和2.1.214版本，执行以下SQL查询时创建预编译语句报错：

SELECT json_build_object('product', c."info"->'product') as info 
FROM item."Item" AS c          
INNER JOIN (
    SELECT "rootId", MAX("revisionNo") AS maxRevisionNo     
    FROM item."Item"     
    WHERE "cRootId" = ?    
    GROUP BY "rootId" 
) AS subquery ON c."rootId" = subquery."rootId" AND c."revisionNo" = subquery.maxRevisionNo 
WHERE c."state" = 'active'

执行connection.prepareStatement(query)时抛出异常：

org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "JSON_BUILD_OBJECT" not found

该查询在真实PostgreSQL环境可正常运行，但H2的PostgreSQL模式下失败。

方法1：替换为兼容H2和PostgreSQL的JSON构造函数 #

H2的PostgreSQL模式并未完全实现所有PostgreSQL特定函数，json_build_object就是其中之一。可以改用SQL标准的JSON_OBJECT函数，该函数在PostgreSQL 12+和H2中均受支持：

修改后的查询：

SELECT JSON_OBJECT('product' VALUE c."info"->'product') as info 
FROM item."Item" AS c          
INNER JOIN (
    SELECT "rootId", MAX("revisionNo") AS maxRevisionNo     
    FROM item."Item"     
    WHERE "cRootId" = ?    
    GROUP BY "rootId" 
) AS subquery ON c."rootId" = subquery."rootId" AND c."revisionNo" = subquery.maxRevisionNo 
WHERE c."state" = 'active'

如果你的PostgreSQL版本低于12，也可以用兼容写法JSON_OBJECT('product' := c."info"->'product')，同样能在两者中运行。

方法2：在H2中自定义JSON_BUILD_OBJECT函数 #

如果不想修改原有SQL，可以在H2数据库初始化时创建一个自定义函数，模拟PostgreSQL的json_build_object行为：

执行以下SQL语句创建函数：

CREATE FUNCTION JSON_BUILD_OBJECT(VARIADIC args ANY)
RETURNS JSON
DETERMINISTIC
BEGIN ATOMIC
    DECLARE result JSON = JSON_OBJECT();
    DECLARE i INT = 1;
    WHILE i <= ARRAY_LENGTH(args) DO
        SET result = JSON_SET(result, CONCAT('$.', args[i]), args[i+1]);
        SET i = i + 2;
    END WHILE;
    RETURN result;
END;

该函数接收可变参数，按键值对顺序构造JSON对象，和PostgreSQL的json_build_object行为一致。你可以在获取H2连接后，先执行这段SQL初始化函数，再执行原有查询。

方法3：等待H2版本更新（暂不可用） #

目前H2的PostgreSQL模式尚未原生支持json_build_object函数，若后续H2版本添加了该函数的兼容实现，可直接升级H2版本解决问题。但截至2.2.220版本，此方法无法生效。

内容的提问来源于stack exchange，提问作者Valeriy K.