How to read or access dynamic nested object from JSON in Oracle PLSQL?

SadashivGawade
March 18, 2024
239 views
0 votes
2 Answers

I have below JSON I want to Read all fields from 65300, 230935, 231832 objects which are under discountDetail objects. Problem is 65300, 230935, 231832 objects are dynamic and are not known in advance. These are ids which are created on run time. Is there any way to read such dynamic nested object from 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"
      }
    },
    "qualifiedPromotions": {
      "230935": {
        "promotionId": "230935",
        "promotionVersion": "25",
        "promotionCode": "BR-Hunt-210731-10660",
        "bundleType": "",
        "channelProgramName": "Deal Registration",
        "name": "Hunting",
        "discType": "ES",
        "autoApply": "N",
        "promotionDiscountType": "Additive",
        "startDate": "2022-12-21",
        "endDate": "2023-06-04",
        "status": "EXPIRED"
      },
      "231832": {
        "promotionId": "231832",
        "promotionVersion": "10",
        "promotionCode": "BR-MSSB-220730-12374",
        "bundleType": "ARCHITECTURE",
        "channelProgramName": "Special Offers",
        "name": "Meraki Smart Spaces Bundle - EEA",
        "discType": "ES",
        "startDate": "2023-02-10",
        "endDate": "2023-07-29",
        "status": "EXPIRED"
      }
    },
    "disqualifiedPromotions": {
      "233681": {
        "promotionId": "233681",
        "promotionCode": "PP-Fast-171028-02253",
        "disqualifyReasons": [
          {
            "code": "D6102",
            "message": "This promotion has expired."
          }
        ]
      }
    },
    "analyticalInfo": {
      "REQUEST_PARSE_TIME": 0.442318,
      "DC_CALL_TIME": 635,
      "DC_TOTAL_TIME": 635,
      "TOTAL_E2E_TIME": 1034,
      "SAVM_TOTAL_TIME": 160,
      "CR_PROCESS_TIME": 233,
      "MDM_PROCESS_TIME": 47,
      "PCV_INVOCATION_TIME": 118,
      "BUNDLE_DISCOUNT_SERVICE_TIME": 153,
      "LPS_CALL_TIME": 99,
      "DEAL_DETAILS_LOG": 47,
      "PGTMV_PROCESS_TIME": 26,
      "CR_ES_INVOCATION_TIME": 232,
      "GEC_SVC_CALL_TIME": 15,
      "LPS_TOTAL_TIME": 100,
      "PREPROCESS_TIME": 3
    }
  }
}

I want to Read all fields from 65300, 230935, 231832 dynamic nested objects which are under discountDetail objects. Whys is it dynamic because these are run time generated values?

Answers

Neither JSON_TABLE and JSON_QUERY are supporting the PATH being a SQL expression, so you have to do it in PL/SQL to be able to use dynamic SQL.
This query will give you all the paths of the content of the "discountDetail" from which you will be able to construct the dynamic query returning the actual content:

select p,
    regexp_replace(p, '.*"([0-9]+)".*','1') as id
from 
    json_table(
        (select json_dataguide(js) from jsondata),
        '$[*]'
        columns (
            p PATH '$."o:path"'
        )
    )
where to_number(regexp_replace(p, '.*"([0-9]+)".*','1') default null on conversion error) is not null
;


$.responseHeader.discountDetail."65300" 65300
$.responseHeader.discountDetail."65300".discountId  65300
$.responseHeader.discountDetail."65300".listLineId  65300
$.responseHeader.discountDetail."65300".discountName    65300
$.responseHeader.discountDetail."65300".listHeaderId    65300
$.responseHeader.discountDetail."65300".discountGroup   65300
...
$.responseHeader.discountDetail."230935"    230935
$.responseHeader.discountDetail."230935".discountId 230935
$.responseHeader.discountDetail."230935".listLineId 230935
$.responseHeader.discountDetail."230935".discountName   230935
...

Use .* in the JSON path to get all keys for an object (and then, if necessary, you can filter for specific Ids outside the JSON path):

SELECT t.id,
       j.*
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$."responseHeader"."discountDetail".*'
         COLUMNS
           discount_id          VARCHAR2(10)  PATH '$.discountId',
           discount_name        VARCHAR2(100) PATH '$.discountName',
           discount_description VARCHAR2(100) PATH '$.discountDescription',
           discount_group       VARCHAR2(20)  PATH '$.discountGroup'
       ) j
WHERE  j.discount_id IN ('65300', '230935', '231832')

Which, for the (minimal) sample data:

CREATE TABLE table_name (
  id   NUMBER
       GENERATED ALWAYS AS IDENTITY
       PRIMARY KEY,
  data CLOB
       CHECK (data IS JSON)
);

INSERT INTO table_name (data)
VALUES (
  EMPTY_CLOB()
  || '{
  "responseHeader": {
    "discountDetail": {
      "65300": {
        "discountId": "65300",
        "discountName": "Distributor Standard Discount - USD",
        "discountDescription": "Standard - Distributor Standard Discount - USD",
        "discountGroup": "Standard"
      },
      "230935": {
        "discountId": "230935",
        "discountName": "BR-Deal Registration-USD",
        "discountDescription": "Promotion-BR-Hunt-210731-10660",
        "discountGroup": "Promotion"
      },
      "231832": {
        "discountId": "231832",
        "discountName": "BR-Special Offers-USD",
        "discountDescription": "Promotion-BR-MSSB-220730-12374",
        "discountGroup": "Promotion"
      }
    }
  }
}');

Outputs:

ID	DISCOUNT_ID	DISCOUNT_NAME	DISCOUNT_DESCRIPTION	DISCOUNT_GROUP
1	65300	Distributor Standard Discount – USD	Standard – Distributor Standard Discount – USD	Standard
1	231832	BR-Special Offers-USD	Promotion-BR-MSSB-220730-12374	Promotion
1	230935	BR-Deal Registration-USD	Promotion-BR-Hunt-210731-10660	Promotion

fiddle

Please signup or login to give your own answer.

Click here to cancel reply.