Consultar Dados JSON no Serviço Object Storage usando Tabelas Externas

Exemplo 1: Consultar Documentos JSON delimitados por Linha como Objetos JSON

Este exemplo mostra como consultar um arquivo JSON contendo vários documentos JSON delimitados por linha no armazenamento de objetos.

Um arquivo JSON, por exemplo, fruitLineDelimited.json existe no armazenamento de objetos que tem três linhas com um objeto por linha.

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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;
   /
   

   Os parâmetros são:

   • table_name: é o nome da tabela externa.

   • credential_name: é o nome da credencial criada na etapa anterior. O parâmetro credential_name deve estar de acordo com as convenções de nomeação de objetos da Oracle. Consulte Regras de Nomeação de Objeto de Banco de Dados para obter mais informações.

   • format: define as opções que você pode especificar para descrever o formato do arquivo de origem.

   • file_uri_list: é uma lista delimitada por vírgulas dos arquivos de origem que você deseja consultar.

2. Consulte a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_1;

Exemplo 2: Consultar Arrays JSON como Objetos JSON

Este exemplo mostra como consultar um único array de objetos JSON. Por padrão, as matrizes são descompactadas.

Um arquivo JSON, por exemplo, fruitArray.json existe no armazenamento de objetos e tem os dados a seguir.

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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 a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_2a;

3. Você pode usar a opção jsonpath para preservar a estrutura do array e retornar um array 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 a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_2b;

Exemplo 3: Arrays JSON Encapsulados do Objeto de Consulta como Objetos JSON

Este exemplo mostra como consultar um arquivo JSON encapsulado em um documento JSON externo. Neste exemplo, você fornece um caminho usando a opção de formato jsonpath para os dados que deseja carregar. O caminho deve levar a uma matriz. As linhas são mapeadas como no exemplo anterior.

Um arquivo JSON, por exemplo, fruitEmbeddedArray.json existe no armazenamento de objetos e tem os seguintes dados:

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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 a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_3;

Exemplo 4: Consultar Documentos JSON (EJSON) Estendidos como Objetos JSON

Este exemplo mostra como consultar um arquivo EJSON. O JSON do tipo SQL pode representar tipos JSON estendidos, como TIMESTAMP, DOUBLE, FLOAT e RAW. O texto JSON pode representar tipos JSON estendidos usando o formato JSON estendido. Essas anotações EJSON são convertidas automaticamente nos tipos correspondentes.

Um arquivo EJSON, por exemplo, fruitEjson.json existe no armazenamento de objetos e tem os seguintes dados:

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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 a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_4;

Exemplo 5: Consultar um Único Documento JSON com Arquivos de Várias Linhas como um Objeto JSON

Este exemplo mostra como consultar um único documento JSON com arquivos de várias linhas.

• Um único documento JSON com arquivos de várias linhas pode ser mapeado para uma tabela, na qual cada arquivo JSON no diretório é mapeado para uma única linha.

• Um único documento JSON com arquivos de várias linhas pode ser um diretório contendo arquivos JSON em que cada arquivo JSON é mapeado para uma única linha na tabela.

Este exemplo usa arquivos JSON que contêm os seguintes dados:

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

1. Crie uma tabela externa no seu banco de dados usando esses arquivos 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 a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_5;

Pontos a Considerar ao Acessar Documentos JSON

• Defina a opção delimiter do campo para um valor ou caractere que não esteja nos dados JSON. Em outras palavras, o valor do delimitador de campo não existe em seus documentos JSON. Quando você cria uma tabela com o pacote DBMS_CLOUD, o delimitador de campo padrão é '|' (pipe).

  Vamos considerar um cenário em que os dados JSON contenham '|'. Por exemplo:

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

  Se o delimitador de campo não for especificado, '|' será considerado como o delimitador de campo e uma consulta retornará o seguinte.

  {"attr1": "oK4IJ

  Para garantir que seus documentos JSON não sejam divididos involuntariamente, convertendo os dados em documentos JSON inválidos, defina a opção de formato do delimitador de campo para um valor que não esteja nos dados. Por exemplo:

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

• Se seus documentos JSON forem armazenados como dados delimitados por linha de documento único, eles serão processados como dados textuais sem problemas. Para um documento JSON que se estende por várias linhas em um arquivo externo, defina a opção de formato recorddelimiter como um valor ou caractere que não exista no arquivo de dados. Por exemplo:

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

  Se a opção recorddelimiter não estiver definida, todas as linhas serão consideradas como um registro individual. O delimitador de registro padrão newline renderiza os dados em documentos JSON inválidos.

• Em um arquivo de dados externo, se um campo não for especificado explicitamente, o tipo de dados padrão será definido como CHAR(255). Assim, ler documentos JSON com mais de 255 caracteres retorna um erro. Portanto, você deve definir o parâmetro field_list como um valor maior que o maior documento JSON em seus arquivos. Por exemplo:

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

• O tamanho padrão do buffer de leitura no Autonomous Database é 10 MB. Para documentos JSON com mais de 10 MB, você deve definir a opção de formato readsize com um valor mais alto, digamos 20 MB.

  json_object('readsize' value '20000000')

Veja a seguir um exemplo de definição de tabela externa que inclui todos esses 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;
/

Os exemplos a seguir demonstram como acessar documentos JSON como dados textuais com o Oracle Database 19c ou anterior usando o tipo de dados CLOB. Como alternativa, você pode usar o tipo de dados VARCHAR2, dependendo do tamanho dos seus documentos JSON.

• Documentos JSON delimitados por linhas

• Matrizes JSON

• Matrizes JSON encapsuladas por objeto

• Documento JSON único com arquivos de várias linhas

Exemplo 1: Consultar Documentos JSON delimitados por linha como Dados Textuais

Este exemplo mostra como consultar um arquivo JSON contendo vários documentos JSON delimitados por linha no armazenamento de objetos.

Um arquivo JSON, por exemplo, fruitLineDelimited.json existe no armazenamento de objetos que tem três linhas com um objeto por linha.

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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;
   /
   

   Os parâmetros são:

   • table_name: é o nome da tabela externa.

   • column_list: é uma lista delimitada por vírgula de nomes de coluna e tipos de dados para a tabela externa. A lista inclui as colunas dentro do arquivo de dados e as derivadas do nome do objeto (dos nomes no caminho do arquivo especificado por file_uri_list).

   • credential_name: é o nome da credencial criada na etapa anterior. O parâmetro credential_name deve estar de acordo com as convenções de nomeação de objetos da Oracle. Consulte Regras de Nomeação de Objeto de Banco de Dados para obter mais informações.

   • field_list: identifica os campos nos arquivos de origem e seus tipos de dados.

   • format: define as opções que você pode especificar para descrever o formato do arquivo de origem.

   • file_uri_list: é uma lista delimitada por vírgulas dos arquivos de origem que você deseja consultar.

2. Consulte a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_6;

Exemplo 2: Consultar Arrays JSON como Dados Textuais

Este exemplo mostra como consultar um único array de objetos JSON. Você não pode descompactar matrizes automaticamente como parte do acesso aos dados da tabela externa, mas pode fazer o descompactamento ao acessar os dados.

Um arquivo JSON, por exemplo, fruitArray.json existe no armazenamento de objetos e tem os dados a seguir.

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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 a tabela externa usando a instrução SELECT.

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

Exemplo 3: Arrays JSON Encapsulados do Objeto de Consulta como Dados Textuais

Este exemplo mostra como consultar um arquivo JSON encapsulado em um documento JSON externo. Você não pode descompactar automaticamente arrays como parte do acesso a dados da tabela externa, mas pode fazer o descompactamento e o subconjunto de documentos JSON ao acessar os dados.

Um arquivo JSON, por exemplo, fruitEmbeddedArray.json existe no armazenamento de objetos e tem os seguintes dados:

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

1. Crie uma tabela externa no seu banco de dados usando o arquivo 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 a tabela externa usando a instrução SELECT.

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

Exemplo 4: Consultar um Único Documento JSON com Arquivos de Várias Linhas como Dados Textuais

Este exemplo mostra como consultar um único documento JSON com arquivos de várias linhas.

• Um único documento JSON com arquivos de várias linhas pode ser mapeado para uma tabela, na qual cada arquivo JSON no diretório é mapeado para uma única linha.

• Um único documento JSON com arquivos de várias linhas pode ser um diretório contendo arquivos JSON em que cada arquivo JSON é mapeado para uma única linha na tabela.

Este exemplo usa arquivos JSON que contêm os seguintes dados:

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

1. Crie uma tabela externa no seu banco de dados usando esses arquivos 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 a tabela externa usando a instrução SELECT.

   SELECT * FROM fruit_9;