CODEWITHSUNDEEP
jsonoracle-databaseplsql
Bartosz
Bartosz

Reputation: 1820

PLSQL- Walking thru JSON structure without knowing element names

Used Database: Im using Oracle 19c database, so i tried to use JSON functions declared already in PLSQL (for instance JSON_TABLE) to import JSON inside database table.

What im doing:

Im just calling API, getting JSON from it, and then i would like to import data inside the database, regardless of what data, and in what structure they came.

Problem:

I would like to iterate JSON data without knowing element names inside that JSON. I would like to know where im actually am (name of current node), and names of child elements, so i could dynamically create tables from those names, add relations between them, and import all data.

What i have tried:

So far i was doing it manually- i had to create tables by myself. Importing data required knowledge of object names, and also knowledge of JSON structure that i want to import. And its working, but oh well... i would like to create something more universal. All this stuff had to be done, because i dont know any way to walk thru structure of JSON without knowing names of objects and generally- entire JSON structure.

Any ideas how to walk thru json structure, without knowing object names and relations between them?

Upvotes: 0

Views: 792

Answers (1)

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

Learn the new PL/SQL JSON data structures JSON Data Structures

procedure parse_json(p_json in blob) is
  l_elem json_element_t := json_element_t.parse(p_json);
  l_obj json_object_t;
  l_arr json_array_t;
  l_keys json_key_list;
begin
  case
    when l_elem.is_Object then 
      l_obj := treat(l_elem as json_object_t);
      l_keys := l_obj.get_Keys;
      for i in 1..l_keys.count loop
        //work with the keys
        if l_obj.get(l_keys(i)).is_object then
          // this key is object,
        end if;
        if l_obj.get(l_keys(i)).is_array then
          // this key is array, 
        end if;
      end loop;
    when l_elem.is_Array then
      l_arr := treat(l_elem as json_array_t);
      for i in 0..l_arr.get_size - 1 loop
        // work with array
        case l_arr..get_type(i) 
          when 'SCALAR' then
             if l_arr.get(i).is_string then
             if l_arr.get(i).is_number then
             if l_arr.get(i).is_timestamp then
             if l_arr.get(i).is_boolean then
          .....
          when 'OBJECT' then 
            ....
          when 'ARRAY' then
            ....
          end case;
      end loop;
    end case; 
end parse_json;

You can also use the truly helpful JSON Data Guide and the DBMS_JSON package to map out the json object for you and even automatically create a view using JSON_TABLE.

Regards

Upvotes: 1

Related Questions