如何用PLSQL遍历复杂JSON对象并生成行式查询结果？

针对嵌套数组类型的复杂JSON，Oracle提供JSON_TABLE内置函数可实现行式拆分遍历，替代返回数组聚合结果的JSON_QUERY。

问题场景 #

给定的JSON为嵌套二维数组结构：

[[{"PONumber":1,"ItemNumber":1,"Part":{"Description":"Tora! Tora! Tora!","UnitPrice":19.95,"UPCCode":24543013174},"Quantity":2.0},{"ItemNumber":2,"Part":{"Description":"The Beastmaster","UnitPrice":19.95,"UPCCode":13131201598},"Quantity":4.0},{"ItemNumber":3,"Part":{"Description":"Heavy Traffic","UnitPrice":19.95,"UPCCode":27616852854},"Quantity":6.0}]]

原查询使用JSON_QUERY返回的是数组形式结果，无法得到逐行展开的结构化数据。

解决方案：使用JSON_TABLE拆分数组 #

JSON_TABLE可将JSON数组映射为关系型表结构，通过多层路径解析嵌套数组，实现行式输出：

示例SQL

WITH json_documents AS (
  SELECT '[[{"PONumber":1,"ItemNumber":1,"Part":{"Description":"Tora! Tora! Tora!","UnitPrice":19.95,"UPCCode":24543013174},"Quantity":2.0},{"ItemNumber":2,"Part":{"Description":"The Beastmaster","UnitPrice":19.95,"UPCCode":13131201598},"Quantity":4.0},{"ItemNumber":3,"Part":{"Description":"Heavy Traffic","UnitPrice":19.95,"UPCCode":27616852854},"Quantity":6.0}]]' AS data
)
SELECT 
  po.PONumber,
  line_item.ItemNumber AS LINE_ITEMNR,
  line_item.Part_Description AS Description,
  line_item.Part_UnitPrice AS UnitPrice,
  line_item.Part_UPCCode AS UPCCode,
  line_item.Quantity
FROM json_documents jd,
     -- 解析最外层二维数组，提取订单编号与内层订单行数组
     JSON_TABLE(jd.data, '$[*]'
       COLUMNS (
         PONumber NUMBER PATH '$[0].PONumber',
         line_items CLOB FORMAT JSON PATH '$[*]'
       )
     ) po,
     -- 拆分订单行数组为独立行
     JSON_TABLE(po.line_items, '$[*]'
       COLUMNS (
         ItemNumber NUMBER PATH '$.ItemNumber',
         Part_Description VARCHAR2(100) PATH '$.Part.Description',
         Part_UnitPrice NUMBER PATH '$.Part.UnitPrice',
         Part_UPCCode VARCHAR2(20) PATH '$.Part.UPCCode',
         Quantity NUMBER PATH '$.Quantity'
       )
     ) line_item;

执行结果

关键说明 #

• JSON_TABLE是Oracle 12c及以上版本内置函数，专门用于将JSON数据转换为关系型表结构；
• 分步解析嵌套数组：先拆分最外层二维数组，提取订单编号与内层订单行集合，再将订单行集合拆分为独立数据行；
• 针对仅存在于第一个元素的PONumber，通过$[0].PONumber路径提取后与所有订单行关联，保证每行都能获取到对应订单编号。

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