Interroger des données JSON dans Object Storage à l'aide de tables externes

Exemple 1 : interroger des documents JSON délimités par des lignes en tant qu'objets JSON

Cet exemple montre comment interroger un fichier JSON contenant plusieurs documents JSON séparés par des lignes dans le stockage d'objets.

Un fichier JSON, par exemple, fruitLineDelimited.json existe dans le stockage d'objets qui comporte trois lignes avec un objet par ligne.

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

1. Créez une table externe dans la base de données à l'aide du fichier 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;
   /
   

   Les paramètres sont les suivants :

   • table_name : nom de la table externe.

   • credential_name : nom des informations d'identification créées à l'étape précédente. Le paramètre credential_name doit être conforme aux conventions de dénomination des objets Oracle. Pour plus d'informations, reportez-vous à Règles de dénomination des objets de base de données.

   • format : définit les options que vous pouvez indiquer pour décrire le format du fichier source.

   • file_uri_list : liste des fichiers source à interroger séparés par une virgule.

2. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_1;

Exemple 2 : interroger des tableaux JSON en tant qu'objets JSON

Cet exemple montre comment interroger un seul tableau d'objets JSON. Par défaut, les tableaux sont décompressés.

Un fichier JSON, par exemple, fruitArray.json existe dans le stockage d'objets et contient les données suivantes.

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

1. Créez une table externe dans la base de données à l'aide du fichier 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. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_2a;

3. Vous pouvez utiliser l'option jsonpath pour conserver la structure de tableau et renvoyer un tableau d'objet 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. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_2b;

Exemple 3 : tableaux JSON encapsulés d'objet de requête en tant qu'objets JSON

Cet exemple montre comment interroger un fichier JSON encapsulé dans un document JSON externe. Dans cet exemple, vous indiquez un chemin à l'aide de l'option de format jsonpath pour les données à charger. Le chemin doit conduire à un tableau. Les lignes sont mappées comme dans l'exemple précédent.

Par exemple, un fichier JSON, fruitEmbeddedArray.json, existe dans le stockage d'objets et contient les données suivantes :

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

1. Créez une table externe dans la base de données à l'aide du fichier 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. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_3;

Exemple 4 : interroger des documents JSON étendus (EJSON) en tant qu'objets JSON

Cet exemple montre comment interroger un fichier EJSON. Le format JSON de type SQL peut représenter des types JSON étendus tels que TIMESTAMP, DOUBLE, FLOAT et RAW. Le texte JSON peut représenter des types JSON étendus à l'aide du format JSON étendu. Ces annotations EJSON sont automatiquement converties aux types correspondants.

Un fichier EJSON, par exemple, fruitEjson.json existe dans le stockage d'objets et contient les données suivantes :

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

1. Créez une table externe dans la base de données à l'aide du fichier 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. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_4;

Exemple 5 : interroger un document JSON unique avec des fichiers multilignes en tant qu'objet JSON

Cet exemple montre comment interroger un document JSON unique avec des fichiers multilignes.

• Un document JSON unique avec des fichiers multilignes peut être mis en correspondance avec une table, où chaque fichier JSON du répertoire est mis en correspondance avec une seule ligne.

• Un document JSON unique avec des fichiers multilignes peut être un répertoire contenant des fichiers JSON dans lequel chaque fichier JSON est mis en correspondance avec une seule ligne de la table.

Cet exemple utilise des fichiers JSON contenant les données suivantes :

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

1. Créez une table externe dans la base de données à l'aide de ces fichiers 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. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_5;

Points à prendre en compte lors de l'accès à des documents JSON

• Définissez l'option de champ delimiter sur une valeur ou un caractère qui ne figure pas dans vos données JSON. En d'autres termes, la valeur de délimiteur de champ n'existe pas dans vos documents JSON. Lorsque vous créez une table avec le package DBMS_CLOUD, le délimiteur de champ par défaut est '|' (pipe).

  Considérons un scénario dans lequel une donnée JSON contient "|". Par exemple :

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

  Si le délimiteur de champ n'est pas spécifié, '|' est considéré comme le délimiteur de champ et une requête renvoie les éléments suivants.

  {"attr1": "oK4IJ

  Pour vous assurer que vos documents JSON ne sont pas fractionnés involontairement, en rendant les données dans des documents JSON non valides, définissez l'option de format de délimiteur de champ sur une valeur qui ne figure pas dans les données. Par exemple :

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

• Si vos documents JSON sont stockés en tant que données séparées par une ligne de document unique, ils sont traités en tant que données textuelles sans aucun problème. Pour un document JSON qui s'étend sur plusieurs lignes dans un fichier externe, vous devez définir l'option de format recorddelimiter sur une valeur ou un caractère qui n'existe pas dans le fichier de données. Par exemple :

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

  Si l'option recorddelimiter n'est pas définie, chaque ligne est considérée comme un enregistrement individuel. Le délimiteur d'enregistrement par défaut newline affiche les données dans des documents JSON non valides.

• Dans un fichier de données externe, si un champ n'est pas spécifié explicitement, le type de données par défaut est défini sur CHAR(255). Ainsi, la lecture de documents JSON de plus de 255 caractères renvoie une erreur. Par conséquent, vous devez définir le paramètre field_list sur une valeur supérieure au document JSON le plus grand dans vos fichiers. Par exemple :

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

• La taille par défaut du tampon de lecture dans Autonomous Database est de 10 Mo. Pour les documents JSON de plus de 10 Mo, vous devez définir l'option de format readsize sur une valeur supérieure, par exemple 20 Mo.

  json_object('readsize' value '20000000')

Voici un exemple de définition de table externe qui inclut tous ces paramètres.

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;
/

Les exemples suivants montrent comment accéder à des documents JSON en tant que données textuelles avec Oracle Database 19c ou une version antérieure à l'aide du type de données CLOB. Vous pouvez également utiliser le type de données VARCHAR2 en fonction de la taille des documents JSON.

• Documents JSON délimités par des lignes

• Tableaux JSON

• Tableaux JSON encapsulés d'objet

• Document JSON unique avec fichiers multilignes

Exemple 1 : interroger des documents JSON séparés par des lignes en tant que données textuelles

Cet exemple montre comment interroger un fichier JSON contenant plusieurs documents JSON séparés par des lignes dans le stockage d'objets.

Un fichier JSON, par exemple, fruitLineDelimited.json existe dans le stockage d'objets qui comporte trois lignes avec un objet par ligne.

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

1. Créez une table externe dans la base de données à l'aide du fichier 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;
   /
   

   Les paramètres sont les suivants :

   • table_name : nom de la table externe.

   • column_list est une liste délimitée par des virgule de noms de colonne et de type de données pour une table externe. La liste inclut les colonnes du fichier de données et celles dérivées du nom de l'objet (à partir des noms figurant dans le chemin de fichier indiqué par file_uri_list).

   • credential_name : nom des informations d'identification créées à l'étape précédente. Le paramètre credential_name doit être conforme aux conventions de dénomination des objets Oracle. Pour plus d'informations, reportez-vous à Règles de dénomination des objets de base de données.

   • field_list : identifie les champs dans les fichiers source et leurs types de données.

   • format : définit les options que vous pouvez indiquer pour décrire le format du fichier source.

   • file_uri_list : liste des fichiers source à interroger séparés par une virgule.

2. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_6;

Exemple 2 : interroger des tableaux JSON en tant que données textuelles

Cet exemple montre comment interroger un seul tableau d'objets JSON. Vous ne pouvez pas décompresser automatiquement les tableaux dans le cadre de l'accès aux données des tables externes, mais vous pouvez procéder au décompressage lors de l'accès aux données.

Un fichier JSON, par exemple, fruitArray.json existe dans le stockage d'objets et contient les données suivantes.

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

1. Créez une table externe dans la base de données à l'aide du fichier 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. Interrogez la table externe avec l'instruction SELECT.

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

Exemple 3 : tableaux JSON encapsulés d'objet de requête en tant que données textuelles

Cet exemple montre comment interroger un fichier JSON encapsulé dans un document JSON externe. Vous ne pouvez pas décompresser automatiquement les tableaux dans le cadre de l'accès aux données de table externe, mais vous pouvez effectuer le décompressage et le sous-ensemble de documents JSON lors de l'accès aux données.

Par exemple, un fichier JSON, fruitEmbeddedArray.json, existe dans le stockage d'objets et contient les données suivantes :

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

1. Créez une table externe dans la base de données à l'aide du fichier 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. Interrogez la table externe avec l'instruction SELECT.

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

Exemple 4 : interroger un document JSON unique avec des fichiers multilignes en tant que données textuelles

Cet exemple montre comment interroger un document JSON unique avec des fichiers multilignes.

• Un document JSON unique avec des fichiers multilignes peut être mis en correspondance avec une table, où chaque fichier JSON du répertoire est mis en correspondance avec une seule ligne.

• Un document JSON unique avec des fichiers multilignes peut être un répertoire contenant des fichiers JSON dans lequel chaque fichier JSON est mis en correspondance avec une seule ligne de la table.

Cet exemple utilise des fichiers JSON contenant les données suivantes :

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

1. Créez une table externe dans la base de données à l'aide de ces fichiers 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. Interrogez la table externe avec l'instruction SELECT.

   SELECT * FROM fruit_9;