Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help parsing JSON array string

Brenden AnsteyApr 21 2021 — edited Apr 21 2021

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

This post has been answered by MaxOrgiyan-Oracle on Apr 22 2021
Jump to Answer
Comments
Post Details
Added on Apr 21 2021
3 comments
1,134 views