Pular para o conteúdo

Convertendo JSON em tabela no Oracle: Explicação técnica e prática

Convertendo JSON em tabela no Oracle

Olá camarada!

Recentemente, me deparei com uma demanda desafiadora vinda da equipe de desenvolvedores do time de soluções. Eles necessitavam converter um JSON em uma tabela relacional do Oracle (versão 19c), onde cada caminho (PATH) do JSON deveria ser representado como uma linha na tabela.

Para aqueles que acompanham meus artigos, sabem que tenho predileção por explorar desafios técnicos e abordá-los de maneira detalhada. Neste artigo, manterei essa abordagem e me concentrarei na explicação técnica, sem entrar em discussões sobre a viabilidade ou razões específicas para a solução. Meu objetivo é fornecer uma visão técnica clara e prática.

É importante salientar que a solução apresentada é preliminar e passível de evoluções significativas. No entanto, acredito que será de grande utilidade para aqueles que enfrentam demandas semelhantes.

Vamos lá!

Antes de mergulharmos no PL/SQL, é fundamental entender um pouco sobre JSON e sua estrutura. A base do JSON consiste em três tipos principais de valores: scalar, object e array.

Scalar

  • Um valor escalar é um valor único e simples.
  • Pode ser uma string, um número, um booleano, ou null.

Exemplo

"nome": "John Doe"
"idade": 25
"ativo": true
"cargo": null

Object

  • Um objeto é uma coleção não ordenada de pares chave-valor.
  • Cada chave deve ser uma string e deve ser única dentro do objeto.
  • Os valores podem ser qualquer um dos tipos JSON, incluindo objetos aninhados.

Exemplo

{
  "pessoa": {
    "nome": "John Doe",
    "idade": 25,
    "ativo": true
  },
  "endereço": {
    "rua": "123 Main St",
    "cidade": "Anytown",
    "país": "USA"
  }
}

Array

  • Um array é uma lista ordenada de valores.
  • Os valores podem ser qualquer um dos tipos JSON, incluindo objetos e outros arrays.
  • Os elementos são separados por vírgulas.

Exemplo

"cores": ["vermelho", "verde", "azul"]
"números": [1, 2, 3, 4, 5]
"pessoas": [
  {"nome": "Alice", "idade": 30},
  {"nome": "Bob", "idade": 35},
  {"nome": "Charlie", "idade": 28}
]

Esses três tipos formam a base para a estrutura de dados do JSON. A combinação de objetos e arrays permite representar estruturas de dados complexas e aninhadas, tornando o JSON uma escolha popular para a troca de dados em muitos contextos.

Agora que conhecemos um pouco sobre a estrutura do JSON, vamos começar a criar a nossa solução.

Primeiro vamos criar um TYPE que servirá como os campos de nossa tabela.

CREATE OR REPLACE TYPE t_json_array_value AS OBJECT (
  rank  NUMBER,
  key   CLOB,
  value CLOB
);

Onde:

Rank: É o nível do PATH do JSON
key: É o atributo
value: Valor do Atributo

Explicarei melhor isso mais a frente.

Agora criemos o TYPE que servirá como a tabela que conterá os campos.

CREATE OR REPLACE TYPE t_json_array AS TABLE OF t_json_array_value;

Agora vamos criar a nossa rotina. Ela se chamará get_json_values e será uma função recursiva.

Uma função recursiva é uma função que chama a si mesma durante sua execução. Em termos mais simples, é um processo no qual uma função resolve um problema que se reduz a instâncias menores do mesmo problema.

Nossa função conterá 4 parâmetros:

p_key: Será utilizado na chamada recursiva, e passará as chaves a serem decompostas
p_rank: Faz o controle do level do PATH na chamada recursiva
p_rank_fix: Informa o limite do level a ser decomposto
p_json: Arquivo JSON a ser decomposto

create or replace FUNCTION get_json_values
      (
       p_key      IN CLOB   DEFAULT NULL
      ,p_rank_fix IN NUMBER DEFAULT 99999
      ,p_rank     IN NUMBER DEFAULT 0
      ,p_json     IN CLOB   DEFAULT NULL
      )
   RETURN t_json_array
   IS
      v_key_list       JSON_KEY_LIST;
      v_json           JSON_OBJECT_T;
      v_array          JSON_ARRAY_T;
      v_json_array     t_json_array := t_json_array();
      v_json_array_ret t_json_array := t_json_array();
      v_key            CLOB;
      v_rank           NUMBER;

      -- Adiciona linhas no array   
      PROCEDURE add_line
                  (
                   p_rank  IN NUMBER
                  ,p_key   IN CLOB
                  ,p_value IN CLOB
                  )
      AS            
      BEGIN
         v_json_array.EXTEND;  
         
         v_json_array(v_json_array.COUNT) := 
            t_json_array_value(p_rank,p_key,p_value);
      
      END add_line;
   
   BEGIN 
      BEGIN
         -- Valida o JSON
         v_json  := JSON_OBJECT_T.parse(p_json);

         -- Busca as chaves do JSON
         v_key_list := v_json.GET_KEYS;  

         -- Varre as chaves      
         FOR x IN 1..v_key_list.COUNT 
         LOOP                                               
            -- Verifica se o JSON não está vazio 
            IF v_json IS NOT NULL
            THEN 
               -- Monta a chave
               v_key  := p_key || '[' || v_key_list(x) ||']';
               v_rank := p_rank + 1;
            
               -- Exibe chaves sem valores SCALAR
               IF v_json.GET_TYPE(v_key_list(x)) != 'SCALAR'
               THEN
                  add_line(p_rank  => v_rank,p_key   => v_key,p_value => NULL);

               END IF;                

               -- Decompõe JSON OBJECT
               IF  v_json.GET_TYPE(v_key_list(x)) = 'OBJECT'
               AND v_rank  <= p_rank_fix
               THEN   
                  BEGIN
                     -- Retorno da decomposição do OBJECT JSON
                     v_json_array_ret := 
                        get_json_values
                           (
                            p_rank     => v_rank
                           ,p_rank_fix => p_rank_fix
                           ,p_key      => v_key 
                           ,p_json     => v_json.GET_OBJECT(v_key_list(x)).TO_CLOB
                           );

                     -- Devolve OBJECT decomposto
                     FOR z IN 1..v_json_array_ret.COUNT 
                     LOOP
                        add_line
                           (
                            p_rank  => v_json_array_ret(z).rank
                           ,p_key   => v_json_array_ret(z).key
                           ,p_value => v_json_array_ret(z).value
                           );

                     END LOOP;   
                     
                  EXCEPTION
                     WHEN others THEN
                         RAISE_APPLICATION_ERROR(-20001,'[ OBJECT ] ' || TO_CHAR(SQLCODE) || ': ' || SQLERRM  || ' - ' || v_key);

                  END;                  

               -- Decompõe JSON ARRAY                           
               ELSIF v_json.GET_TYPE(v_key_list(x)) = 'ARRAY'
               AND   v_rank <= p_rank_fix
               THEN
                  BEGIN     
                     -- Carrega resultado do JSON ARRAY
                     v_array := v_json.GET_ARRAY(v_key_list(x));   

                     -- Varre o Array
                     FOR a IN 0..v_array.GET_SIZE -1
                     LOOP   
                        -- Monta a chave
                        v_key  := p_key || '[' || v_array.GET(a).TO_CLOB() ||']';
                        v_rank := p_rank + 1;
                     
                       -- Devolve o valor da chave
                       add_line(p_rank => v_rank, p_key => v_key, p_value => NULL);
                              
                       -- Retorno da decomposição do ARRAY JSON
                       v_json_array_ret := 
                           get_json_values
                              (
                               p_rank     => v_rank
                              ,p_rank_fix => p_rank_fix
                              ,p_key      => v_key 
                              ,p_json     => v_array.GET(a).TO_CLOB()
                              );

                        -- Devolve OBJECT decomposto                           
                        FOR b IN 1..v_json_array_ret.COUNT 
                        LOOP
                           add_line
                              (
                               p_rank  => v_json_array_ret(b).rank
                              ,p_key   => v_json_array_ret(b).key
                              ,p_value => v_json_array_ret(b).value
                              );

                        END LOOP;  

                     END LOOP;

                  EXCEPTION
                      WHEN others THEN
                         RAISE_APPLICATION_ERROR(-20001,'[ ARRAY ] ' || TO_CHAR(SQLCODE) || ': ' || SQLERRM || ' - ' || v_key);

                  END;

               -- Decompõe JSON SCALAR
               ELSIF v_json.GET_TYPE(v_key_list(x)) = 'SCALAR'
               AND   v_rank <= p_rank_fix
               THEN
                  BEGIN
                     -- Devolve reultado do JSON SCALAR
                     add_line(p_rank => v_rank, p_key => v_key, p_value => v_json.GET_STRING(v_key_list(x)));                     

                  EXCEPTION
                      WHEN others THEN
                         RAISE_APPLICATION_ERROR(-20001,'[ SCALAR ] ' || TO_CHAR(SQLCODE) || ': ' || SQLERRM || ' - ' || v_key);

                  END;
                  
               END IF; 
               
            END IF;      

         END LOOP;  

      END;

      RETURN(v_json_array);     

   END get_json_values;

Para executar a nossa função, vamos fazê-lo através de um SELECT. Vamos utilizar o seguinte JSON para isso:

{
  "nome": "Alice",
  "idade": 30,
  "solteiro": true,
  "cores_preferidas": ["azul", "verde", "amarelo"],
  "endereco": {
    "rua": "Rua das Flores",
    "numero": 123,
    "cidade": "Cidade Feliz"
  },
  "pontuacao": 8.7,
  "ativo": false,
  "descricao": null
}

Agora o nosso SELECT:

SELECT * FROM 
TABLE
   (
    get_json_values
       (
        p_json => '{
                    "nome": "Alice",
                    "idade": 30,
                    "solteiro": true,
                    "cores_preferidas": ["azul", "verde", "amarelo"],
                    "endereco": {
                      "rua": "Rua das Flores",
                      "numero": 123,
                      "cidade": "Cidade Feliz"
                    },
                    "pontuacao": 8.7,
                    "ativo": false,
                    "descricao": null
                   }
                   '
       )
   )

Ao executar teremos o resultado:

      RANK KEY                                                                              VALUE                                                                           
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 [nome]                                                                           Alice                                                                           
         1 [idade]                                                                          30                                                                              
         1 [solteiro]                                                                       true                                                                            
         1 [cores_preferidas]                                                                                                                                               
         1 [azul]                                                                                                                                                           
         1 [verde]                                                                                                                                                          
         1 [amarelo]                                                                                                                                                        
         1 [endereco]                                                                                                                                                       
         2 [endereco][rua]                                                                  Rua das Flores                                                                  
         2 [endereco][numero]                                                               123                                                                             
         2 [endereco][cidade]                                                               Cidade Feliz                                                                    

      RANK KEY                                                                              VALUE                                                                           
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 [pontuacao]                                                                      8.7                                                                             
         1 [ativo]                                                                          false                                                                           
         1 [descricao]                                                                                                                                                      

14 rows selected. 

Agora vamos testar utilizando o parâmetro rank. Pegando apenas os atributos do primeiro nível:

SELECT * FROM 
TABLE
   (
    get_json_values
       (
        p_rank_fix => 1,
        p_json => '{
                    "nome": "Alice",
                    "idade": 30,
                    "solteiro": true,
                    "cores_preferidas": ["azul", "verde", "amarelo"],
                    "endereco": {
                      "rua": "Rua das Flores",
                      "numero": 123,
                      "cidade": "Cidade Feliz"
                    },
                    "pontuacao": 8.7,
                    "ativo": false,
                    "descricao": null
                   }
                   '
       )
   )


      RANK KEY                                                                              VALUE                                                                           
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 [nome]                                                                           Alice                                                                           
         1 [idade]                                                                          30                                                                              
         1 [solteiro]                                                                       true                                                                            
         1 [cores_preferidas]                                                                                                                                               
         1 [azul]                                                                                                                                                           
         1 [verde]                                                                                                                                                          
         1 [amarelo]                                                                                                                                                        
         1 [endereco]                                                                                                                                                       
         1 [pontuacao]                                                                      8.7                                                                             
         1 [ativo]                                                                          false                                                                           
         1 [descricao]                                                                                                                                                      

11 rows selected. 

Agora vamos tentar com o nível 2 fixo:


      RANK KEY                                                                              VALUE                                                                           
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 [nome]                                                                           Alice                                                                           
         1 [idade]                                                                          30                                                                              
         1 [solteiro]                                                                       true                                                                            
         1 [cores_preferidas]                                                                                                                                               
         1 [azul]                                                                                                                                                           
         1 [verde]                                                                                                                                                          
         1 [amarelo]                                                                                                                                                        
         1 [endereco]                                                                                                                                                       
         2 [endereco][rua]                                                                  Rua das Flores                                                                  
         2 [endereco][numero]                                                               123                                                                             
         2 [endereco][cidade]                                                               Cidade Feliz                                                                    

      RANK KEY                                                                              VALUE                                                                           
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 [pontuacao]                                                                      8.7                                                                             
         1 [ativo]                                                                          false                                                                           
         1 [descricao]                                                                                                                                                      

14 rows selected. 

E é isso pessoal !

Como eu tinha citado no início do artigo, essa é uma versão preliminar da rotina. Talvez você encontre algum bug ou mal funcionamento que não encontrei nos meus testes.

Caso tenha alguma sugestão para melhorá-la, fique a vontade para comentar !

Referências

Um abraço

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 37

Sem votos ! Seja o primeiro a classificar !

10 comentários em “Convertendo JSON em tabela no Oracle: Explicação técnica e prática”

  1. giovano avatar

    Foi uma abordagem deveras interessante. Eu realmente não lembro se em algum momento eu já precisei utilizar os dados de um JSON dessa maneira, mas vejo potencial nessa solução.

    Você a utiliza em algum ambiente produtivo ?

  2. Avatar de Sergio Willians

    Sim, utilizo !

    Uma vantagem nessa solução, é que diferente de manipular JSON no Oracle na maneira tradicional, a curva de aprendizado para implementar essa solução é muito baixa.

    Já tenho uma versão mais otimizada e melhorada em alguns aspectos. Depois atualizarei o artigo.

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress