Hi,
I have some JSON data returned from a vendor system which I need to store in the database. I want to be able to extract the processing error strings (attachment_67420210420-54083-1uaig73.csv and Malformed CSV) and store them in a table.
I've come to a dead end trying different approaches and would appreciate any suggestions:
The JSON:
'{
"id": 120,
"processing_errors": [
[
"attachment_67420210420-54083-1uaig73.csv",
"Malformed CSV"
]
]
}'
Attempt 1:
declare
l_response varchar2(4000) := '{
"id": 120,
"processing_errors": [
[
"attachment_67420210420-54083-1uaig73.csv",
"Malformed CSV"
]
]
}';
begin
dbms_output.put_line('id: '||json_value(l_response,'$.id'));
dbms_output.put_line('processing_errors: '||json_value(l_response,'$.processing_errors' default 'error' on error));
end;
/
Result:
id: 120
processing_errors: error
PL/SQL procedure successfully completed.
Attempt 2
create or replace type string_tbl_t as table of varchar2(2000);
declare
l_response varchar2(4000) := '{
"id": 120,
"processing_errors": [
[
"attachment_67420210420-54083-1uaig73.csv",
"Malformed CSV"
]
]
}';
l_string_tbl string_tbl_t ;
begin
dbms_output.put_line('id: '||json_value(l_response,'$.id'));
dbms_output.put_line('processing_errors: '||json_value(l_response,'$.processing_errors'));
SELECT value
bulk collect into l_string_tbl
FROM json_table(l_response, '$.processing_errors'
COLUMNS (value PATH '$'));
dbms_output.put_line('l_string_tbl.count: '||l_string_tbl.count);
FOR i IN l_string_tbl.FIRST..l_string_tbl.LAST LOOP
dbms_output.put_line('l_string_tbl['||i||']val: ' || l_string_tbl(i));
end loop;
end;
/
Result
id: 120
processing_errors:
l_string_tbl.count: 1
l_string_tbl[1]val:
PL/SQL procedure successfully completed.
The JSON is valid and when I test it here it displays the processing_errors tag twice along with its data.
Thanks in advance