Interroger des données JSON dans le stockage d'objets à 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 délimité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 votre 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 données d'identification créées à l'étape précédente. Le paramètre credential_name doit être conforme aux conventions d'attribution de nom d'objet Oracle. Pour plus d'informations, voir Règles d'attribution de nom aux objets de base de données.

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

   • file_uri_list : Liste délimitée par des virgules des fichiers sources à interroger.

2. Interrogez la table externe à l'aide de l'énoncé 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. Les tableaux sont décompressés par défaut.

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 votre 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 à l'aide de l'énoncé SELECT.

   SELECT * FROM fruit_2a;

3. Vous pouvez utiliser l'option jsonpath pour conserver la structure du tableau et retourner 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 à l'aide de l'énoncé SELECT.

   SELECT * FROM fruit_2b;

Exemple 3 : Tableaux JSON encapsulés dans un objet d'interrogation 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.

Un fichier JSON, par exemple, 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 votre 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 à l'aide de l'énoncé 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 type SQL JSON 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 en 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 votre 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 à l'aide de l'énoncé SELECT.

   SELECT * FROM fruit_4;

Exemple 5 : Interroger un document JSON unique avec des fichiers multiligne en tant qu'objet JSON

Cet exemple montre comment interroger un seul document JSON avec des fichiers multiligne.

• Un seul document JSON avec des fichiers multiligne peut être mappé à une table, où chaque fichier JSON du répertoire est mappé à une seule rangée.

• Un seul document JSON avec des fichiers multiligne peut être un répertoire contenant des fichiers JSON où chaque fichier JSON est mappé à une seule rangée 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 votre 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 à l'aide de l'énoncé SELECT.

   SELECT * FROM fruit_5;

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

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

  Considérons un scénario dans lequel les données JSON contiennent '|'. 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 interrogation retourne ce qui suit.

  {"attr1": "oK4IJ

  Pour vous assurer que vos documents JSON ne sont pas fractionnés involontairement, en affichant les données dans des documents JSON non valides, réglez l'option de format de délimiteur de champ à une valeur qui ne figure pas dans les données. Exemple :

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

• Si vos documents JSON sont stockés en tant que données délimité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 rangées d'un fichier externe, vous devez régler l'option de format recorddelimiter à une valeur ou à un caractère qui n'existe pas dans le fichier de données. 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 rend les données dans des documents JSON non valides.

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

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

• La taille de la mémoire tampon de lecture par défaut dans Autonomous Database est 10 Mo. Pour les documents JSON dont la taille est supérieure à 10 Mo, vous devez régler l'option de format readsize à 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 aux 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 de vos documents JSON.

• Documents JSON délimités par des lignes

• Tableaux JSON

• Tableaux JSON encapsulés d'objet

• Document JSON unique avec fichiers multiligne

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

Cet exemple montre comment interroger un fichier JSON contenant plusieurs documents JSON délimité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 votre 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 : Liste séparée par des virgules de noms de colonne et de types de données pour la 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 du chemin d'accès spécifié par file_uri_list).

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

   • field_list : identifie les champs des fichiers sources et leur type de données.

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

   • file_uri_list : Liste délimitée par des virgules des fichiers sources à interroger.

2. Interrogez la table externe à l'aide de l'énoncé 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éballer automatiquement les tableaux dans le cadre de l'accès aux données de la table externe, mais vous pouvez effectuer le déballage 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 votre 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 à l'aide de l'énoncé SELECT.

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

Exemple 3 : Tableaux JSON encapsulés dans un objet d'interrogation 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éballer automatiquement les tableaux dans le cadre de l'accès aux données de la table externe, mais vous pouvez effectuer le déballage et le sous-ensemble de documents JSON lors de l'accès aux données.

Un fichier JSON, par exemple, 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 votre 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 à l'aide de l'énoncé 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 multiligne en tant que données textuelles

Cet exemple montre comment interroger un seul document JSON avec des fichiers multiligne.

• Un seul document JSON avec des fichiers multiligne peut être mappé à une table, où chaque fichier JSON du répertoire est mappé à une seule rangée.

• Un seul document JSON avec des fichiers multiligne peut être un répertoire contenant des fichiers JSON où chaque fichier JSON est mappé à une seule rangée 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 votre 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 à l'aide de l'énoncé SELECT.

   SELECT * FROM fruit_9;