Esegui query sui dati JSON nello storage degli oggetti utilizzando tabelle esterne

Esempio 1: query su documenti JSON delimitati da riga come oggetti JSON

Questo esempio mostra come eseguire una query su un file JSON contenente più documenti JSON delimitati da riga nello storage degli oggetti.

Un file JSON, ad esempio fruitLineDelimited.json, esiste nello storage degli oggetti che ha tre righe con un oggetto per riga.

{ "name": "apple", "count": 20 }
{ "name": "orange", "count": 42 }
{ "name": "pear", "count": 10 }

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_1',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitLineDelimited.json';
   END;
   /
   

   Di seguito sono riportati i parametri disponibili.

   • table_name: è il nome della tabella esterna.

   • credential_name: indica il nome della credenziale creata nel passo precedente. Il parametro credential_name deve essere conforme alle convenzioni di denominazione degli oggetti Oracle. Per ulteriori informazioni, vedere Regole di denominazione degli oggetti database.

   • format: definisce le opzioni che è possibile specificare per descrivere il formato del file di origine.

   • file_uri_list: è una lista delimitata da virgole dei file di origine che si desidera interrogare.

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_1;

Esempio 2: query sugli array JSON come oggetti JSON

Questo esempio mostra come eseguire una query su un singolo array di oggetti JSON. Per impostazione predefinita, gli array vengono disimballati.

Un file JSON, ad esempio fruitArray.json, esiste nello storage degli oggetti e contiene i dati seguenti.

[
   {
      "name" : "apple",
      "count": 20
   },
   {
      "name" : "orange",
      "count": 42
   },
   {
      "name" : "pear",
      "count": 10
   }
]

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_2a',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_2a;

3. È possibile utilizzare l'opzione jsonpath per conservare la struttura di array e restituire un array di oggetti JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_2b',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc', 'jsonpath' value '$'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
   END;
   /
   

4. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_2b;

Esempio 3: array JSON con wrapping dell'oggetto query come oggetti JSON

Questo esempio mostra come eseguire una query su un file JSON sottoposto a wrapping in un documento JSON esterno. In questo esempio, si fornisce un percorso utilizzando l'opzione di formato jsonpath ai dati che si desidera caricare. Il percorso deve portare a un array. Le righe vengono mappate come nell'esempio precedente.

Un file JSON, ad esempio fruitEmbeddedArray.json, esiste nello storage degli oggetti e contiene i dati seguenti:

{
  "last_updated": 1434054678,
  "ttl": 0,
  "version": "1.0",
  "fruit": [
    { "name" : "apple", "count": 20 },
    { "name" : "orange", "count": 42 },
    { "name" : "pear", "count": 10 }
  ]
}

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_3',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc', 'jsonpath' value '$.fruit[*]'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEmbeddedArray.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_3;

Esempio 4: query su documenti JSON estesi (EJSON) come oggetti JSON

Questo esempio mostra come eseguire una query su un file EJSON. Il tipo SQL JSON può rappresentare tipi JSON estesi come TIMESTAMP, DOUBLE, FLOAT e RAW. Il testo JSON può rappresentare i tipi JSON estesi utilizzando il formato JSON esteso. Queste annotazioni EJSON vengono convertite automaticamente nei tipi corrispondenti.

Un file EJSON, ad esempio fruitEjson.json, esiste nello storage degli oggetti e contiene i dati seguenti:

{ "name" : "apple", "count": 20, "modified":{"$date":"2020-06-29T11:53:05.439Z"} }
{ "name" : "orange", "count": 42 }
{ "name" : "pear", "count": 10 }

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_4',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEjson.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_4;

Esempio 5: query su un singolo documento JSON con file multilinea come oggetto JSON

Questo esempio mostra come eseguire una query su un singolo documento JSON con file multilinea.

• Un singolo documento JSON con file multilinea può essere mappato a una tabella, in cui ogni file JSON nella directory è mappato a una singola riga.

• Un singolo documento JSON con file multilinea può essere una directory contenente file JSON in cui ogni file JSON è mappato a una singola riga nella tabella.

In questo esempio vengono utilizzati i file JSON contenenti i dati seguenti:

fruitMultiLineFile1.json

{
   "name" : "apple",
   "count": 42
}

fruitMultiLineFile2.json

{
   "name" : "orange",
   "count": 5
}

fruitMultiLineFile3.json

{
   "name" : "pear",
   "count": 10
}

1. Creare una tabella esterna nel database utilizzando questi file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_5',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_5;

Punti da considerare durante l'accesso ai documenti JSON

• Definire l'opzione delimiter del campo su un valore o un carattere non presente nei dati JSON. In altre parole, il valore del delimitatore di campo non esiste nei documenti JSON. Quando si crea una tabella con il pacchetto DBMS_CLOUD, il delimitatore di campo predefinito è '|' (pipe).

  Consideriamo uno scenario in cui i dati JSON contengono '|'. Ad esempio:

  {"attr1": "oK4IJ|V", "attr2": "igN”}

  Se il delimitatore di campo non è specificato, '|' viene considerato come delimitatore di campo e una query restituisce quanto segue.

  {"attr1": "oK4IJ

  Per assicurarsi che i documenti JSON non vengano suddivisi involontariamente, rendendo i dati in documenti JSON non validi, impostare l'opzione di formato del delimitatore di campo su un valore non presente nei dati. Ad esempio:

  json_object('delimiter' value 'X''0''')

• Se i documenti JSON vengono memorizzati come dati delimitati da una riga di documento singola, vengono elaborati come dati testuali senza problemi. Per un documento JSON che si estende su più righe in un file esterno, è necessario impostare l'opzione di formato recorddelimiter su un valore o un carattere non esistente nel file di dati. Ad esempio:

  json_object('recorddelimiter' value '0x''1B''')

  Se l'opzione recorddelimiter non è impostata, ogni riga viene considerata come un singolo record. Il delimitatore di record predefinito newline visualizza i dati in documenti JSON non validi.

• In un file di dati esterno, se un campo non viene specificato in modo esplicito, il tipo di dati predefinito viene impostato su CHAR(255). Pertanto, la lettura di documenti JSON con lunghezza superiore a 255 caratteri restituisce un errore. Pertanto, è necessario impostare il parametro field_list su un valore superiore al documento JSON più grande all'interno dei file. Ad esempio:

  field_list =>'"MYDATA" char(10000)'

• La dimensione del buffer di lettura predefinita in Autonomous Database è di 10 MB. Per i documenti JSON con dimensioni superiori a 10 MB, è necessario impostare l'opzione di formato readsize su un valore maggiore, ad esempio 20 MB.

  json_object('readsize' value '20000000')

Ecco una definizione di tabella esterna di esempio che include tutti questi parametri.

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_5',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
END;
/

Gli esempi riportati di seguito mostrano come accedere ai documenti JSON come dati testuali con Oracle Database 19c o versioni precedenti utilizzando il tipo di dati CLOB. In alternativa, è possibile utilizzare il tipo di dati VARCHAR2 a seconda delle dimensioni dei documenti JSON.

• Documenti JSON delimitati da riga

• Array JSON

• Array JSON con wrapping dell'oggetto

• Documento JSON singolo con file multilinea

Esempio 1: query su documenti JSON delimitati da riga come dati testuali

Questo esempio mostra come eseguire una query su un file JSON contenente più documenti JSON delimitati da riga nello storage degli oggetti.

Un file JSON, ad esempio fruitLineDelimited.json, esiste nello storage degli oggetti che ha tre righe con un oggetto per riga.

{ "name": "apple", "count": 20 }
{ "name": "orange", "count": 42 }
{ "name": "pear", "count": 10 }

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_6',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitLineDelimited.json';
   END;
   /
   

   Di seguito sono riportati i parametri disponibili.

   • table_name: è il nome della tabella esterna.

   • column_list: è una lista delimitata da virgole di nomi di colonna e tipi di dati per la tabella esterna. L'elenco include le colonne all'interno del file di dati e quelle derivate dal nome dell'oggetto (dai nomi nel percorso del file specificato da file_uri_list).

   • credential_name: indica il nome della credenziale creata nel passo precedente. Il parametro credential_name deve essere conforme alle convenzioni di denominazione degli oggetti Oracle. Per ulteriori informazioni, vedere Regole di denominazione degli oggetti database.

   • field_list: identifica i campi nei file di origine e i relativi tipi di dati.

   • format: definisce le opzioni che è possibile specificare per descrivere il formato del file di origine.

   • file_uri_list: è una lista delimitata da virgole dei file di origine che si desidera interrogare.

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_6;

Esempio 2: query sugli array JSON come dati testuali

Questo esempio mostra come eseguire una query su un singolo array di oggetti JSON. Non è possibile disimballare automaticamente gli array come parte dell'accesso ai dati della tabella esterna, ma è possibile disimballare durante l'accesso ai dati.

Un file JSON, ad esempio fruitArray.json, esiste nello storage degli oggetti e contiene i dati seguenti.

[
   {
      "name" : "apple",
      "count": 20
   },
   {
      "name" : "orange",
      "count": 42
   },
   {
      "name" : "pear",
      "count": 10
   }
]

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_7',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * 
   FROM fruit_7
   NESTED mydata 
   COLUMNS (nested path '$[*]' 
   COLUMNS (data format json path '$'));

Esempio 3: array JSON con wrapping oggetto query come dati testuali

Questo esempio mostra come eseguire una query su un file JSON sottoposto a wrapping in un documento JSON esterno. Non è possibile disimballare automaticamente gli array come parte dell'accesso ai dati delle tabelle esterne, ma è possibile disimballare e sottoimpostare i documenti JSON durante l'accesso ai dati.

Un file JSON, ad esempio fruitEmbeddedArray.json, esiste nello storage degli oggetti e contiene i dati seguenti:

{
  "last_updated": 1434054678,
  "ttl": 0,
  "version": "1.0",
  "fruit": [
    { "name" : "apple", "count": 20 },
    { "name" : "orange", "count": 42 },
    { "name" : "pear", "count": 10 }
  ]
}

1. Creare una tabella esterna nel database utilizzando il file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_8',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEmbeddedArray.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * 
   FROM fruit_8 
   NESTED mydata 
   COLUMNS (nested path '$.fruit[*]' 
   COLUMNS (data format json path '$'));
   

Esempio 4: query su un singolo documento JSON con file multilinea come dati testuali

Questo esempio mostra come eseguire una query su un singolo documento JSON con file multilinea.

• Un singolo documento JSON con file multilinea può essere mappato a una tabella, in cui ogni file JSON nella directory è mappato a una singola riga.

• Un singolo documento JSON con file multilinea può essere una directory contenente file JSON in cui ogni file JSON è mappato a una singola riga nella tabella.

In questo esempio vengono utilizzati i file JSON contenenti i dati seguenti:

fruitMultiLineFile1.json

{
   "name" : "apple",
   "count": 42
}

fruitMultiLineFile2.json

{
   "name" : "orange",
   "count": 5
}

fruitMultiLineFile3.json

{
   "name" : "pear",
   "count": 10
}

1. Creare una tabella esterna nel database utilizzando questi file JSON.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_9',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
   END;
   /
   

2. Eseguire una query sulla tabella esterna utilizzando l'istruzione SELECT.

   SELECT * FROM fruit_9;