Hi Tom,
This is a part of a JSON file i'm trying to parse.I'm getting the first value extracted using this
json_value(po_xml,'$.schedulingDetails.schedulingDetail[0].actualSchedule.selectedAppointmentDate')
Its pulling the value 2035-09-09 correctly.But the second selectedAppointmentDate value is not getting pulled and its always null.Can you please help?
{ "schedulingDetails":{
"schedulingDetail":[
{
"id":"SCHEDULINGINFO_02",
"nameReference":"NAME_01",
"actualSchedule":{
"selectedAppointmentDate":"2035-09-09"
},
"installType":"TECH",
"nffl":{
"nfflFlag":true
}
},
{
"id":"SCHEDULINGINFO_01",
"nameReference":"NAME_01",
"actualSchedule":{
"selectedAppointmentDate":"2019-06-10",
"selectedAppointmentTime":"09:00:00",
"startTime":"09:00 AM",
"endTime":"11:00 AM",
"workOrderId":"S9155036224"
},
"installType":"TECH",
"preference":"FIRSTAVAILABLE_TIMESLOT"
}
]
}
}
If you use json_table, you can pass arrays as a nested path. This converts each array element into a new row.
So if you pass '$.schedulingDetail[*]' in the nesting, list out the path to the elements you want within this:
with jdata as (
select '{
"schedulingDetails": {
"schedulingDetail": [
{
"id": "SCHEDULINGINFO_02",
"nameReference": "NAME_01",
"actualSchedule": {
"selectedAppointmentDate": "2035-09-09"
},
"installType": "TECH",
"nffl": {
"nfflFlag": true
}
},
{
"id": "SCHEDULINGINFO_01",
"nameReference": "NAME_01",
"actualSchedule": {
"selectedAppointmentDate": "2019-06-10",
"selectedAppointmentTime": "09:00:00",
"startTime": "09:00 AM",
"endTime": "11:00 AM",
"workOrderId": "S9155036224"
},
"installType": "TECH",
"preference": "FIRSTAVAILABLE_TIMESLOT"
}
]
}
}' doc
from dual
)
select t.*
from jdata, json_table (
doc, '$.schedulingDetails'
columns (
nested path '$.schedulingDetail[*]'
columns (
id varchar2 path '$.id',
dt varchar2 path '$.actualSchedule.selectedAppointmentDate'
)
)
) t;
ID DT
SCHEDULINGINFO_02 2035-09-09
SCHEDULINGINFO_01 2019-06-10