I understand the basics of JSON but I'm struggling with what is needed for a task at hand. The logic is interacting with a remote web service that is returning JSON. I'm trying to extract the latest document ID from their response and I'm unclear on how. I have the following which returns all the data, but I'm not sure I've even started down the right path.
with json_data as (select '{
"resultRows": [
{
"fields": [
{
"columnId": "6",
"value": "Registration Packet"
},
{
"columnId": "App_Internal_Col_ID",
"value": "1617084000000"
},
{
"columnId": "8",
"value": "Internal_Doc_ID_1"
},
{
"columnId": "39",
"value": ""
}
]
},
{
"fields": [
{
"columnId": "6",
"value": "Registration Packet"
},
{
"columnId": "App_Internal_Col_ID",
"value": "1617105600000"
},
{
"columnId": "8",
"value": "Internal_Doc_ID_2"
},
{
"columnId": "39",
"value": ""
}
]
},
{
"fields": [
{
"columnId": "6",
"value": "Registration Packet"
},
{
"columnId": "App_Internal_Col_ID",
"value": "1633068000000"
},
{
"columnId": "8",
"value": "Internal_Doc_ID_3"
},
{
"columnId": "39",
"value": ""
}
]
}
],
"hasMore": false
}' rsp_data from dual
)
select jt.*
from json_data jd,
json_table(jd.rsp_data,
'$.resultRows.fields[*]'
columns(
col_id varchar2(40) path '$.columnId',
col_val varchar2(40) path '$.value')) jt;
The desired output of this query is
Internal_Doc_ID_3
because the sibling object App_Internal_Col_ID has the largest value. The remote web service does not clarify that the desired entry will be in the last fields array. The solution needs to run on Oracle 18c.