Oracle中如何解析多层嵌套JSON_结合JSON

Oracle 12c+ 支持用 JSON_TABLE 解析多层嵌套 JSON，但需确保 JSON 合法、路径准确、类型匹配，并善用 NESTED PATH 处理数组嵌套，避免笛卡尔积；对结构不固定场景应前置清洗或改用 PL/SQL 动态解析。

Oracle 12c+ 直接用 `JSON_TABLE` 解析多层嵌套 JSON 是可行的，但必须满足 JSON 格式合法且路径准确

oracle 12.1.0.2 起支持原生 json 函数，json_table 是目前最稳妥的解析方式。它不像 json_value 只能取单值，也不像老式正则或 xmltype 转换那样易出错。但前提是：你的 json 字符串必须通过 is json 校验为合法 json —— 即使是带转义、空格、unicode 的标准格式也没问题；而一旦含 html 实体、未闭合引号或 js 风格（如单引号、尾逗号），就会报 ora-40442: json syntax error。

实操建议：

先用 SELECT * FROM DUAL WHERE '<your_json>' IS JSON;</your_json> 快速验证合法性
嵌套数组优先走 $[*] 路径，对象字段用 $.key，深层嵌套如 $.data.items[0].meta.id 可直接写，无需预展开
若某层可能缺失（比如 $.user.profile 有时为 null 或根本不存在），在 COLUMNS 子句中加 MISSING ON ERROR，否则整行会被过滤掉
避免在 JSON_TABLE 的 PATH 中拼接变量 —— Oracle 不支持动态路径，需用 PL/SQL 拼字符串再 EXECUTE IMMEDIATE

嵌套数组展开时，`Nested PATH` 比重复写 `$[*].xxx` 更清晰且性能更好

比如 JSON 中有 "orders": [{"id":1,"items":[{"sku":"A"},{"sku":"B"}]}, {"id":2,"items":[{"sku":"C"}]}]，想展开成每行一个 item 并带上 order.id —— 如果只用一层 JSON_TABLE，就得在 COLUMNS 里反复引用 $.orders[*].id，但这样会触发笛卡尔积（order × items），结果错乱。

正确做法是用嵌套 NESTED PATH：

JSON_TABLE(
  json_col, 
  '$.orders[*]' COLUMNS (
    order_id NUMBER PATH '$.id',
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR2(20) PATH '$.sku'
    )
  )
)

这样 Oracle 内部会做关联展开，顺序和层级关系保持原样。注意：NESTED PATH 必须放在 COLUMNS 内，不能在外层路径上直接套两层 $[*]；另外，如果某 order 的 items 是空数组或 null，默认该 order 下无记录，需加 NULL ON EMPTY 控制行为。

`JSON_TABLE` 的列定义类型不匹配会导致静默截断或转换失败

比如 JSON 里 "price": 99.95，你在 COLUMNS 定义成 price NUMBER(3)，Oracle 会报 ORA-40473: invalid data type for JSON path；定义成 VARCHAR2(5) 则可能截成 "99.95"（长度够）或 "99.9"（如果 JSON 值是 99.956）。更隐蔽的是布尔值：true / false 必须用 BOOLEAN 类型声明，若写成 VARCHAR2，返回的是字符串 "true"，不是 Oracle 的 TRUE 布尔字面量，无法参与 WHERE is_active = TRUE 这类判断。

chartjs-reporter

此技能用于在用户需要将结构化数据（查询结果、CSV 摘要、JSON 记录或 Python 字典/列表）转换为独立的 HTML 页面时使用。

下载

关键点：

数值统一用 NUMBER，别依赖隐式转换；精度不足时 Oracle 报错，不是四舍五入
日期字段如 "created": "2023-10-05T08:30:00Z"，要用 TIMESTAMP WITH TIME ZONE PATH '$.created'，否则转成字符串后难处理
嵌套对象若只需取其中一两个字段，不要定义 OBJECT 类型再二次解析 —— 开销大，直接在内层 NESTED PATH 展开更高效

当 JSON 层级过深或结构不固定时，`JSON_TABLE` 会变脆弱，这时应考虑前置清洗或改用 PL/SQL

比如第三方接口返回的 JSON，$.response.data.results[*].payload 下可能是对象、数组或 null，且字段名随机（"fld_123", "field_x"），这种场景硬写 JSON_TABLE 路径几乎不可维护。Oracle 不支持通配符路径（如 $.**.id），也没有类似 jq 的递归下降语法。

此时更实际的做法：

用 JSON_QUERY(json_col, '$.response.data.results') RETURNING CLOB 先抽取出 results 数组，再在应用层或 PL/SQL 中逐条处理
写个简单 PL/SQL 函数，用 JSON_OBJECT_T / JSON_ARRAY_T 解析，配合 get_keys() 和循环，动态提取字段 —— 虽慢一点，但逻辑可控
如果数据量大且需频繁查询，不如在入库前用外部工具（Python + jq）标准化 JSON 结构，再存入规范化的表字段

真正麻烦的从来不是语法怎么写，而是你没法假设上游 JSON 永远守规矩。路径写死、类型写死、层级写死 —— 这三处任何一环松动，JSON_TABLE 就容易变成定时报错源。