如何使用JSON_DATAGUIDE等内置函数动态获取JSON的键值对

解决方案 #

你可以通过组合JSON_DATAGUIDE提取键路径、JSON_TABLE解析路径列表、JSON_VALUE取对应值的方式实现需求，全程为纯SQL语法，无需使用PL/SQL相关特性，可直接运行的代码如下：

WITH input_json AS (
    -- 此处替换为你实际需要处理的JSON字段/值
    SELECT '{a:100, b:200, c:300}' AS json_content FROM dual
)
SELECT
    SUBSTR(jd.path, 3) AS "Column Key",
    JSON_VALUE(ij.json_content, jd.path) AS "Column Value"
FROM
    input_json ij,
    JSON_TABLE(
        JSON_DATAGUIDE(ij.json_content),
        '$[*]' COLUMNS (
            path VARCHAR2(100) PATH '$."o:path"'
        )
    ) jd;

实现逻辑说明 #

1. CTE input_json 用于统一存储待处理的原始JSON，避免重复书写相同的JSON值，实际使用时可替换为你业务表中的JSON字段。
2. 调用JSON_DATAGUIDE获取JSON的所有键路径元数据，再通过JSON_TABLE将元数据数组拆分为多行，每行对应一个键的完整路径（形如$.a）。
3. 通过SUBSTR函数截断路径前缀$.，得到纯键名作为Column Key。
4. 调用JSON_VALUE，传入提取到的路径从原始JSON中读取对应值，作为Column Value。

本方案适用于Oracle 12c Release 2及更高版本，所有依赖函数均为Oracle原生内置SQL函数，符合你提出的语法限制要求。

内容的提问来源于stack exchange，提问作者samurai-jack07