Consulta de datos JSON en Object Storage mediante tablas externas

Ejemplo 1: consulta de documentos JSON delimitados por línea como objetos JSON

En este ejemplo se muestra cómo consultar un archivo JSON que contiene varios documentos JSON delimitados por líneas en Object Storage.

Un archivo JSON, por ejemplo, fruitLineDelimited.json, existe en el almacenamiento de objetos que tiene tres líneas con un objeto por línea.

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

1. Cree una tabla externa en la base de datos mediante el archivo 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;
   /
   

   Los parámetros son:

   • table_name: es el nombre de la tabla externa.

   • credential_name: es el nombre de la credencial creada en el paso anterior. El parámetro credential_name debe cumplir las convenciones de nomenclatura de objetos de Oracle. Consulte Reglas de nomenclatura de objetos de base de datos para obtener más información.

   • format: define las opciones que puede especificar para describir el formato del archivo de origen.

   • file_uri_list: es una lista delimitada por comas de los archivos de origen que desea consultar.

2. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_1;

Ejemplo 2: consulta de matrices JSON como objetos JSON

En este ejemplo se muestra cómo consultar una única matriz de objetos JSON. Las matrices están desembaladas por defecto.

Un archivo JSON, por ejemplo, fruitArray.json, existe en el almacenamiento de objetos y tiene los siguientes datos.

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

1. Cree una tabla externa en la base de datos mediante el archivo 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. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_2a;

3. Puede utilizar la opción jsonpath para conservar la estructura de matriz y devolver una matriz de objeto 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. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_2b;

Ejemplo 3: Matrices JSON encapsuladas de objetos de consulta como objetos JSON

En este ejemplo se muestra cómo consultar un archivo JSON envuelto en un documento JSON externo. En este ejemplo, proporciona una ruta de acceso mediante la opción de formato jsonpath a los datos que desea cargar. La ruta debe conducir a una matriz. Las filas se correlacionan como en el ejemplo anterior.

Un archivo JSON, por ejemplo, fruitEmbeddedArray.json, existe en el almacenamiento de objetos y tiene los siguientes datos:

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

1. Cree una tabla externa en la base de datos mediante el archivo 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. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_3;

Ejemplo 4: consulta de documentos JSON ampliados (EJSON) como objetos JSON

En este ejemplo se muestra cómo consultar un archivo EJSON. El tipo SQL JSON puede representar tipos JSON ampliados, como TIMESTAMP, DOUBLE, FLOAT y RAW. El texto JSON puede representar tipos de JSON ampliados mediante el formato JSON ampliado. Estas anotaciones EJSON se convierten automáticamente en los tipos correspondientes.

Un archivo EJSON, por ejemplo, fruitEjson.json, existe en el almacenamiento de objetos y tiene los siguientes datos:

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

1. Cree una tabla externa en la base de datos mediante el archivo 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. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_4;

Ejemplo 5: Consulta de un único documento JSON con archivos multilínea como objeto JSON

En este ejemplo se muestra cómo consultar un único documento JSON con archivos de varias líneas.

• Un único documento JSON con archivos de varias líneas se puede asignar a una tabla, donde cada archivo JSON del directorio se asigna a una sola fila.

• Un único documento JSON con archivos de varias líneas puede ser un directorio que contenga archivos JSON donde cada archivo JSON se asigna a una sola fila de la tabla.

En este ejemplo, se utilizan archivos JSON que contienen los siguientes datos:

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

1. Cree una tabla externa en la base de datos mediante estos archivos 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. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_5;

Puntos que hay que considerar al acceder a documentos JSON

• Defina la opción de campo delimiter en un valor o carácter que no esté en los datos JSON. Es decir, el valor delimitador de campo no existe en los documentos JSON. Al crear una tabla con el paquete DBMS_CLOUD, el delimitador de campo por defecto es '|' (pipe).

  Consideremos un escenario en el que los datos JSON contengan '|'. Por ejemplo:

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

  Si no se especifica el delimitador de campo, '|' se considera el delimitador de campo y una consulta devuelve lo siguiente.

  {"attr1": "oK4IJ

  Para asegurarse de que los documentos JSON no se dividan involuntariamente, presentando los datos en documentos JSON no válidos, defina la opción de formato del delimitador de campo en un valor que no esté en los datos. Por ejemplo:

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

• Si los documentos JSON se almacenan como datos delimitados por línea de documento único, se procesan como datos textuales sin problemas. Para un documento JSON que abarque varias filas en un archivo externo, debe definir la opción de formato recorddelimiter en un valor o carácter que no exista en el archivo de datos. Por ejemplo:

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

  Si no se define la opción recorddelimiter, cada línea se considera un registro individual. El delimitador de registro predeterminado newline convierte los datos en documentos JSON no válidos.

• En un archivo de datos externo, si no se especifica explícitamente un campo, el tipo de dato por defecto se define en CHAR(255). Por lo tanto, la lectura de documentos JSON de más de 255 caracteres devuelve un error. Por lo tanto, debe definir el parámetro field_list en un valor superior al documento JSON más grande de los archivos. Por ejemplo:

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

• El tamaño del buffer de lectura por defecto en Autonomous Database es de 10 MB. Para documentos JSON de más de 10 MB, debe definir la opción de formato readsize en un valor superior, supongamos 20 MB.

  json_object('readsize' value '20000000')

A continuación se muestra un ejemplo de definición de tabla externa que incluye todos estos parámetros.

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

En los siguientes ejemplos se muestra cómo acceder a documentos JSON como datos textuales con Oracle Database 19c o una versión anterior mediante el tipo de dato CLOB. También puede utilizar el tipo de dato VARCHAR2 en función del tamaño de los documentos JSON.

• Documentos JSON delimitados por líneas

• Matrices de JSON

• Matrices JSON encapsuladas de objeto

• Un solo documento JSON con archivos de varias líneas

Ejemplo 1: consulta de documentos JSON delimitados por línea como datos textuales

En este ejemplo se muestra cómo consultar un archivo JSON que contiene varios documentos JSON delimitados por líneas en Object Storage.

Un archivo JSON, por ejemplo, fruitLineDelimited.json, existe en el almacenamiento de objetos que tiene tres líneas con un objeto por línea.

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

1. Cree una tabla externa en la base de datos mediante el archivo 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;
   /
   

   Los parámetros son:

   • table_name: es el nombre de la tabla externa.

   • column_list: es una lista delimitada por comas de nombres de columna y tipos del dato para la tabla externa. La lista incluye las columnas del archivo de datos y las derivadas del nombre del objeto (de los nombres de la ruta de acceso del archivo especificada por file_uri_list).

   • credential_name: es el nombre de la credencial creada en el paso anterior. El parámetro credential_name debe cumplir las convenciones de nomenclatura de objetos de Oracle. Consulte Reglas de nomenclatura de objetos de base de datos para obtener más información.

   • field_list: identifica los campos en los archivos de origen y sus tipos de datos.

   • format: define las opciones que puede especificar para describir el formato del archivo de origen.

   • file_uri_list: es una lista delimitada por comas de los archivos de origen que desea consultar.

2. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_6;

Ejemplo 2: consulta de matrices JSON como datos textuales

En este ejemplo se muestra cómo consultar una única matriz de objetos JSON. No puede desempaquetar matrices automáticamente como parte del acceso a los datos de la tabla externa, pero puede desempaquetarlas mientras accede a los datos.

Un archivo JSON, por ejemplo, fruitArray.json, existe en el almacenamiento de objetos y tiene los siguientes datos.

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

1. Cree una tabla externa en la base de datos mediante el archivo 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. Consulte la tabla externa mediante la sentencia SELECT.

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

Ejemplo 3: Matrices JSON envueltas en objeto de consulta como datos textuales

En este ejemplo se muestra cómo consultar un archivo JSON envuelto en un documento JSON externo. No puede desempaquetar matrices automáticamente como parte del acceso a datos de tabla externa, pero puede desempaquetar y subjuego de documentos JSON al acceder a los datos.

Un archivo JSON, por ejemplo, fruitEmbeddedArray.json, existe en el almacenamiento de objetos y tiene los siguientes datos:

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

1. Cree una tabla externa en la base de datos mediante el archivo 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. Consulte la tabla externa mediante la sentencia SELECT.

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

Ejemplo 4: Consulta de un único documento JSON con archivos multilínea como datos textuales

En este ejemplo se muestra cómo consultar un único documento JSON con archivos de varias líneas.

• Un único documento JSON con archivos de varias líneas se puede asignar a una tabla, donde cada archivo JSON del directorio se asigna a una sola fila.

• Un único documento JSON con archivos de varias líneas puede ser un directorio que contenga archivos JSON donde cada archivo JSON se asigna a una sola fila de la tabla.

En este ejemplo, se utilizan archivos JSON que contienen los siguientes datos:

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

1. Cree una tabla externa en la base de datos mediante estos archivos 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. Consulte la tabla externa mediante la sentencia SELECT.

   SELECT * FROM fruit_9;