H2数据库JSON_TABLE别名创建失败，请求技术支持

问题背景 #

为编写单元测试，尝试给H2数据库的JSON_TABLE命令创建别名，但执行时出现编译错误。原创建别名的SQL代码如下：

DROP ALIAS if exists JSON_TABLE;
CREATE ALIAS IF NOT EXISTS JSON_TABLE AS '
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.h2.tools.SimpleResultSet;
import org.json.JSONArray;
import org.json.JSONObject;
import java.lang.String;
java.sql.ResultSet jsonTable(java.lang.String json, java.lang.String key) throws java.sql.SQLException {
    org.h2.tools.SimpleResultSet rs = new org.h2.tools.SimpleResultSet();
    rs.addColumn("FIELD", Types.VARCHAR, 255, 0);
    rs.addColumn("OLD_VALUE", Types.VARCHAR, 255, 0);
    rs.addColumn("NEW_VALUE", Types.VARCHAR, 255, 0);

    org.json.JSONArray jsonArray = new org.json.JSONArray(json);
    for (int i = 0; i < jsonArray.length(); i++) {
        org.json.JSONObject obj = jsonArray.getJSONObject(i);
        rs.addRow(
            obj.optString("field", null),
            obj.optString("oldValue", null),
            obj.optString("newValue", null)
        );
    }
    return rs;
}';

使用该别名的视图定义：

CREATE OR REPLACE VIEW dummy.V AS
SELECT ROWNUM        AS ID,
       a.changed_at  AS "CHANGED_AT",
       a.changed_by  AS "CHANGED_BY",
       a.object_id   AS "OBJECT_ID",
       a.order_id    AS "ORDER_ID",
       a.order_type  AS "ORDER_TYPE",
       a.object_type AS "OBJECT_TYPE",
       a.action      AS "ACTION",
       a.task_id     AS "TASK_ID",
       jt.FIELD      AS FIELD,
       jt.OLD_VALUE  AS OLD_VALUE,
       jt.NEW_VALUE  AS NEW_VALUE
FROM dummy.Table a,
     json_table(
             a.changes, '$[*]'
         COLUMNS (
             FIELD VARCHAR2(255 CHAR) PATH '$.field',
             OLD_VALUE VARCHAR2(255 CHAR) PATH '$.oldValue',
             NEW_VALUE VARCHAR2(255 CHAR) PATH '$.newValue'
             )
     ) jt

报错核心信息：

/org/h2/dynamic/JSON_TABLE.java:7: error: illegal start of type
import java.sql.ResultSet;
^
/org/h2/dynamic/JSON_TABLE.java:7: error: <identifier> expected
import java.sql.ResultSet;
                 ^
...（共14个类似编译错误）

错误原因 #

H2数据库的CREATE ALIAS要求提供的Java代码必须是完整的类定义，不能直接写import语句和独立方法。H2会将这段代码编译成一个Java类，原代码缺少类包裹，导致编译器无法识别语法结构。

修复方案 #

1. 修正别名创建SQL #

将Java代码包裹在一个类中，同时调整引号转义（把"替换为"，并在SQL中用单引号转义），修正后的代码如下：

DROP ALIAS IF EXISTS JSON_TABLE;
CREATE ALIAS IF NOT EXISTS JSON_TABLE AS '
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.h2.tools.SimpleResultSet;
import org.json.JSONArray;
import org.json.JSONObject;

public class JsonTableFunction {
    public static ResultSet jsonTable(String json) throws SQLException {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("FIELD", Types.VARCHAR, 255, 0);
        rs.addColumn("OLD_VALUE", Types.VARCHAR, 255, 0);
        rs.addColumn("NEW_VALUE", Types.VARCHAR, 255, 0);

        JSONArray jsonArray = new JSONArray(json);
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject obj = jsonArray.getJSONObject(i);
            rs.addRow(
                obj.optString("field", null),
                obj.optString("oldValue", null),
                obj.optString("newValue", null)
            );
        }
        return rs;
    }
}';

2. 适配视图调用 #

由于当前实现只接受JSON字符串参数（无法解析标准JSON_TABLE的路径和列定义语法），需要调整视图的调用方式：

CREATE OR REPLACE VIEW dummy.V AS
SELECT ROWNUM        AS ID,
       a.changed_at  AS "CHANGED_AT",
       a.changed_by  AS "CHANGED_BY",
       a.object_id   AS "OBJECT_ID",
       a.order_id    AS "ORDER_ID",
       a.order_type  AS "ORDER_TYPE",
       a.object_type AS "OBJECT_TYPE",
       a.action      AS "ACTION",
       a.task_id     AS "TASK_ID",
       jt.FIELD      AS FIELD,
       jt.OLD_VALUE  AS OLD_VALUE,
       jt.NEW_VALUE  AS NEW_VALUE
FROM dummy.Table a,
     TABLE(JSON_TABLE(a.changes)) jt

3. 额外说明 #

如果需要完全兼容标准JSON_TABLE的语法（支持路径和列定义），需要更复杂的实现：

• 解析第二个参数中的JSON路径表达式
• 解析COLUMNS子句中的列定义和路径
• 动态生成结果集的列和数据

但上述修复已经解决了编译错误，满足基础的JSON数组展开需求。

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