I have a clob column in my 12.1.0.2 db that contains a valid JSON string. Doing some testing of parsing of the string and running into an issue with a nested path. My columns contains the following JSON:
{
"DQM_Data": {
"plant_id": "3001",
"transmit_time": "2016-05-25 14:31:06",
"points_in_queue": 1,
"messages": [
{
"work_event": {
"msg_time": "2016-05-25 14:30:55",
"vert_correction": 0.6,
"ch_latitude": 43.713064,
"ch_longitude": -91.263719,
"ch_depth": 8.5,
"ch_heading": 58,
"slurry_velocity": 14.9,
"slurry_density": 1.02,
"pump_rpm": 458,
"vacuum": 4.1,
"outlet_psi": 91
}
}
]
}
}
This is my SQL. At first, my query would have an empty column for WE_CH_LATITUDE. I then added "error on error" and I get "ORA-40462: JSON_VALUE evaluated to no value". So either I'm parsing it wrong or something else is going on and I can't figure out what it is.
SELECT
AB.PLANT_IDENTIFIER,
TO_DATE(AB.TRANSMIT_TIME,'YYYY-MM-DD HH24:MI:SS') TRANSMIT_TIME,
AB.WE_CH_LATITUDE
FROM
SJM_TEMP5 R,
JSON_TABLE(CONTENT, '$' error on error
COLUMNS (PLANT_IDENTIFIER NUMBER PATH '$.DQM_Data.plant_id',
TRANSMIT_TIME VARCHAR2(40) PATH '$.DQM_Data.transmit_time',
NESTED PATH '$.DQM_Data.messages.work_event' COLUMNS (
WE_CH_LATITUDE NUMBER PATH '$.ch_latitude'))) AB
Any help is appreciated on this. Thanks in advance