Convert string to JSON object using Oracle PL/SQL

Question

I have a "input.json" file in oracle data directory. I can read the file in my PL/SQL code using the UTL_FILE command. Now these are in a string format and I would like to convert it to a JSON string and parse them all using a PL/SQL block. I am using oracle 12.2.

This is my JSON input file contents:

{
    "CAR":["%HON%","%UZU%"],
    "NAME":["%RAY%","%OE%"];
}

Create or Replace procedure TEST1 as
    fHandle   UTL_FILE.FILE_TYPE;
    s varchar(200);
    -- begin Reading of code
BEGIN
     fHandle := UTL_FILE.FOPEN('DISCOVERY', 'input.json', 'r');
     Loop
         UTL_FILE.get_line(fHandle,s);
         dbms_output.put_line(s);
     end loop; 
     UTL_FILE.fclose(fHandle);
END;

excpected output is a valid JSON string

CAR:"%HON%"
CAR:"%UZU%"
NAME:"%RAY%"
NAME:"%OE%"

Answer 1

You could:

• Create an external table to read the file
• Use JSON_table to convert the document to relational rows-and-columns

Which looks a little like:

/* Create the file */
create or replace directory tmp as '/tmp';
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'input.json', 'w');
  utl_file.put_line ( f, '{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }');
  utl_file.fclose(f);
end;
/

create table json_ext (
  json_doc varchar2(100)
) organization external (
  default directory tmp
  access parameters (
    records delimited by newline
    fields (
      json_doc char(1000)
    )
  )
  location ( 'input.json' )
);

select * from json_ext;

JSON_DOC                                               
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] } 

select * 
from   json_ext, 
       json_table (
         json_doc, '$'
         columns (
           nested path '$.CAR[*]' columns (
             CAR path '$'
           ),
           nested path '$.NAME[*]' columns (
             NAME path '$'
           )
         )
       );

JSON_DOC                                                CAR       NAME     
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }    %HON%     <null>    
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }    %UZU%     <null>    
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }    <null>    %RAY%     
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }    <null>    %OE%   

This splits each array into its own set of rows and columns. To get this as a single list of attribute names and array values, you can unpivot the results:

with rws as (
  select j.* 
  from   json_ext, 
         json_table (
           json_doc, '$'
           columns (
             nested path '$.CAR[*]' columns (
               CAR path '$'
             ),
             nested path '$.NAME[*]' columns (
               NAME path '$'
             )
           )
         ) j
) 
  select * from rws 
  unpivot ( 
    val for attr in ( CAR, NAME )
  );

ATTR    VAL     
CAR     %HON%    
CAR     %UZU%    
NAME    %RAY%    
NAME    %OE%  

Answer 2

This requires looping through the JSON elements and then through it's array elements. Ideas borrowed from this Post

SET SERVEROUTPUT ON
DECLARE
     l_json CLOB := '{
    "CAR" :["%HON%","%UZU%"],
    "NAME":["%RAY%","%OE%" ]
}';
     l_json_obj json_object_t;
     l_keys json_key_list;
     l_arr json_array_t;
     elem json_element_t;
BEGIN
     l_json_obj := json_object_t(l_json);
     l_keys := l_json_obj.get_keys;
     FOR i IN 1..l_keys.count LOOP
          l_arr :=  l_json_obj.get_array(l_keys(i));
          FOR j IN 0..l_arr.get_size - 1 LOOP
               elem := l_arr.get(j);
               dbms_output.put(l_keys(i)
                               || ':');
               dbms_output.put_line(elem.stringify);
          END LOOP;

     END LOOP;

END;
/

Result

CAR:"%HON%"
CAR:"%UZU%"
NAME:"%RAY%"
NAME:"%OE%"

Answer 3

You can do this using regular expressions:

Create or Replace procedure TEST1 as
  fHandle   UTL_FILE.FILE_TYPE;
  s varchar(200);
BEGIN
  fHandle := UTL_FILE.FOPEN('DISCOVERY', 'input.json', 'r');

  WHILE TRUE LOOP
    BEGIN
      UTL_FILE.get_line(fHandle, s);

      IF s <> '{' AND s <> '}' THEN
        FOR aRow IN (SELECT REGEXP_SUBSTR(s, '[^:]*', 1, 1) AS COL1,
                            REGEXP_SUBSTR(s, '"%[^,]*%"', 1, 1) AS COL2,
                            REGEXP_SUBSTR(s, '"%[^,]*%"', 1, 2) AS COL3
                       FROM DUAL)
        LOOP
          DBMS_OUTPUT.PUT_LINE(COL1 || ':' || COL2);
          DBMS_OUTPUT.PUT_LINE(COL1 || ':' || COL3);
        END LOOP;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
  end loop; 

  UTL_FILE.fclose(fHandle);
END TEST1;