如何在Oracle中通用解析CLOB存储的JSON并动态生成查询列

实现思路 #

你存储的JSON结构为固定的非嵌套对象数组，无需复杂嵌套解析逻辑，整体分为两步实现：

1. 提取数组中第一个JSON对象的所有键名作为待生成的列名
2. 用提取到的列名动态拼接JSON_TABLE查询语句，执行后即可得到结构化的表结果

步骤1：提取JSON键名 #

优先推荐使用数据库原生JSON函数提取键名，容错率远高于正则匹配，仅当数据库版本不支持JSON函数时再使用正则方案。

方案A：原生JSON函数提取（推荐） #

以Oracle为例，可直接通过JSON_QUERY+JSON_TABLE拿到第一个对象的所有键：

SELECT DISTINCT JSON_VALUE(j.key, '$') AS column_name
FROM Some_Table t,
     JSON_TABLE(JSON_QUERY(t.json_response, '$[0]'), '$.*' COLUMNS key PATH '$') j
WHERE t.Condition = #input_parameter#;

方案B：正则匹配提取 #

如果数据库版本不支持上述JSON函数，可以用正则抓取第一个大括号内的所有键：

SELECT REGEXP_SUBSTR(str, '"(.*?)"', 1, LEVEL, NULL, 1) AS column_name
FROM (
    -- 提取数组中第一个{}包裹的内容
    SELECT REGEXP_SUBSTR((SELECT json_response FROM Some_Table WHERE Condition=#input_parameter#), '\{(.*?)\}', 1, 1, NULL, 1) AS str
    FROM dual
)
CONNECT BY REGEXP_SUBSTR(str, '"(.*?)"', 1, LEVEL) IS NOT NULL;

步骤2：动态生成列定义并执行查询 #

通过存储过程拼接动态SQL即可实现，以Oracle存储过程为例：

CREATE OR REPLACE PROCEDURE parse_dynamic_json(p_input_param IN VARCHAR2, p_result OUT SYS_REFCURSOR) IS
    v_clob CLOB;
    v_col_def VARCHAR2(32767);
    v_sql VARCHAR2(32767);
BEGIN
    -- 读取目标CLOB数据
    SELECT json_response INTO v_clob FROM Some_Table WHERE Condition = p_input_param;
    
    -- 批量拼接COLUMNS后的列定义规则，所有列统一用varchar(256)
    SELECT LISTAGG(column_name || ' VARCHAR2(256) PATH ''$.' || column_name || '''', ',') 
    INTO v_col_def
    FROM (
        -- 此处替换为你选择的键名提取SQL，优先使用原生JSON函数方案
        SELECT DISTINCT JSON_VALUE(j.key, '$') AS column_name
        FROM JSON_TABLE(JSON_QUERY(v_clob, '$[0]'), '$.*' COLUMNS key PATH '$') j
    );
    
    -- 组装完整的查询SQL
    v_sql := 'SELECT * FROM JSON_TABLE(:v_clob, ''$[*]'' COLUMNS ' || v_col_def || ')';
    
    -- 执行动态SQL，返回结果游标
    OPEN p_result FOR v_sql USING v_clob;
END;
/

调用该存储过程传入参数后，拿到的返回游标就是你需要的结构化表结果，列数和列名完全匹配CLOB内JSON的键。

注意事项 #

• 若提取到的键名包含SQL保留关键字，拼接列定义时给列名两端加上双引号包裹即可正常使用
• 若单条CLOB的JSON键数量过多导致拼接的SQL超过VARCHAR2长度上限，可将拼接变量替换为CLOB类型
• 本方案逻辑为通用逻辑，若使用MySQL/PostgreSQL等其他数据库，仅需将上述Oracle专属的JSON函数、动态游标语法替换为对应数据库的语法即可使用

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