I working on parsing a json string stored in a table CLOB in oracle 11g. This process is part of a long parsing routine that parses the data and stores the values in another table and I've just noticed that part of my data is not getting out. The json parses and validates with JSONLint. So I've simplified the parsing to try and find out where I'm going wrong.
So my json coming out my table looks like this.
{
"JSON_data": {
"plant_id": "3006",
"transmit_time": "2015-12-18 11:57:45",
"messages": [{
"work_msg": {
"msg_time": "2015-06-23 04:54:17",
"trigger_type": "interval",
"vert_correction": 358.3,
"ch_latitude": 37.916302,
"ch_longitude": -87.487365,
"ch_heading": 212.3,
"ch_cable_port": 1029.79,
"ch_cable_stbd": 348.63,
"ch_depth": -27.03,
"slurry_velocity": 25.71,
"slurry_density": 1.02,
"ch_rpm": 205.49,
"ch_psi": 540.89,
"prod_instantaneous": 0,
"prod_cumulative": 1216.100000,
"outfall_latitude": 37.915967,
"outfall_longitude": -87.484369,
"outfall_heading": 120.7,
"pump_entries": [{
"pump_name": "main",
"vacuum": 12.73,
"outlet_psi": 22.88
}],
"spud_entries": [{
"position": 6
}]
},
"pipe_length_event": {
"msg_time": "2015-06-23 04:54:17",
"length_floating": 970
}
}]
}
}
My parsing is correctly finding and doing its thing with the 'work_msg' data. It's the 'pipe_length_event' data that I'm not getting to. Below is my simplified pl/sql procedure.
DECLARE
vCONTENT CLOB;
v_parent_json json;
v_json_message_list json_list;
v_json_message_list_value json_value;
v_parent_json_value json_value;
BEGIN
SELECT CONTENT INTO vCONTENT FROM SJM_TEMP4;
v_parent_json := json(vCONTENT);
v_parent_json := json(v_parent_json.get(1));
v_json_message_list := json_list(v_parent_json.get('messages'));
DBMS_OUTPUT.PUT_LINE(v_json_message_list.count);
for message_loop_counter in 1 ..v_json_message_list.count loop
v_parent_json_value := json(v_json_message_list.get(message_loop_counter)).get(1);
if v_parent_json_value.mapname = 'work_msg' then
DBMS_OUTPUT.PUT_LINE('FOUND: work_msg');
else
DBMS_OUTPUT.PUT_LINE(v_parent_json_value.mapname);
end if;
END LOOP;
END;
My dbms_output first gives me a sub-list count of 1. Not 2 so my parsing is not even recognizing the "pipe_length_event" as a sub-list of "messages".
How do I get "pipe_length_event" data using this procedure? I'm almost certain this was working in the past so my first thought is that the json is formatted differently. Is the json ill-formatted?
Thanks in advance.