Oracle 19c JSON解析：无法获取嵌套GL_MAP的表格数据

核心查询语句 #

假设你的表名为your_table，存储JSON的BLOB字段为json_blob，可以使用两种方式提取目标数据：

方法1：嵌套JSON_TABLE（直观易调试）

SELECT
    jt2.gl_code,
    jt2.txn_name,
    jt2.txn_type_id
FROM
    your_table t,
    -- 第一层：遍历cross_references数组，提取key和对应的values子数组
    JSON_TABLE(
        t.json_blob FORMAT JSON,
        '$.cross_references[*]' COLUMNS (
            ref_key VARCHAR2(100) PATH '$.key',
            values_clob CLOB PATH '$.values' FORMAT JSON
        )
    ) jt1,
    -- 第二层：遍历GL_MAP对应的values子数组，提取目标字段
    JSON_TABLE(
        jt1.values_clob,
        '$[*]' COLUMNS (
            gl_code VARCHAR2(50) PATH '$.gl_code',
            txn_name VARCHAR2(100) PATH '$.txn_name',
            txn_type_id NUMBER PATH '$.txn_type_id'
        )
    ) jt2
WHERE
    jt1.ref_key = 'GL_MAP';

方法2：带过滤条件的JSONPath（简洁高效）

SELECT
    jt.gl_code,
    jt.txn_name,
    jt.txn_type_id
FROM
    your_table t,
    JSON_TABLE(
        t.json_blob FORMAT JSON,
        -- 直接定位到cross_references中key为GL_MAP的对象的values数组元素
        '$.cross_references[?(@.key == "GL_MAP")].values[*]' COLUMNS (
            gl_code VARCHAR2(50) PATH '$.gl_code',
            txn_name VARCHAR2(100) PATH '$.txn_name',
            txn_type_id NUMBER PATH '$.txn_type_id'
        )
    ) jt;

关键注意事项 #

1. 必须指定FORMAT JSON：Oracle无法自动识别BLOB中的JSON内容，必须在JSON_TABLE中添加该参数，告知数据库这是合法的JSON数据。
2. JSONPath语法规范：Oracle 19c的JSONPath要求字符串匹配使用双引号，过滤表达式[?(@.key == "GL_MAP")]用于精准定位目标数组元素。
3. 数据类型匹配：根据JSON中实际字段类型调整查询里的字段类型（比如txn_type_id如果是字符串，就把NUMBER改为VARCHAR2）。
4. JSON有效性验证：如果查询无结果，先验证BLOB中的JSON是否合法：

   SELECT * FROM your_table WHERE json_blob IS JSON FORMAT JSON;
   

   这条语句会筛选出存储了有效JSON的行，排除格式错误的数据。

常见失败原因排查 #

• 未添加FORMAT JSON参数，导致Oracle无法解析BLOB中的JSON结构。
• JSONPath语法错误（比如用单引号包裹字符串、数组遍历路径错误）。
• 多层数组结构未正确嵌套JSON_TABLE，导致无法遍历到子数组元素。

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