Hi,
I am trying to process the following JSON using JSON_TABLE function and try to unpack and load into the database. There will be three nested for loops to go to the last level and start inserting the data. But I can't see the contents of trend types which is the data column. Check the code below:
declare
sample_json varchar2 (32767) := '{
"trends" : [
{
"type" : "Financials",
"data" : [{"category" : "Expenses",
"trend": [{"date": "31-DEC-2010", "value": "1500000"},
{"date": "31-DEC-2011", "value": "1700000"},
{"date": "31-DEC-2012", "value": "1830000"},
{"date": "31-DEC-2013", "value": "1970000"},
{"date": "31-DEC-2014", "value": "1990000"},
{"date": "31-DEC-2015", "value": "2050000"},
{"date": "31-DEC-2016", "value": "2000000"},
{"date": "31-DEC-2017", "value": "1900000"},
{"date": "31-DEC-2018", "value": "2100000"},
{"date": "31-DEC-2019", "value": "2150000"}]},
{"category" : "assets",
"trend": [{"date": "31-DEC-2010", "value": "7890000"},
{"date": "31-DEC-2011", "value": "9990000"},
{"date": "31-DEC-2012", "value": "10500000"},
{"date": "31-DEC-2013", "value": "11700000"},
{"date": "31-DEC-2014", "value": "15500000"},
{"date": "31-DEC-2015", "value": "19850000"},
{"date": "31-DEC-2016", "value": "25700000"},
{"date": "31-DEC-2017", "value": "25900000"},
{"date": "31-DEC-2018", "value": "27900000"},
{"date": "31-DEC-2019", "value": "29800000"}]}
]
},
{
"type" : "Customers",
"data" : [{"category" : "Count",
"trend": [{"date": "31-DEC-2010", "value": "2000"},
{"date": "31-DEC-2011", "value": "2500"},
{"date": "31-DEC-2012", "value": "2630"},
{"date": "31-DEC-2013", "value": "2700"},
{"date": "31-DEC-2014", "value": "1900"},
{"date": "31-DEC-2015", "value": "1800"},
{"date": "31-DEC-2016", "value": "1840"},
{"date": "31-DEC-2017", "value": "1950"},
{"date": "31-DEC-2018", "value": "2100"},
{"date": "31-DEC-2019", "value": "2170"}]}
]
}
]
}';
begin
--First for loop to process the contents of the trends element
for i in (select trend_type, trend_data
from json_table(sample_json, '$.trends[*]'
columns trend_type varchar2 path '$.type',
trend_data varchar2 path '$.data'
)) loop
dbms_output.put_line(i.trend_type||' -> '||nvl(i.trend_data,'NULL'));
--Second for loop to process the contents of the data element
---Last for loop to process to the contents of the trend element inside data element
end loop;
end;
/
At the moment, the code returns NULL for the trend_data column. Any idea of what might be wrong in the code? Besides, do you think it is the best way to process this data using nested loops?
I will appreciate any help or guidance.
Many thanks and best regards,
Bilal