JSON e o Oracle Database 12c

by Anish Shrivastava,
Por Alex Zaballa Revisado por Marcelo Pivovar - Solution Architect

No Oracle Database 12c (12.1.0.2), foi adicionado o suporte nativo ao JavaScript Object Notation (JSON). O JSON é um formato leve para intercâmbio de dados que é relativamente fácil para o ser humano ler e escrever, além de ser fácil para os softwares analisarem e gerarem. Apesar da adição recente, o JSON não é uma tecnologia nova e já faz parte do javascript há muito tempo. Este formato, criado por Douglas Crockford, está descrito no RFC 4627 e é muito utilizado como uma alternativa ao XML. Vamos a um exemplo comparativo entre o formato XML e o formato JSON:

JSON

{   
   "Nome": "Alex",    
  "SobreNome": "Zaballa",    
  "Certificacoes": [ "OCA", "OCP", "OCE", "OCS", "OCM" ]
}

XML

Copy

<Pessoa>

    <Nome>Alex</Nome>   
   <SobreNome>Zaballa</SobreNome>  
    <Certificacoes>         
 <Certificacao>OCA</Certificacao>   
        <Certificacao>OCP</Certificacao>  
         <Certificacao>OCE</Certificacao>   
        <Certificacao>OCS</Certificacao>    
       <Certificacao>OCM</Certificacao>   
    </Certificacoes> 
 </Pessoa>

Criando uma tabela para armazenar os dados:

Copy

CREATE TABLE tabela_json (   

 codigo NUMBER NOT NULL,  
   dados  VARCHAR2(4000), 
   CONSTRAINT tabela_json_pk PRIMARY KEY (codigo), 
   CONSTRAINT tabela_json_chk1 CHECK (dados IS JSON) 
 );

Verificando a tabela criada:

Copy

SQL> SELECT table_name,       

       column_name,       
       format,          
    data_type     
  FROM  user_json_columns; 
 

TABLE_NAME           COLUMN_NAME          FORMAT    DATA_TYPE  
-------------------- -------------------- --------- ------------- 
 TABELA_JSON          DADOS                TEXT      VARCHAR2

Como podemos verificar, nenhum tipo de dado novo foi adicionado ao ORACLE para o JSON, utilizamos um já existente, como VARCHAR2 ou CLOB por exemplo. O que muda, é que adicionamos uma CONTRAINT de CHECK do tipo IS JSON. Com isto em mente, vamos realizar alguns testes para entender melhor. Inserindo os dados na tabela:

Copy

INSERT INTO tabela_json (codigo, dados)

 VALUES (1,     
      '{    
   "Nome": "Alex",  
     "SobreNome": "Zaballa",   
    "Certificacoes": [ "OCA", "OCP", "OCE", "OCS", "OCM" ]  
     } 
 ');

Tentando inserir dados fora do formato JSON:

Copy

INSERT INTO tabela_json (codigo, dados)

 VALUES (2,'Joao da Silva');     
 ERROR at line 1:  ORA-02290: check constraint (TABELA_JSON_CHK1) violated

Buscando os dados:

Copy

SQL> select * from tabela_json;

       CODIGO   DADOS  ----------   --------------------------------------------------------------     
      1   {             
    "Nome": "Alex",     
            "SobreNome": "Zaballa",     
            "Certificacoes": [ "OCA", "OCP", "OCE", "OCS", "OCM" ]  
              }

Buscando os dados utilizando Dot-Notation:

Copy

SQL> SELECT t.dados.Nome,    

          t.dados.SobreNome,         
     t.dados.Certificacoes   
    FROM tabela_json t;

NOME                 SOBRENOME        CERTIFICACOES  

-------------------- ---------------- --------------------------------------  

Alex                 Zaballa          ["OCA","OCP","OCE","OCS","OCM"]

Atualizando os dados:

Copy

SQL> UPDATE tabela_json SET dados =  '{

    "Nome": "ALEX",    
  "SobreNome": "ZABALLA",     
 "Certificacoes": [ "OCA", "OCP", "OCE", "OCS", "OCM" ]  
     }' 
 WHERE codigo=1;
 
1 row updated.

Condições SQL que podem ser utilizadas com o JSON

IS JSON e IS NOT JSON: Testa se os dados são ou não do tipo JSON.
JSON_EXISTS: Testa a existência de um valor específico dentro dos dados JSON.

Copy

CREATE TABLE tabela_json2 (

   codigo NUMBER NOT NULL, 
   dados  VARCHAR2(4000));
 

INSERT INTO tabela_json2 (codigo, dados)  VALUES (1,   
       '{        
    "Nome": "Alex",     
       "SobreNome": "Zaballa"      
      }  
 ');

Copy

INSERT INTO tabela_json2 (codigo, dados)

 VALUES (2,'Joao da Silva');    
 SQL> select * from tabela_json2 where dados is not json;
 
    CODIGO  DADOS  ----------  ----------------------------------------    
       2  Joao da Silva

Funções SQL que podem ser utilizadas com o JSON

JSON_VALUE: encontra um valor JSON escalar especificado nos dados e retorna como um valor SQL.
JSON_QUERY: encontra um ou mais valores nos dados JSON e retorna estes valores.
JSON_TABLE: cria uma visão relacional dos dados JSON.

Copy

SQL> SELECT JSON_VALUE(t.dados, '$.Nome') AS Nome,    

          JSON_VALUE(t.dados, '$.SobreNome') AS SobreNome    
     FROM tabela_json t;
NOME                  SOBRENOME 
 --------------------  -------------------- 
 Alex                  Zaballa

JSON e índices Como os dados do tipo JSON são armazenados nos data types já existentes no ORACLE, a criação de B-Tree Index, Bitmap Index and Function-Based Indexes pode ser feita. Além destes, existe o suporte ao Oracle Text, habilitando o uso da função JSON_TEXTCONTAINS.

Copy

CREATE INDEX tabela_json_text_idx ON tabela_json (dados)

   INDEXTYPE IS CTXSYS.CONTEXT  
   PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');
 
SELECT COUNT(*)  
  FROM tabela_json    WHERE JSON_TEXTCONTAINS(dados, '$.SobreNome', 'ZABALLA');
  COUNT(*)  ----------      
  1
  

Execution Plan 
 ---------------------------------------------------------- 
 Plan hash value: 3967707323
 
--------------------------------------------------------------------------------------------- 
 |  Id | Operation        | Name                 |   Rows    | Bytes | Cost(%CPU) | Time     |  
 
 --------------------------------------------------------------------------------------------- 
 |   0 | SELECT STATEMENT |                      |     1     |  2014 |     4   (0)| 00:00:01 | 
 |   1 | SORT AGGREGATE   |                      |     1     |  2014 |            |          | 
 |*  2 | DOMAIN INDEX     | TABELA_JSON_TEXT_IDX |     1     |  2014 |     4   (0)| 00:00:01 | 
 ---------------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------  
 2 - access("CTXSYS"."CONTAINS"("TABELA_JSON"."DADOS",'{ZABALLA}  
                  INPATH(/SobreNome)')>0)

Referências: //docs.oracle.com/database/121/ADXDB/json.htm

Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 14 anos, é ORACLE ACE, certificado OCM Database 11G e conta com mais de 100 outras certificações em produtos da Oracle. Desde 2007 é funcionário da empresa Júpiter em Angola, alocado em um projeto no Ministério das Finanças. Alex também é fundador do Grupo de Usuários Oracle de Angola (GUOA) e membro do time OraWorld.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.