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
exatamente o que eu precisava! Obrigado Sergio
Fico feliz em saber que este artigo foi útil !
Útil!
Obrigado pelo comentário !
Me ajudou muito e deu-me muitas idéias de como implementar isso aqui riririri
Nossa, imagine minha satisfação ao saber disso ! Obrigado por compartilhar.
Genial!
Valeu !
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 ?
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.