Hierarchal JSON to oracle table

Question

I have a JSON object stored in a CLOB that represents a hierarchy:

{
    "Version": 1,
    "nodes": [{
            "id": 0,
            "Fields": ["ABC"],
            "nodes": [{
                    "id": 1,
                    "Fields": ["DEF"],
                    "nodes": [{
                            "id": 2,
                            "Fields": ["GHI", "HIG"],
                            "nodes": []
                        }
                    ]
                }, {
                    "id": 3,
                    "Fields": ["XYZ", "YZX"],
                    "nodes": [{
                            "id": 4,
                            "Fields": ["UVW"],
                            "nodes": [{
                                    "id": 5,
                                    "Fields": ["RST"],
                                    "nodes": []
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}

I need to represent this as a table:

Ver  id    Field   Parent
  1   0    ABC       null
  1   1    DEF          0
  1   2    GHI|HIG      1
  1   3    XYZ|YZX      0
  1   4    UVW          3
  1   5    RST          4

I've tried using PL/JSON. While I am able to find individual points of data, I'm runnning into difficulties with the hierarchal nature.

APEX (and 12c) is not an option.

Can anyone help with this?

Answer 1

I write jSON parser with pl\sql. Could you please check and take it of you need.

declare 
  type t_str_rec is table of varchar2(4000); 
  type t_str_str is table of t_str_rec; 
  l_tab t_str_str := t_str_str(); 
  l_count number := 0; 
  procedure get_number (p_value clob, 
                        p_offset out number,  
                        p_number out varchar2) is  
    l_cur_var  varchar2(1 char); 
  begin  
    p_offset := 1; 
    l_cur_var := dbms_lob.substr(p_value,1,1);  
    while l_cur_var IN ('0','1','2','3','4','5','6','7','8','9') loop 
      l_cur_var := dbms_lob.substr(p_value,1,p_offset);  
      p_offset := p_offset + 1; 
    end loop; 
    p_number := dbms_lob.substr(p_value,p_offset-2,1); 
    p_offset := p_offset-2;  
--    dbms_output.put_line('get_number:'||p_offset); 
  end;  
  procedure get_q_string (p_value clob, 
                          p_offset out number,  
                          p_string out varchar2) is  
    l_cur_pos number; 
    l_cur_var  varchar2(1 char); 
    l_count_quotes number := 0; 
    l_is_end  number := 0; 
  begin  
    p_offset := 1; 
    while l_is_end = 0 loop 
      p_offset := dbms_lob.instr(p_value,'"',p_offset + 1); 
      l_cur_var := dbms_lob.substr(p_value,1,p_offset-1);  
      while l_cur_var = '\' loop  
        l_count_quotes := l_count_quotes +1; 
        l_cur_pos := l_cur_pos-1;  
        l_cur_var := dbms_lob.substr(p_value,1,l_cur_pos-1);  
      end loop; 
      if(mod(l_count_quotes,2) = 0) then  
         l_is_end := 1; 
      end if; 
    end loop; 
    p_string := dbms_lob.substr(p_value,p_offset-2,2); 
--    dbms_output.put_line('get_q_string:'||p_offset|| ':'||length(p_string) ); 
  end;  
  procedure get_one_bracket(p_value clob, 
                            p_bracket varchar2 := '[]', 
                            p_offset out number) is 
    l_left varchar2(1 char) := substr(p_bracket,1,1); 
    l_right varchar2(1 char) := substr(p_bracket,2,1); 
    l_cur_pos number; 
begin 
    l_cur_pos := 1; 
    p_offset := dbms_lob.instr(p_value,l_right); 
    l_cur_pos := dbms_lob.instr(p_value,l_left,l_cur_pos+1); 
    while l_cur_pos > 0 and l_cur_pos < p_offset loop 
        l_cur_pos := dbms_lob.instr(p_value,l_left,l_cur_pos+1); 
        p_offset  := dbms_lob.instr(p_value,l_right,p_offset+1); 
        if p_offset < 1 then  
            p_offset := dbms_lob.getlength(p_value); 
            exit; 
        end if; 
    end loop; 
--    p_offset := p_offset-1; 
--    dbms_output.put_line('get_one_bracket:'|| p_bracket||':'||p_offset ); 
  end; 
  procedure parse_json (p_clob   clob 
                      , p_parent number) is 
    l_pos   number := 1;  
    l_offset number := 1; 
    l_length number := dbms_lob.getlength(p_clob); 
    l_cur_char  varchar2(1 char); 
    l_rec  t_str_rec := t_str_rec();  
    l_parent number := p_parent;
  begin 
    while(l_pos < l_length) loop 
      l_cur_char := dbms_lob.substr(p_clob, 1,l_pos);
      if l_cur_char = '"' then  
          l_rec.extend(); 
          get_q_string(dbms_lob.substr(p_clob, l_length-l_pos, l_pos), 
                       l_offset, 
                       l_rec(l_rec.last)); 
      elsif l_cur_char = ':' then null; -- it's just a separator  
      elsif l_cur_char IN ('0','1','2','3','4','5','6','7','8','9') then 
        l_rec.extend(); 
        get_number (dbms_lob.substr(p_clob, l_length-l_pos, l_pos), 
                    l_offset,  
                    l_rec(l_rec.last)); 
      elsif l_cur_char = '{' then  
        l_rec.extend(); 
        l_rec(l_rec.last) := '{}'; 
        get_one_bracket(dbms_lob.substr(p_clob, l_length-l_pos+1, l_pos), 
                        '{}', 
                        l_offset); 
        declare 
          l_id number := l_count; 
        begin 
          parse_json (dbms_lob.substr(p_clob, l_offset, l_pos+1),  
                      l_id); 
        end; 
      elsif l_cur_char = '[' then  
        l_rec.extend(); 
        l_rec(l_rec.last) := '[]'; 
        get_one_bracket(dbms_lob.substr(p_clob, l_length-l_pos+1, l_pos), 
                        '[]', 
                        l_offset); 
        l_rec.extend(); 
        l_count := l_count + 1;  
        l_rec(l_rec.last) := l_count; 
        l_rec.extend(); 
        l_rec(l_rec.last) := l_parent; 
        l_tab.extend; 
        l_tab(l_tab.last) := l_rec;  
        l_rec := t_str_rec(); 
        declare 
          l_id number := l_count; 
        begin 
          parse_json (dbms_lob.substr(p_clob, l_offset, l_pos+1),  
                      l_id); 
        end;
      elsif l_cur_char = ',' then
        IF dbms_lob.substr(p_clob, 1,l_pos-1) NOT IN (']','}',',') then
          l_rec.extend(); 
          l_count := l_count + 1;  
          l_rec(l_rec.last) := l_count; 
          l_rec.extend(); 
          l_rec(l_rec.last) := l_parent; 

          l_tab.extend; 
          l_tab(l_tab.last) := l_rec;  

          l_rec := t_str_rec(); 
        end if;
      end if;  
      l_pos := l_pos+l_offset; 
      l_offset := 1; 
    end loop;
    l_rec.extend(); 
    l_count := l_count + 1;  
    l_rec(l_rec.last) := l_count; 
    l_rec.extend(); 
    l_rec(l_rec.last) := l_parent; 
    l_tab.extend; 
    l_tab(l_tab.last) := l_rec;  
    l_rec := t_str_rec(); 
  end; 
begin 
    parse_json( 
'{ 
    "Version": 1, 
    "nodes": [{ 
            "id": 0, 
            "Fields": ["ABC"], 
            "nodes": [{ 
                    "id": 1, 
                    "Fields": ["DEF"], 
                    "nodes": [{ 
                            "id": 2, 
                            "Fields": ["GHI", "HIG"], 
                            "nodes": [] 
                        } 
                    ] 
                }, { 
                    "id": 3, 
                    "Fields": ["XYZ", "YZX"], 
                    "nodes": [{ 
                            "id": 4, 
                            "Fields": ["UVW"], 
                            "nodes": [{ 
                                    "id": 5, 
                                    "Fields": ["RST"], 
                                    "nodes": [] 
                                } 
                            ] 
                        } 
                    ] 
                } 
            ] 
        } 
    ] 
}',l_count); 
    declare 
      v_vers varchar2(10);
      v_id    varchar2(10) ;
      v_field  varchar2(4000);
      v_del varchar2(1); 
      v_is_field  number;
      v_parent varchar2(10);
      type t_num_decode is table of varchar2(10) index by varchar2(10);
      l_num_decode t_num_decode;
    begin 
      for ba in l_tab.first .. l_tab.last loop 
          if v_vers is null and l_tab(ba)(1) = 'Version' then
            v_vers := l_tab(ba)(2); 
          end if;
          if l_tab(ba)(1) = 'id' then
            v_id := l_tab(ba)(2); 
          elsif l_tab(ba)(1) = 'Fields' then
            v_is_field := 1;
            v_field := '';
          elsif l_tab(ba)(1) = 'nodes' and v_id is not null then
              if l_num_decode.exists(l_tab(ba)(4)) then 
                v_parent := l_num_decode(l_tab(ba)(4));
              end if; 
              dbms_output.put_line(v_vers||','||v_id||','||v_field||','||nvl(v_parent,'null'));
              l_num_decode(l_tab(ba)(3)) := v_id;
              v_is_field := 0;
              v_del := '';
          elsif v_is_field = 1 then
            v_field := v_field||v_del||l_tab(ba)(1);
            v_del := '|';
          end if; 
      end loop;
    end;
end;
/