使用JSON TABLE及Nested Path转换JSON为表时无法获取文档编号与目标市场

核心问题 #

1. 嵌套层级错误：document_numbers和target_markets是JSON根节点下的独立数组，和partnumbers同级，但原代码把它们错误嵌套在了properties的子路径里，导致SQL无法定位到正确的数据节点。
2. 字段拼写错误：代码里的document_verion是拼写错误，JSON中的正确键名是document_version，这会导致该字段返回空值。
3. 无效路径冗余：原代码中包含$.hierarchy_information[*]，但给定的JSON里没有这个节点，属于无效路径，可直接移除。

修正后的SQL代码 #

SELECT JT.*
FROM JSON_TABLE (
    '{
        "general": {
          "product_key": "501088",
          "group_subtype_id": 1,
          "group_subtype_name": "Wheel Speed Sensor",
          "variant_id": 6,
          "variant_name": "DF22",
          "rb_customer_id": 287383
        },
        "partnumbers": [
          {
            "partnumber": "F04FD009BD",
            "pn_type": "Series OEM",
            "mat_status": "00 - planned",
            "properties": [
              {"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"},
              {"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"},
              {"property_id":6,"property_name":"Axle","value_id":62,"value":"Front / Rear Right"},
              {"property_id":7,"property_name":"Base Type - Development P/N","value_id":72,"value":"FFF"},
              {"property_id":8,"property_name":"Base Type - Released P/N","value_id":73,"value":"SSS"}
            ]
          }
        ],
        "document_numbers": [
          {"document_number":"1234569871","document_version":"05","document_type":"TCD"},
          {"document_number":"0123456789","document_version":"01","document_type":"TCD"},
          {"document_number":"1234569870","document_version":"05","document_type":"TCD"},
          {"document_number":"1234567890","document_version":"01","document_type":"TCD"}
        ],
        "target_markets": [
          {"country_name":"Belize","iso_code":"BZ"},
          {"country_name":"Central African Republic","iso_code":"CF"},
          {"country_name":"Albania","iso_code":"AL"}
        ]
      }',
    '$'
    COLUMNS (
      product_key NUMBER PATH '$.general.product_key',
      group_subtype_id NUMBER PATH '$.general.group_subtype_id',
      group_subtype_name VARCHAR2(100) PATH '$.general.group_subtype_name',
      variant_id NUMBER PATH '$.general.variant_id',
      variant_name VARCHAR2(100) PATH '$.general.variant_name',
      rb_customer_id NUMBER PATH '$.general.rb_customer_id',
      -- 处理partnumbers及嵌套的properties
      NESTED PATH '$.partnumbers[*]' COLUMNS (
        partnumber VARCHAR2(100) PATH '$.partnumber',
        pn_type VARCHAR2(100) PATH '$.pn_type',
        mat_status VARCHAR2(100) PATH '$.mat_status',
        NESTED PATH '$.properties[*]' COLUMNS (
          property_id VARCHAR2(100) PATH '$.property_id',
          property_name VARCHAR2(100) PATH '$.property_name',
          value_id VARCHAR2(100) PATH '$.value_id',
          value VARCHAR2(100) PATH '$.value'
        )
      ),
      -- 根级别独立处理document_numbers
      NESTED PATH '$.document_numbers[*]' COLUMNS (
        document_number VARCHAR2(100) PATH '$.document_number',
        document_version VARCHAR2(10) PATH '$.document_version', -- 修正拼写错误，JSON中为字符串类型，用VARCHAR更合适
        document_type VARCHAR2(100) PATH '$.document_type'
      ),
      -- 根级别独立处理target_markets
      NESTED PATH '$.target_markets[*]' COLUMNS (
        country_name VARCHAR2(100) PATH '$.country_name',
        iso_code VARCHAR2(10) PATH '$.iso_code'
      )
    )
) JT;

额外说明 #

• document_version在JSON中是带前导零的字符串（如"05"），用VARCHAR2类型比NUMBER更合适，避免前导零丢失。
• 根级别的数组需要各自作为独立的NESTED PATH节点，不能嵌套在其他子数组的路径中，否则会因路径上下文错误无法读取数据。

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