如何在Oracle PLSQL中读取JSON中的动态嵌套对象？

问题描述 #

需要读取如下JSON数据中discountDetail对象下的所有嵌套对象字段，但这些嵌套对象的ID是运行时生成的动态值，无法提前预知，如何在Oracle PL/SQL中实现？

示例JSON：

{
  "responseHeader": {
    "transactionId": "357910e9558654b6:20f4dee6:18d59ad95c2:-800056572",
    "status": {
      "code": "SUCCESS",
      "type": "S",
      "description": "TRANSACTION SUCCESSFUL"
    },
    "discountDetail": {
      "65300": {
        "discountId": "65300",
        "discountName": "Distributor Standard Discount - USD",
        "discountDescription": "Standard - Distributor Standard Discount - USD",
        "discountGroup": "Standard",
        "modifierLineTypeCode": "DIS",
        "discountMethodCode": "%",
        "modifierNumber": "Distributor Standard Discount - USD",
        "discountLineNumber": "Line, %",
        "listHeaderId": "0.0",
        "listLineId": "0.0",
        "priceBreakTypeCode": "N",
        "prorationTypeCode": "N",
        "postTermClause": false,
        "pricingPhaseId": "2",
        "accrualIndicator": "N",
        "automaticIndicator": "N",
        "updateIndicator": "Y",
        "appliedIndicator": "Y",
        "overRideableIndicator": "Y"
      },
      "230935": {
        "discountId": "230935",
        "discountName": "BR-Deal Registration-USD",
        "discountDescription": "Promotion-BR-Hunt-210731-10660",
        "discountGroup": "Promotion",
        "modifierLineTypeCode": "DIS",
        "discountMethodCode": "%",
        "modifierNumber": "BR-Deal Registration-USD",
        "discountLineNumber": "Line,%",
        "listHeaderId": "1566939",
        "listLineId": "254610462",
        "priceBreakTypeCode": "N",
        "discountType": "ES",
        "adjustmentMethodCode": "Additive",
        "pricingPhaseId": "2.0",
        "updateAllowableIndicator": "N",
        "updateIndicator": "Y",
        "appliedIndicator": "Y",
        "printOnInvoiceIndicator": "N"
      },
      "231832": {
        "discountId": "231832",
        "discountName": "BR-Special Offers-USD",
        "discountDescription": "Promotion-BR-MSSB-220730-12374",
        "discountGroup": "Promotion",
        "modifierLineTypeCode": "DIS",
        "discountMethodCode": "%",
        "modifierNumber": "BR-Special Offers-USD",
        "discountLineNumber": "Line,%",
        "listHeaderId": "561690",
        "listLineId": "94777454",
        "priceBreakTypeCode": "N",
        "prorationTypeCode": "N",
        "postTermClause": false,
        "pricingPhaseId": "2",
        "accrualIndicator": "N",
        "automaticIndicator": "N",
        "updateAllowableIndicator": "N",
        "updateIndicator": "Y",
        "appliedIndicator": "Y",
        "printOnInvoiceIndicator": "N",
        "overRideableIndicator": "N"
      }
    }
  }
}

解决方案 #

方法1：使用JSON_TABLE配合通配符直接解析 #

利用JSON_TABLE的通配符*匹配discountDetail下的所有动态键，直接解析对应对象的字段，无需提前知道ID值。

DECLARE
  l_json CLOB := '<上述完整JSON字符串>';
BEGIN
  FOR rec IN (
    SELECT 
      jt.discount_id,
      jt.discount_name,
      jt.discount_description,
      jt.discount_group,
      jt.modifier_line_type_code,
      jt.discount_method_code,
      jt.modifier_number,
      jt.discount_line_number,
      jt.list_header_id,
      jt.list_line_id,
      jt.price_break_type_code,
      jt.proration_type_code,
      jt.post_term_clause,
      jt.pricing_phase_id,
      jt.accrual_indicator,
      jt.automatic_indicator,
      jt.update_allowable_indicator,
      jt.update_indicator,
      jt.applied_indicator,
      jt.print_on_invoice_indicator,
      jt.over_rideable_indicator
    FROM 
      JSON_TABLE(
        l_json,
        '$.responseHeader.discountDetail.*'
        COLUMNS (
          discount_id VARCHAR2(50) PATH '$.discountId',
          discount_name VARCHAR2(200) PATH '$.discountName',
          discount_description VARCHAR2(500) PATH '$.discountDescription',
          discount_group VARCHAR2(50) PATH '$.discountGroup',
          modifier_line_type_code VARCHAR2(10) PATH '$.modifierLineTypeCode',
          discount_method_code VARCHAR2(10) PATH '$.discountMethodCode',
          modifier_number VARCHAR2(200) PATH '$.modifierNumber',
          discount_line_number VARCHAR2(50) PATH '$.discountLineNumber',
          list_header_id VARCHAR2(50) PATH '$.listHeaderId',
          list_line_id VARCHAR2(50) PATH '$.listLineId',
          price_break_type_code VARCHAR2(10) PATH '$.priceBreakTypeCode',
          proration_type_code VARCHAR2(10) PATH '$.prorationTypeCode',
          post_term_clause VARCHAR2(10) PATH '$.postTermClause',
          pricing_phase_id VARCHAR2(10) PATH '$.pricingPhaseId',
          accrual_indicator VARCHAR2(10) PATH '$.accrualIndicator',
          automatic_indicator VARCHAR2(10) PATH '$.automaticIndicator',
          update_allowable_indicator VARCHAR2(10) PATH '$.updateAllowableIndicator',
          update_indicator VARCHAR2(10) PATH '$.updateIndicator',
          applied_indicator VARCHAR2(10) PATH '$.appliedIndicator',
          print_on_invoice_indicator VARCHAR2(10) PATH '$.printOnInvoiceIndicator',
          over_rideable_indicator VARCHAR2(10) PATH '$.overRideableIndicator'
        )
      ) jt
  ) LOOP
    -- 处理每条折扣记录，示例为打印关键信息
    DBMS_OUTPUT.PUT_LINE('折扣ID: ' || rec.discount_id || '，折扣名称: ' || rec.discount_name);
  END LOOP;
END;
/

方法2：使用JSON_OBJECT_T动态遍历键值 #

如果字段也存在动态变化的可能，可使用Oracle 12c+引入的JSON_OBJECT_T类型，通过get_keys()获取所有动态ID，再逐个解析对应对象的字段。

DECLARE
  l_json_obj JSON_OBJECT_T;
  l_discount_detail JSON_OBJECT_T;
  l_keys JSON_KEY_LIST;
  l_discount_obj JSON_OBJECT_T;
  i NUMBER := 1;
BEGIN
  l_json_obj := JSON_OBJECT_T('<上述完整JSON字符串>');
  
  -- 获取discountDetail对象
  l_discount_detail := l_json_obj.get_object('responseHeader').get_object('discountDetail');
  -- 获取所有动态ID
  l_keys := l_discount_detail.get_keys();
  
  -- 遍历每个ID对应的折扣对象
  WHILE i <= l_keys.COUNT LOOP
    l_discount_obj := l_discount_detail.get_object(l_keys(i));
    
    -- 读取并打印字段
    DBMS_OUTPUT.PUT_LINE('动态ID: ' || l_keys(i));
    DBMS_OUTPUT.PUT_LINE('折扣ID: ' || l_discount_obj.get_string('discountId'));
    DBMS_OUTPUT.PUT_LINE('折扣名称: ' || l_discount_obj.get_string('discountName'));
    -- 其他字段可按需读取，若字段也动态，可继续用get_keys遍历字段
    
    i := i + 1;
  END LOOP;
END;
/

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