Manipulação de Dados JSON no Oracle Database 12c: Um Guia Prático
O JavaScript Object Notation (JSON) é um formato leve e de fácil leitura para a serialização de dados estruturados. Usado amplamente para troca de informações entre sistemas, o JSON é independente de linguagem de programação, adotando um formato compacto que segue a estrutura de pares atributo-valor. Desde sua criação por Douglas Crockford em 2000, o JSON se tornou um padrão de referência, definido nos documentos RFC 7159 e ECMA-404.
Com o Oracle Database 12c, o suporte nativo a dados JSON traz uma poderosa integração entre a flexibilidade desse formato e as funcionalidades robustas dos bancos de dados relacionais, como transações, indexação, consultas declarativas e visualizações. Essa compatibilidade foi introduzida a partir da versão 12.1.0.2, permitindo aos desenvolvedores manipular dados JSON diretamente dentro de suas bases de dados.
No JSON, um valor pode assumir várias formas, como objeto, array, número, string ou até mesmo os literais false, null ou true, tornando-o extremamente versátil para diversas aplicações.
Neste post, iremos criar e manipular informações em formato JSON, explorando seus principais usos e como integrá-lo de forma eficaz no Oracle Database 12c.
Pré-Requisitos
Neste Lab iremos criar e manipular informações em formato JSON, sendo necessário:
- Oracle Database 12.1.0.2 ou superior.
Descrição
Criando uma tabela relacional com uma coluna JSON e incluindo uma restrição de verificação ‘is json’ para garantir que a coluna contenha apenas dados JSON:
create table cep_json (
codigo number not null,
cep varchar2(2000),
constraint cep_json_pk primary key (codigo),
constraint cep_json_check_json check (cep is json)
);
Verificando a tabela criada, conclui-se que nenhum tipo de dados novo foi adicionado ao Oracle para o JSON, sendo utilizado um tipo já existente VARCHAR2:
select * from user_json_columns;
TABLE_NAME COLUMN_NAME FORMAT DATA_TYPE
---------- ----------- ------ ---------
CEP_JSON CEP TEXT VARCHAR2
Para este artigo utiliza-se JSON contendo informações sobre os CEPs da Avenida T-63, os dados foram obtidos por meio da API ViaCep, disponível no site https://viacep.com.br/, a consulta foi realizada utilizando a seguinte chave de busca:
https://viacep.com.br/ws/GO/Goiania/Avenida+t+63/json/A informação obtida foi inserida na tabela cep_json:
insert into cep_json (codigo, cep) values (1,
'{
"cep": "74280-230",
"logradouro": "Avenida T 63",
"complemento": "de 1370 a 1954 - lado par",
"bairro": "Nova Suiça",
"localidade": "Goiânia",
"uf": "GO",
"unidade": "",
"ibge": "5208707",
"gia": ""
}');Ao inserir os dados fora do formato JSON obtemos a seguinte mensagem de erro:
Erro a partir da linha : 1 no comando –
insert into cep_json (codigo, cep) values (11,'”cep”: “74823-345”,’)
Relatório de erros –
ORA-02290: restrição de verificação (SYS.CEP_JSON_CHECK_JSON) violada
Para consultar os dados armazenados na tabela cep_json, temos duas opções de consulta, a forma tradicional ou o formato Dot-Notation:
select * from cep_json;CODIGO CEP
---------- -----------------------
1 "{
"cep": "74280-230",
"logradouro": "Avenida T 63",
"complemento": "de 1370 a 1954 - lado par",
"bairro": "Nova Suiça",
"localidade": "Goiânia",
"uf": "GO",
"unidade": "",
"ibge": "5208707",
"gia": ""
}"
select j.cep.cep, j.cep.logradouro, j.cep.bairro from cep_json j;
CEP LOGRADOURO BAIRRO
——— ———— ———
74280-230 Avenida T 63 Nova Suíça
74335-102 Avenida T 63 Anhanguera
Nas consultas utilizando o JSON, é possível utilizar condições SQL para filtrar as informações desejadas:
- IS JSON e IS NOT JSON – Para testar se os dados são ou não do tipo JSON.
- JSON_EXISTS – Para testar existência de um valor especifico dentro dos dados JSON.
select * from cep_json where cep is json;Para procurar a existência do atributo “complemento” na coluna CEP e valor “Setor Bueno” no atributo “bairro”, utilizaremos a condição JSON_EXISTS:
select * from cep_json where json_exists(cep,'$[*].complemento');
select * from cep_json where json_exists(cep,'$?(@.bairro == "Setor Bueno")');
select * from cep_json where json_exists(cep,'$?(@.bairro like "%Bueno")');Outra forma de pesquisar um valor nos dados JSON é utilizando a função JSON_TEXTCONTAINS, porém, este recurso só estará disponível ativando o índice de contexto, caso contrário receberá a seguinte mensagem de erro:
ERRO na linha 1:
ORA-40467: JSON_TEXTCONTAINS() não pode ser avaliado sem um índice de contexto ativado por JSON
Para a criação do index de contexto:
create index idx_json_cep_01
on cep_json (cep)
indextype is CTXSYS.CONTEXT
parameters ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');Para consultar o valor “Bueno” na coluna CEP, no atributo “bairro” dos dados JSON:
select * from cep_json where json_textcontains(cep,'$.bairro','Bueno');Para consultar o valor “Bueno” na coluna CEP, por todos os atributos existentes nos dados JSON.
select * from cep_json where json_textcontains(cep,'$','Bueno');
select * from cep_json where json_textcontains(cep,'$','74%');Conclui-se que o banco de dados Oracle 12c facilita a utilização e manipulação de dados no formato JSON, dessa forma, torna-se versátil o armazenamento de dados de terceiros, pois viabiliza o armazenamento e a recuperação de um objeto JSON, utilizando apenas uma tabela simples com duas ou mais colunas.
Referências
Documentação relacionada a Dot Nation: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/simple-dot-notation-access-to-json-data.html#GUID-7249417B-A337-4854-8040-192D5CEFD576
JSON in Oracle Database: https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246
Introducing JSON: https://www.json.org/
RFC 7159: https://tools.ietf.org/html/rfc7159
ECMA-404: https://www.ecma-international.org/publications/standards/Ecma-404.htm
