JSON e o Oracle Database 12c

Por Alex Zaballa
Postado em Dezembro 2014

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

<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:

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:

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:

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:

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:

SQL> select * from tabela_json;
 
    CODIGO   DADOS
----------   --------------------------------------------------------------
         1   {
               "Nome": "Alex",
               "SobreNome": "Zaballa",
               "Certificacoes": [ "OCA", "OCP", "OCE", "OCS", "OCM" ]
             }

Buscando os dados utilizando Dot-Notation:

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:

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.
CREATE TABLE tabela_json2 (
  codigo NUMBER NOT NULL,
  dados  VARCHAR2(4000));

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

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.
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.

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:
http://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.