I'm using PL/JSON on Oracle 11.2.0.1. My JSON is in a clob. I think I've been looking at it for too long and I'm getting lost trying to get past the first array level. The JSON I'm pulling out of the table is below.
{
"JSON_Data": {
"Plant_id": 2901,
"Transmit_time": "2016-02-08T01:01:01.627Z",
"Messages": [
{
"Work_event_message": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"trigger_type": "interval",
"Vert_correction": 287.2,
"Ch_latitude": 37.102494,
"Ch_longitude": -89.129392,
"Ch_depth": 123.32,
"Target_depth": 10,
"Survey_depth": 45.54,
"Pump_name": "Slurry Pump Location",
"Vacuum": 89.98,
"Outlet_psi": 63.36,
"Slurry_velocity": 74.47,
"Prod_cumulative": 100,
"Comment": "Work Event Comment"
},
{
"State_messages": {
"Station_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Station_name": "Test Station Name",
"Comment": "State Message Comment"
}
],
"Booster_pump_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Pump_name": "Test Pump Name",
"Event_type": "Add",
"Comment": "Booster Pump Event Comments"
}
],
"Pipe_length_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Length_floating": 6,
"Length_submerged": 7,
"Length_land": 8,
"Comment": "Pipe Length Comment"
}
],
"Outfall_position": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Outfall_latitude": 30.695366,
"Outfall_longitude": -88.039891,
"Outfall_heading": 259,
"Outfall_elevation": 876.67,
"Comment": "Outfall Position Comment"
}
],
"Contract_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Contract_number": "456-ABC-784512",
"Event_type": "Start",
"Comment": "Contract Event Comment"
}
],
"Scow_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Scow_name": "Scow Name",
"Event_type": "Event Type",
"Comment": "Scow Event Comment"
}
],
"Operator_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Operator_name": "Operator Name",
"Comment": "Operator Event Comment"
}
],
"Non_eff_event": [
{
"Msg_time": "2016-02-08T01:01:01.627Z",
"Function_code": "ab01",
"Comment": "Non Eff Event Comment"
}
],
"Nullification_event": [
{
"Nullified_st_time": "2016-02-08T01:01:01.627Z",
"Nullified_msg_type": "yz99"
}
]
}
}
]
}
]
}
}
I'm having trouble getting to the subjects and arrays at the "State_messages" level. My PL/SQL that I have so far is below. So far I've only successfully gotten to the data within the "work_event_message" section. Can someone give me some insight on how to successfully navigate to the "state_messages" and below sections? I've also posted on StackOverflow but haven't gotten anything yet. I'm going bonkers....
DECLARE
vCONTENT CLOB;
vJSON_OBJ json;
vJSON_LIST json_list;
vJSON_VALUE JSON_VALUE;
v_parent_json JSON;
BEGIN
SELECT CONTENT INTO vCONTENT FROM SJM_TEMP4 WHERE ID = 2;
vJSON_OBJ := json(vCONTENT);
v_parent_json := json(vJSON_OBJ.get(1));
vJSON_LIST := json_list(v_parent_json.get('Messages'));
dbms_output.put_line(vJSON_LIST.count);
--WORK_EVENT_MESSAGE
for message_loop_counter in 1 ..vJSON_LIST.count loop
vJSON_VALUE := json(vJSON_LIST.get(message_loop_counter)).get(1);
dbms_output.put_line(lower(vJSON_VALUE.mapname));
end loop;
END;