Oracle JSON_TABLE解析异常：仅能提取general节点内容

示例JSON数据 #

{
  "general": {
    "doc_id": "DOC123",
    "title": "Sample Document",
    "created_date": "2024-05-20"
  },
  "partnumbers": [
    {
      "part_id": "P001",
      "name": "Engine Part",
      "properties": {
        "material": "Steel",
        "weight": "5.5kg",
        "dimensions": "10x20x5cm"
      }
    },
    {
      "part_id": "P002",
      "name": "Transmission Part",
      "properties": {
        "material": "Aluminum",
        "weight": "3.2kg",
        "dimensions": "15x12x8cm"
      }
    }
  ]
}

原查询问题 #

原代码仅能提取general节点信息，无法解析嵌套的partnumbers数组及其子节点properties，示例原代码如下：

SELECT jt.*
FROM your_table t,
     JSON_TABLE(t.json_column, '$'
       COLUMNS (
         doc_id VARCHAR2(50) PATH '$.general.doc_id',
         title VARCHAR2(100) PATH '$.general.title',
         created_date DATE PATH '$.general.created_date'
       )
     ) jt;

修正后的查询代码 #

通过**多层NESTED PATH**可以遍历嵌套的数组和子对象，完整解析所有节点：

SELECT 
  jt.doc_id,
  jt.title,
  jt.created_date,
  jt_part.part_id,
  jt_part.part_name,
  jt_part.material,
  jt_part.weight,
  jt_part.dimensions
FROM your_table t,
     JSON_TABLE(t.json_column, '$'
       COLUMNS (
         doc_id VARCHAR2(50) PATH '$.general.doc_id',
         title VARCHAR2(100) PATH '$.general.title',
         created_date DATE PATH '$.general.created_date',
         -- 遍历partnumbers数组的所有元素
         NESTED PATH '$.partnumbers[*]'
           COLUMNS (
             part_id VARCHAR2(50) PATH '$.part_id',
             part_name VARCHAR2(100) PATH '$.name',
             -- 解析每个零件的properties子对象
             NESTED PATH '$.properties'
               COLUMNS (
                 material VARCHAR2(50) PATH '$.material',
                 weight VARCHAR2(20) PATH '$.weight',
                 dimensions VARCHAR2(50) PATH '$.dimensions'
               )
           )
       )
     ) jt;

关键说明 #

1. NESTED PATH '$.partnumbers[*]'：[*]匹配数组中的所有元素，实现对partnumbers数组的遍历
2. 嵌套的NESTED PATH '$.properties'：直接解析每个零件对象下的properties子节点，提取其内部字段
3. 空值/异常处理（可选）：如果properties可能为空或不存在，可以添加DEFAULT子句避免空值：

   material VARCHAR2(50) PATH '$.material' DEFAULT 'N/A' ON EMPTY
   

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