Manipulando objetos JSON no PL/SQL: Um guia simples e rápido

PL/SQL e JSON

Olá pessoal !

Tenho recebido diversos e-mails com dúvidas sobre a interação de JSON com PL/SQL. Então resolvi escrever algo para demonstrar de maneira simples e rápida sobre como manipular objetos JSON no PL/SQL.

Então vamos lá !

Os testes foram feitos utilizando o Oracle 19c e baseados na documentação do 21c. Não se preocupem com isso ! 🙂

Abaixo um script com diversos testes que montei. Cada um fazendo uma manipulação específica do objeto JSON:

DECLARE
  v_objec_json   JSON_OBJECT_T;
  v_objec_json_2 JSON_OBJECT_T;
  v_eleme_json   JSON_ELEMENT_T;
  v_array_json   JSON_ARRAY_T;
  v_array_json_2 JSON_ARRAY_T;
 
  keys           JSON_KEY_LIST;
  array_size     NUMBER := 0;
  keys_string    VARCHAR2(1200);

BEGIN
  -- Inicializa o JSON Array
  v_array_json := new JSON_ARRAY_T;
 
  -- Faz a validação (parse - validando o array) do JSON e converte a string para JSON OBJECT
  -- O Parse tem construtor para VARCHAR2,BLOB e CLOB
  v_objec_json :=
       JSON_OBJECT_T.parse('{"PONumber": 1600,
                              "Reference": "ABULL-20140421",
                              "Requestor": "Alexis Bull",
                              "User": "ABULL",
                              "CostCenter": "A50",
                              "ShippingInstructions": {"name": "Alexis Bull",
                                                       "Address": {"street": "200 Sporting Green",
                                                       "city": "South San Francisco",
                                                       "state": "CA",
                                                       "zipCode": 99236,
                                                       "country": "United States of America"},
                              "Phone": [{"type": "Office", "number": "909-555-7307"},
                                        {"type": "Mobile", "number": "415-555-1234"}]},
                              "Special Instructions": null,
                              "AllowPartialShipment": true,
                              "LineItems": [{"ItemNumber": 1,
                                             "Part": {"Description": "One Magic Christmas",
                                                      "UnitPrice": 19.95,
                                                      "UPCCode": 13131092899},
                                                      "Quantity": 9.0},
                                            {"ItemNumber": 2,
                                             "Part": {"Description": "Lethal Weapon",
                                             "UnitPrice": 19.95,
                                             "UPCCode": 85391628927},
                                             "Quantity": 5.0}],
                              "totalQuantity": 14,
                              "totalPrice": 279.3}');
                             
  -- Busca as chaves do root do JSON                            
  keys := v_objec_json.GET_KEYS;
 
  -- Varre o array com as chaves e as exibe
  DBMS_OUTPUT.put_line('********************************');
  DBMS_OUTPUT.put_line('*      Varrendo as chaves     **');
  DBMS_OUTPUT.put_line('********************************');
 
  FOR i IN 1..keys.COUNT
  LOOP
     DBMS_OUTPUT.put_line(keys(i));

  END LOOP;

  -- Busca os valores dos atributos
  DBMS_OUTPUT.put_line('********************************');
  DBMS_OUTPUT.put_line('** Varrendo os elementos {e}  **');
  DBMS_OUTPUT.put_line('********************************');
 
  BEGIN
     -- Treat é utilizado para CAST, principalmente quando se coloca um JSON_ELEMENT em um JSON_OBJECT
     -- Ou mesmo só para iniciar a variável
     v_eleme_json := TREAT (v_eleme_json AS JSON_OBJECT_T);
     
     -- Varre o array com as chaves e as exibe
     FOR i IN 1 .. keys.COUNT
     LOOP
        v_eleme_json := v_objec_json.GET(keys(i));
        DBMS_OUTPUT.put_line(keys(i) || ':' || v_eleme_json.TO_STRING );

     END LOOP;    
 
  END;
 
  -- Busca os valores dos atributos
  DBMS_OUTPUT.put_line('********************************');
  DBMS_OUTPUT.put_line('**   Varrendo os arrays [e]   **');
  DBMS_OUTPUT.put_line('********************************');  
   
  BEGIN
     -- Atribui os valores
     v_array_json_2 := v_objec_json.GET_ARRAY('LineItems');
     
     -- Varre o Array
     FOR i IN 0..v_array_json_2.GET_SIZE -1
     LOOP
        BEGIN
           -- Você pode habilitar o nível de erro entre 0 e 4 se quiser 🙂
           --v_objec_json.on_error(0);  
           
           v_objec_json_2 := JSON_OBJECT_T(v_array_json_2.GET(i));
           
           -- Possíveis GETs
           -- get(key VARCHAR2)           RETURN JSON_ELEMENT_T
           -- get_String(key VARCHAR2)    RETURN VARCHAR2
           -- get_Number(key VARCHAR2)    RETURN NUMBER
           -- get_Date(key VARCHAR2)      RETURN DATE
           -- get_Timestamp(key VARCHAR2) RETURN TIMESTAMP
           -- get_Boolean(key VARCHAR2)   RETURN BOOLEAN
           -- get_Clob(key VARCHAR2)      RETURN CLOB
           -- get_Blob(key VARCHAR2)      RETURN BLOB
           -- get_Object(key VARCHAR2)    RETURN JSON_OBJECT_T
           -- get_Array(key VARCHAR2)     RETURN JSON_ARRAY_T
           
           DBMS_OUTPUT.put_line('Part     : ' || v_objec_json_2.GET_OBJECT('Part').GET_STRING('Description') || ' - ' ||
                                'Quantity : ' || v_objec_json_2.GET_NUMBER('Quantity')                       || ' - ' ||
                                'UnitPrice: ' || v_objec_json_2.GET_OBJECT('Part').GET_STRING('UnitPrice'));
        EXCEPTION
           WHEN others THEN
               DBMS_OUTPUT.put_line(SQLERRM);

        END;  
     END LOOP;
     
     DBMS_OUTPUT.put_line(v_objec_json.to_string);
 
     -- É possível manipular o documento JSON utilizando os seguintes comandos: remove,rename_key e put

     -- Excluindo uma chave
     v_objec_json.REMOVE('PONumber');
     DBMS_OUTPUT.put_line('REMOVE     : ' || v_objec_json.to_string);
     
     -- Renomeando uma chave
     v_objec_json.RENAME_KEY('Reference','References');
     DBMS_OUTPUT.put_line('RENAME_KEY : ' || v_objec_json.to_string);
     
     -- Incluindo uma chave e valor
     -- Possíveis PUTs
     -- put(key VARCHAR2, value JSON_ELEMENT_T)
     -- put(key VARCHAR2, value VARCHAR2)
     -- put(key VARCHAR2, value NUMBER)
     -- put(key VARCHAR2, value BOOLEAN)
     -- put(key VARCHAR2, value DATE)
     -- put(key VARCHAR2, value TIMESTAMP)
     -- put_Null(key VARCHAR2)
     
     v_objec_json.PUT('PONumber',1600);
     DBMS_OUTPUT.put_line('PUT        : ' || v_objec_json.to_string);    

  END;


END;

Sugiro que rodem o bloco PL/SQL, façam testes e o alterem para investigar novas possibilidades de uso. Caso tenham alguma dúvida, peço que perguntem através dos comentários !

Espero que seja de alguma valia ! 🙂

Referências

• PL/SQL
• SQL