Sample code to parse JSON in Oracle PL/SQL using JSON_OBJECT_T

JSON_Parsing_JSON_OBJECT_T.sql

DECLARE

  lc_json_file_url      CONSTANT VARCHAR2(500) := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/nueva/b/nueva-public/o/Blog%20Public%20Files%2Fjson_parsing%2Fsample.json';
  l_json                CLOB;
  l_orders_obj          JSON_OBJECT_T;
  l_orders_arr          JSON_ARRAY_T;
  l_order_obj           JSON_OBJECT_T;
  l_lines_arr           JSON_ARRAY_T;
  l_order_date          TIMESTAMP WITH TIME ZONE;
  l_ship_date           TIMESTAMP WITH TIME ZONE;
  l_keys                JSON_KEY_LIST;
  l_keys_str            VARCHAR2(32000);
  
  -- Function to convert an ISO 8601 Date String (e.g. "2022-08-04T08:00:06.000Z"
  --   to an Oracle TIMESTAMP WITH TIME ZONE data type. This is only needed because
  --   Oracle does not provide a PL/SQL equivelent for TO_UTC_TIMESTAMP_TZ
  FUNCTION convert_iso_date (p_date IN VARCHAR2) RETURN TIMESTAMP WITH TIME ZONE IS
    l_date  TIMESTAMP WITH TIME ZONE;
  BEGIN
    SELECT TO_UTC_TIMESTAMP_TZ (p_date) INTO l_date
    FROM sys.dual;
    RETURN l_date;
  END convert_iso_date;

BEGIN

  -- Get the JSON Document from OCI.
  l_json := apex_web_service.make_rest_request
             (p_url => lc_json_file_url,
              p_http_method => 'GET');

  -- Parse the JSON into a JSON Object Type.
  l_orders_obj := json_object_t.parse(l_json);
  -- Disable error handling (the default).
  l_orders_obj.On_error(0);
  
  -- Get the numeric value for 'order_count' from the 'meta' object
  dbms_output.put_line('Value of [order_count] in the [meta] object: ' ||
    l_orders_obj.get_Object('meta').get_Number('order_count'));
  
  -- Get the 'orders' array from the JSON
  l_orders_arr := l_orders_obj.get_Array('orders');
  dbms_output.put_line('Count of rows in the [orders] array: '||
    l_orders_arr.get_size);
  
  -- Loop Through 'orders' array. 
  -- *** Note: The Loop Index for JSON_OBJECT starts at 0 NOT 1 ***
  FOR i IN 0..l_orders_arr.get_size -1 LOOP

    -- Get the order object for the for the current order in the 'orders' array
    l_order_obj := json_object_t(l_orders_arr.get(i));
    dbms_output.put_line(' [order_number]: ' || l_order_obj.get_Number('order_number'));

    -- Output the fields, objects and arrays directly accessible from the Order Object.
    -- This could be useful if you do not always know what fields could be in the JSON.
    l_keys := l_order_obj.get_keys;
    FOR x IN 1..l_keys.COUNT LOOP
      l_keys_str := l_keys_str || l_keys(x) || ',';
    END LOOP;
    dbms_output.put_line('  Order Keys: [' || l_keys_str || ']') ;
    
    -- Convert the ISO8601 to TIMESTAMP WITH TIME ZONE assumes the time is in the UTC time zone.
    l_order_date := convert_iso_date (p_date => l_order_obj.get_String('order_date'));
    dbms_output.put_line('  [order_date]: '||
      TO_CHAR(l_order_date, 'DD-MON-YYYY HH24:MI:SS TZH:TZM'));

    -- Check if the optional field 'ship_date' exists in the order object.
    IF l_order_obj.has('ship_date') THEN
      l_ship_date := convert_iso_date (p_date => l_order_obj.get_String('ship_date'));
      dbms_output.put_line('  [ship_date] : '||
        TO_CHAR(l_ship_date, 'DD-MON-YYYY HH24:MI:SS TZH:TZM'));
    ELSE
      dbms_output.put_line('  [ship_date] : N/A');
    END IF;
    
    -- Output fields from the 'customer' object.
    dbms_output.put_line('  [customer].[number]: '||
      l_order_obj.get_Object('customer').get_Number('number'));
    dbms_output.put_line('  [customer].[name]  : '||
      l_order_obj.get_Object('customer').get_String('name'));
      
    -- Loop through the 'lines' array for the current order.
    l_lines_arr := l_order_obj.get_Array('lines');
    dbms_output.put_line('  Order Lines  : ' || l_lines_arr.get_size);
    FOR y IN 0..l_lines_arr.get_size -1 LOOP
      -- Reference fields in the line without converting it to an object first.
      dbms_output.put_line('      [line_number] : ' ||
        json_object_t(l_lines_arr.get(y)).get_Number('line_number'));
      dbms_output.put_line('      [item_number] : ' ||
        json_object_t(l_lines_arr.get(y)).get_String('item_number'));
    END LOOP;  -- End Lines Loop
        
  END LOOP;    -- End Orders Loop

END;