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?
2
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:
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):Which, for the (minimal) sample data:
Outputs:
fiddle