LAB – Usando JSON no Oracle Database 12c

Introdução

JavaScript Object Notation (JSON) é um formato de texto para serialização de dados estruturados, que permite a troca de dados entre sistemas independente de linguagem de programação, usando um formato compacto que utiliza texto legível a humanos, no formato atributo-valor. Foi especificado por Douglas Crockford em 2000 e definido nos dois padrões RFC 7159 e ECMA-404.

O Oracle Database 12c suporta dados JSON (JavaScript Object Notation) nativamente com recursos de banco de dados relacional, incluindo transações, indexação, consulta declarativa e exibições desde a versão 12.1.0.2.

Um valor JSON deve ser um objeto, matriz, número ou string, ou um dos três nomes literais a seguir: false, null ou true.

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

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