Trabalhando com JSON no Oracle Nativamente em PL/SQL
Muito se tem falado sobre o fim da vida de PL/SQL: que escrever código dentro do próprio banco o deixa extremamente lento, que a Oracle já indica que seu foco é em JAVA (externo ao banco), etc, etc…
O fato concreto é que PL/SQL se mantém uma linguagem sempre atualizada e praticamente qualquer coisa que você faz com JAVA, conseguirá fazer em PL/SQL (pode ser mais trabalhoso, SIM!, mas vai fazer).
JSON + PL/SQL
Podemos dizer que JSON seria hoje a língua franca de comunicação entre sistemas, e por se tratar de texto, seria muito complicado tentar ler a String (ou VARCHAR2, ou CLOB) do Oracle e tentar fazer a leitura do JSON no braço. Seria necessário“parsear” usando SUBSTR cada sequencia de caracteres em um objeto JSON complexo.
Antigamente, e em versões antigas do Oracle, se você queria trabalhar com JSON, precisava de uma biblioteca auxiliar feita por terceiros para tal. Um exemplo disto é esta. Contudo, a partir da versão Oracle Database 12c release 2 (12.1.0.2) a coisa mudou e já foi possível tratar JSON nativamente. A descrição do uso de JSON que falaremos é a que consta na versão Oracle 19c (a mais recente até a escrita deste artigo).
JSON em Tabelas
Primeiramente vamos tentar entender como inserir JSON (e apenas JSON) em uma tabela do Oracle. Isso é fácil, basta colocar uma restrição de que aquela coluna no banco só pode ser JSON, como demonstrado abaixo.
create table rede_social (
id NUMBER(3),
amigos VARCHAR2(4000),
CONSTRAINT “amigos_check” CHECK (amigos IS JSON) ENABLE
)Agora observe o resultado do comando abaixo:
INSERT INTO rede_social (id, amigos) VALUES (1,'não é JASON')
Relatório de erros -
ORA-02290: restrição de verificação (amigos_check) violadaVeja que nesta tabela chamada rede_social, se tentamos colocar uma VARCHAR2 comum em um campo que espera um objeto JSON, teremos uma exceção de violação da restrição criada chamada de “amigos_check”.
Agora observe o próximo insert:
INSERT INTO rede_social (id, amigos) VALUES (1,'[{"a":"3"}]');
1 linha inserida.Assim percebemos que o Oracle está preparado para manter objetos JSON de forma nativa. No exemplo em questão foi utilizado um VARCHAR2(4000) mas nada impede de ser usado um campo CLOB que pode suportar até 4GB de dados. Atenção: O tratamento dado é em respeito ao dado ser um JSON bem formado, e não necessariamente do mesmo tipo de objeto JSON. Ou seja, se no campo amigos eu colocar [{“a”:”3"}] ou {“outra_coisa”:”144"}, ambos serão aceitos pois ambos são objetos JSON bem formados.
Consultando Objetos JSON no Oracle
Assim como consultamos tabelas, podemos consultar objetos JSON e os valores de suas chaves. Por exemplo, se inserirmos, como dito acima a seguinte linha:
INSERT INTO rede_social (id, amigos) VALUES (1,'{"outra_coisa":"144"}')Poderemos consultar o valor 144 da seguinte forma:
select chave.amigos.outra_coisa from rede_social chave
OUTRA_COISA
---------------------------------------
144Trabalhando com Dados Complexos
A ideia agora é saber como trabalhamos com dados JSON no Oracle. Neste link está a página oficial do Oracle 19c acerca deste assunto. O que vou tratar aqui é como percorrer um objeto JSON complexo no Oracle e extrair informação para ser usada em qualquer processamento PL/SQL. Para ilustar o cenário vamos imaginar uma rede social onde existem amigos, amigos de amigos e amigos de amigos de amigos. Tudo isso em um objeto JSON. A hierarquia de amigos é esta:
E que pode ser representada pelo seguinte JSON bem formado:
Supondo que recebemos este retorno JSON de uma rede social e o salvamos em uma tabela no Oracle. Agora nós queremos transformar essa estrutura em um elemento tabela para saber quem é amigo de quem de forma mais direta (não transitiva), ou seja, o tratamento que faremos no PL/SQL precisará de uma estrutura: [(a1, a2), (a1, a3)…] que signifique: a1 é amigo de a2 (como também a2 é amigo de a1); e a1 é amigo de a3 (como também a3 é amigo de a1).
Implementação em PL/SQL
Construiremos um procedimento recursivo que vai mergulhar neste JSON dado acima e retornará a tabela dada acima. Primeiro vamos definir as estruturas em PL/SQL que precisaremos.
-- Um registro de amigos
TYPE tp_reg_amigos IS RECORD (
amigo1 VARCHAR2(100),
amigo2 VARCHAR2(100)
);-- Uma lista de amigos
TYPE tp_tb_lista_amigos IS TABLE OF tp_reg_amigos INDEX BY BINARY_INTEGER;
Iremos utilizar dois recursos do Oracle para tratar de JSON: JSON_OBJECT_T e JSON_ARRAY_T. Por intuição sabemos que JSON_OBJECT_T é um handler para objetos JSON e que JSON_ARRAY_T é um handler para arrays JSON. Vamos considerar que o objeto JSON com a árvore de amigos será recuperado da tabela rede_social que criamos, ele a princípio será carregado como um VARCHAR2 e utilizaremos o comando JSON_ARRAY_T.parse() para transformar o VARCHAR2 em array JSON entendível pelo PL/SQL.
Sabemos que recebemos uma lista de amigos e temos que encontrar todos os relacionamentos de amigos de amigos. Assim uma funcionalidade importante que JSON_ARRAY_T nos dá é JSON_ARRAY_T.get_size(), que diz o tamanho do array JSON. Assim, só vamos passar qualquer coisa para a nossa função recursiva se houver 1 ou mais elementos no array. Assim, na nossa função recursiva a primeira coisa a verificar é se o Array JSON contém elementos e chamar a função recursiva pr_popular_lista_amigos.
Saída:
AMIGO 1: Fernando é amigo do AMIGO 2: João
AMIGO 1: Fernando é amigo do AMIGO 2: Maria
AMIGO 1: Fernando é amigo do AMIGO 2: Carlos
AMIGO 1: João é amigo do AMIGO 2: Antônio
AMIGO 1: João é amigo do AMIGO 2: Bianca
AMIGO 1: Maria é amigo do AMIGO 2: Tatiana
AMIGO 1: Carlos é amigo do AMIGO 2: Karina
AMIGO 1: Carlos é amigo do AMIGO 2: RobertoProcedimento PL/SQL concluído com sucesso.
Vamos agora linha a linha detalhando a solução:
- Linhas 14 e 15: Os parâmetros de entrada, observar que a lista é do tipo IN/OUT;
- Linha 17: Criada variável do tipo JSON_OBJECT_T para podermos acessar um objeto JSON;
- Linha 20: Criada variável do tipo array para tratar quando houver detalhes;
- Linha 22: Seria uma dos casos base da função recursiva, se o array for vazio a procedure PL/SQLdeve retornar;
- Linha 27: Fazendo parse no objeto JSON utilizando JSON_OBJECT_T.parse();
- Linha 28: Recuperando o array de amigos de um objeto;
- Linhas 32 e 33: Adicionando na estrutura de retorno a ligação de amigos;
- Linha 38: Chamada recursiva, caso haja conteúdo no array de detalhes;
Espero que este artigo tenha ajudado a tratar JSON com PL/SQL e a provar que a linguagem está mais viva do que nunca.
