Расширенная загрузка данных JSON для автономной базы данных

Загрузка данных JSON может быть достигнута несколькими способами для автономных баз данных. Я могу указать на блог Джеффа Смита для других методов, таких как использование Oracle REST Data Service (или ORDS).

Сегодня я просто продемонстрирую еще один простой метод, который очень полезен для быстрой загрузки данных JSON в коллекции SODA в автономных базах данных.

Это продолжение моего поста в блоге Как получить доступ к открытым данным с помощью Oracle Autonomous Database.

В демонстрационных целях мне нужно загрузить данные JSON, которые размещены в Интернете и являются общедоступными. Это действительно важно, хотя представленный здесь метод можно улучшить для поддержки аутентификации…

Цель: загрузить документы JSON и загрузить их в коллекцию SODA.

Для этого я буду использовать пакет APEX_WEB_SERVICE PL/SQL, который может загружать файлы напрямую.

Следующим шагом будет установка следующих типов PL/SQL и функции, чтобы творить чудеса:

CREATE OR REPLACE TYPE t_opendata_json_doc AS OBJECT ( json_document CLOB );
CREATE OR REPLACE TYPE t_opendata_json_doc_collection IS TABLE OF t_opendata_json_doc;
CREATE OR REPLACE FUNCTION getJSON( p_my_url IN varchar2, 
                                    p_json_path IN varchar2 default '$' ) 
    RETURN t_opendata_json_doc_collection PIPELINED AS
  TYPE OpenDataCursorType IS REF CURSOR;
  l_clob   clob;
  cur OpenDataCursorType;
  invalid_path_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_path_exception, -40561);
BEGIN
  l_clob := apex_web_service.make_rest_request(p_url => p_my_url, p_http_method => 'GET' );
  begin
    -- uses the provided JSON Path to access JSON data
    open cur for 'select d.json_document from json_table(:l_clob, ''$'' columns (nested PATH ''' || p_json_path || ''' columns(json_document CLOB format json path ''$''))) d' using l_clob;
    loop
      fetch cur into l_clob;
      exit when cur%NOTFOUND;
      PIPE ROW(t_opendata_json_doc(l_clob));
    end loop;
    
    close cur;
EXCEPTION WHEN invalid_path_exception THEN
  begin
    -- try if this is an array of JSON documents to flatten
    open cur for 'select d.json_document from json_table(:l_clob, ''$'' columns (nested path ''$[*]'' columns (json_document CLOB format json path ''$''))) d' using l_clob;
    
    loop
      fetch cur into l_clob;
      exit when cur%NOTFOUND;
      PIPE ROW(t_opendata_json_doc(l_clob));
    end loop;
    
    close cur;
  
  exception WHEN invalid_path_exception THEN
    -- return the JSON document as is
    open cur for 'select d.json_document from json_table(:l_clob, ''$'' columns json_document CLOB format json path ''$'') d' using l_clob;
    
    loop
      fetch cur into l_clob;
      exit when cur%NOTFOUND;
      PIPE ROW(t_opendata_json_doc(l_clob));
    end loop;
    close cur;
  end;
END;
RETURN;
END;
/

Запрос этой табличной функции

Ниже приведены несколько способов вызова функции…

Один документ JSON в файле

select *
  from getJSON( 'https://www.ncdc.noaa.gov/cag/global/time-series/globe/land_ocean/1/8/1880-2021/data.json' );

Он скачает файл:

{
  "description": { 
    "title": "Global Land and Ocean Temperature Anomalies, August",
    "units": "Degrees Celsius",
    "base_period": "1901-2000",
    "missing":-999
  },
  "data": { 
  "1880": "-0.07",
  "1881": "-0.06",
...
  }
}

и вернуть документ как есть:

Один документ JSON в файле, доступ к полю с использованием пути JSON

select * 
  from getJSON( 'https://tools.learningcontainer.com/sample-json-file.json', '$.Pets' );

Он скачает файл:

{
  "Name": "Test",
  "Mobile": 12345678,
  "Boolean": true,
  "Pets": ["Dog", "cat"],
  "Address": {
    "Permanent address": "USA",
    "current Address": "AU"
  }
}

и верните связанное поле массива Pets:

Несколько документов JSON внутри массива JSON

select * 
  from getJSON( 'https://raw.githubusercontent.com/loiclefevre/jsonloader/main/src/main/resources/Uni_POI_upper.json' );

Это загрузит следующий файл:

[
   {
      "NAME":"piscina_tor_di_quinto",
      "UNIVERSITY_ID":1,
      "TYPE":"Centro_sportivo",
      "COSTO":{
         "MIN":"2.20",
         "MAX":"7.20"
      },
      "CITY":"Roma",
      "ADDRESS":"Via Fornaci di Tor di Quinto, 64",
      "GEOMETRY":{
         "type":"Point",
         "coordinates":[
            12.4842835,
            41.9365692
         ]
      },
      "OPEN":"start from January 9th.",
      "SIZE":"16m x 33m",
      "PHOTO":{
         "TYPE1":"url",
         "PICTURES1":"https://web.uniroma1.it/sapienzasport/sites/default/files/IMG_20200622_164135.jpg"
      }
   },
   {
      "NAME":"mensa_lollis",
      "UNIVERSITY_ID":1,
      "TYPE":"Mensa",
...
   }
]

И он вернет один документ JSON на элемент массива:

Загрузка документов JSON в коллекцию SODA с использованием SQL

Теперь остается шаг, чтобы фактически вставить эти документы JSON в коллекцию SODA, используя простой SQL:

> soda create points_of_interest;
 Successfully created collection: points_of_interest
> desc points_of_interest;
Name          Null?    Type          
------------- -------- ------------- 
ID            NOT NULL VARCHAR2(255) 
CREATED_ON    NOT NULL TIMESTAMP(6)  
LAST_MODIFIED NOT NULL TIMESTAMP(6)  
VERSION       NOT NULL VARCHAR2(255) 
JSON_DOCUMENT          JSON
> INSERT INTO points_of_interest (id, version, json_document)
SELECT SYS_GUID(), SYS_GUID(), json_document
FROM getJSON( 'https://raw.githubusercontent.com/loiclefevre/jsonloader/main/src/main/resources/Uni_POI_upper.json' );
14 rows inserted.

Elapsed: 00:00:00.585

И мы закончили!