We are on DB version 19.2. We are trying to use JSON data set very first time and have no experience working with it. We are reading the Oracle documentation related to JSON but found it very hard to follow and understand. We need help to query the data from JSON file that have elements that are part of Nested arrays in side the JSON objects. Any help is really appreciated. Thanks
Here is a JSON file for reference
{
"accountDetails": {
"accountName": "My Account",
"custodianAccount": "My AccountNumber",
"startDate": "220150615",
"endDate": "20230703",
"marketValue": {
"currencyCode": "CAD",
"amount": 737995.96
},
"performance": [
{
"periodLabel": "Inception-to-Date",
"twrrNof": 0.03523584423021675,
"twrrGof": 0.04128811745224015,
"periodStart": "20150616",
"periodEnd": "20230703"
},
{
"periodLabel": "Quarter-to-Date",
"twrrNof": 7.559920027098155,
"twrrGof": 7.559924692275288,
"periodStart": "20230701",
"periodEnd": "20230703"
}
],
},
"sleeves": {
"sleeveDetails": [
{
"sleeveName": "Sleeve1",
"inceptionDate": "20210318",
"marketValue": {
"currencyCode": "CAD",
"amount": 154312.67
},
"performance": [
{
"periodLabel": "Inception-to-Date",
"twrrNof": 1.59171883177918061,
"twrrGof": 0.09171883177918061,
"periodStart": "20210319",
"periodEnd": "20230703"
},
{
"periodLabel": "Quarter-to-Date",
"twrrNof": 1.503752211169898434,
"twrrGof": 5.53752211169898434,
"periodStart": "20230701",
"periodEnd": "20230703"
}
],
},
{
"sleeveName": "Sleeve2",
"inceptionDate": "20210825",
"marketValue": {
"currencyCode": "CAD",
"amount": 262423.86
},
"performance": [
{
"periodLabel": "Inception-to-Date",
"twrrNof": 2.53041295906685626,
"twrrGof": 0.03041295906685626,
"periodStart": "20210826",
"periodEnd": "20230703"
},
{
"periodLabel": "Quarter-to-Date",
"twrrNof": 2.4019374838543011474,
"twrrGof": 0.0019374838543011474,
"periodStart": "20230701",
"periodEnd": "20230703"
}
],
},
{
"sleeveName": "Sleeve3",
"inceptionDate": "20151008",
"marketValue": {
"currencyCode": "CAD",
"amount": 321259.43
},
"performance": [
{
"periodLabel": "Inception-to-Date",
"twrrNof": 3.5362634897972308,
"twrrGof": 5.562634897972308,
"periodStart": "20151009",
"periodEnd": "20230703"
},
{
"periodLabel": "Quarter-to-Date",
"twrrNof": 3.511802131625067158,
"twrrGof": 5.5011802131625067158,
"periodStart": "20230701",
"periodEnd": "20230703"
}
],
},
]
}
}
Expected Query output
here how we want our query output to look like when we read data from above JSON 
JSON file contains data for single account and its performance and 1 or more sleeves related to that account performance for those sleeves.
Output to JSON column mapping :
- accountName = accountDetails.accountName
- custodianAccount = accountDetails.custodianAccount
- accountMarketValue = accountDetails.marketValue.amount
- account_twrrNof_Inception-to-Date = accountDetails.performance.twrrNof (where periodLabel='Inception-to-Date')
- account_twrrNof_Quarter-to-Date = accountDetails.performance.twrrNof (where periodLabel='Quarter-to-Date')
- sleeveName = sleeves.sleeveDetails.sleeveName
- sleeveMarketValue = sleeves.sleeveDetails.marketValue.amount
- sleeve_twrrNof_Inception-to-Date = sleeves.sleeveDetails.performance.twrrNof (where periodLabel='Inception-to-Date')
- sleeve_twrrNof_Quarter-to-Date = sleeves.sleeveDetails.performance.twrrNof (where periodLabel='Quarter-to-Date')
Note ,
- for columns account_twrrNof_
I have kept only 2 columns here for simplicity purpose , but there 8 different performance number for account , so when we read value for twrrNof, we also need to know which time period it is for, for example mapping it with periodLabel='Inception-to-Date' means this twrrNof value is account_twrrNof_Inception-to-Date.
There is only 1 account expected in the JSON file.
- for columns sleeve_twrrNof_
Same like account level performance, I have kept only 2 columns here for simplicity purpose , but there 8 different performance number for each sleeve, so when we read value for twrrNof, we also need to know which time period it is for, for example mapping it with periodLabel='Inception-to-Date' means this twrrNof value is sleeve_twrrNof_Inception-to-Date.
also account may have 1 or more sleeves in the file , so there may be at least 1 or many sleeves and related performance in the JSON file.