Manejar archivos JSON en las bases de datos Oracle era un dolor hasta que llegó la versión de Oracle Database 12cR2, esto debido a que muchas de las funcionalidades de extraer datos, recorrer archivos JSON, añadir nuevas propiedades, convertir formatos, entre otros; no existían, es por ello que hoy les comparto una guía donde les explico los diferentes comandos a utilizar para manipular archivos o datos en formato JSON en nuestra base de datos.

Nota: Las funciones, paquetes y formatos JSON que veremos aquí, funcionan apartir de Oracle Database 12cR2, anterior a esa versión se debe usar otras formas manuales para manipular objetos JSON.

Fuente: livesql.oracle.com

Qué es JSON y por qué es crucial en PL/SQL:

JSON es el formato estándar para el intercambio de datos en aplicaciones web. En PL/SQL, podemos trabajar con JSON para almacenar, recuperar y manipular datos de una manera que se ajuste perfectamente a nuestras necesidades.

Creando un objeto JSON:

Ahora, veamos cómo podemos crear objetos JSON en PL/SQL utilizando funciones como JSON_OBJECT.

Primero crearemos un objeto JSON, será un poco complejo, esto con el fin de poder abarcar todas las secciones que podríamos encontrar en un archivo JSON:

DECLARE
   v_json_obj JSON_OBJECT_T := JSON_OBJECT_T();
BEGIN
   -- Crear el objeto JSON
   v_json_obj.PUT('Account.Holder', 'Gari Kaspárov');
   v_json_obj.PUT('Account.CurrencyCode', 'CRC');
   v_json_obj.PUT('Account.ProductName', 'Cuenta');
   v_json_obj.PUT('Account.State', '1');
   v_json_obj.PUT('IsSuccessful', true);
   v_json_obj.PUT('Errors[0].Account', 123456789);
   v_json_obj.PUT('Errors[0].Code', 'asde23');
   v_json_obj.PUT('OperationId', '31e32f8f-6da5-4d3c-8434-203d8f53ed89');
   
   DBMS_OUTPUT.PUT_LINE(v_json_obj.TO_STRING());
END;

Salida:

{
   "Account": {
      "Holder": "Gari Kaspárov",
      "CurrencyCode": "CRC",
      "ProductName": "Cuenta",
      "State": "1"
   },
   "IsSuccessful": true,
   "Errors": [
      {
         "Account": 123456789,
         "Code": "asde23"
      }
   ],
   "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89"
}

Convertir un VARCHAR a formato JSON:

Si nos dan un JSON en un VARCHAR, lo tenemos almacenado en algún campo de la base de datos o bien lo recibimos de la consulta a un API externa, podemos convertirlo a JSON_OBJECT_T de la siguiente manera:

DECLARE
   v_json_str VARCHAR2(4000) := '{
      "Account": {
         "Holder": "Gari Kaspárov",
         "CurrencyCode": "CRC",
         "ProductName": "Cuenta",
         "State": "1"
      },
      "IsSuccessful": true,
      "Errors": [
         {
            "Account": 123456789,
            "Code": "asde23"
         }
      ],
      "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89"
   }';

   v_json_obj JSON_OBJECT_T;
BEGIN
   -- Convertir el JSON de VARCHAR a JSON_OBJECT_T
   v_json_obj := JSON_OBJECT_T.PARSE(v_json_str);

   -- Imprimir el objeto JSON
   DBMS_OUTPUT.PUT_LINE(v_json_obj.TO_STRING());
END;

Si quisieramos convertirlo de JSON_OBJECT_T a VARCHAR, podemos usar la siguiente función: v_json_obj.TO_STRING()

Leer y almacenar las propiedades de un JSON

Luego de tener el json convertido al formato de manipulación de la data, para extraer sus propiedades haremos lo siguiente:

DECLARE
   v_holder VARCHAR2(50);
   v_currency_code VARCHAR2(3);
   v_account_number INTEGER;
   v_json_str VARCHAR2(4000) := '{
      "Account": {
         "Holder": "Gari Kaspárov",
         "CurrencyCode": "CRC",
         "ProductName": "Cuenta",
         "State": "1"
      },
      "IsSuccessful": true,
      "Errors": [
         {
            "Account": 123456789,
            "Code": "asde23"
         }
      ],
      "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89"
   }';

   v_json_obj JSON_OBJECT_T;
BEGIN
   --Convertimos el JSON de VARCHAR a JSON_OBJECT_T
   v_json_obj := JSON_OBJECT_T.PARSE(v_json_str);

   -- Leemos las propiedades del objeto JSON
   v_holder := v_json_obj.GET_STRING('Account.Holder');
   v_currency_code := v_json_obj.GET_STRING('Account.CurrencyCode');
   v_account_number := v_json_obj.GET_NUMBER('Errors[0].Account');
   
   -- Imprimir los resultados
   DBMS_OUTPUT.PUT_LINE('Holder: ' || v_holder);
   DBMS_OUTPUT.PUT_LINE('Currency Code: ' || v_currency_code);
   DBMS_OUTPUT.PUT_LINE('Account Number: ' || v_account_number);
END;

Salida:

Holder: Gari Kaspárov
Currency Code: CRC
Account Number: 123456789

En caso de que tuvieramos varios objetos dentro del array Errors, lo podríamos recorrer de la siguiente manera:

DECLARE
   v_json_str VARCHAR2(4000) := '{
      "Account": {
         "Holder": "Gari Kaspárov",
         "CurrencyCode": "CRC",
         "ProductName": "Cuenta",
         "State": "1"
      },
      "IsSuccessful": true,
      "Errors": [
         {
            "Account": 123456789,
            "Code": "asde23"
         },
         {
            "Account": 987654321,
            "Code": "qwerty"
         }
      ],
      "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89"
   }';

   v_json_obj JSON_OBJECT_T;
   v_errors_array JSON_ARRAY_T;
   v_error_obj JSON_OBJECT_T;
   v_account NUMBER;
   v_code VARCHAR2(50);
BEGIN
   -- Convertir el JSON de VARCHAR a JSON_OBJECT_T
   v_json_obj := JSON_OBJECT_T.PARSE(v_json_str);

   -- Obtener el array 'Errors'
   v_errors_array := v_json_obj.GET_ARRAY('Errors');

   -- Iterar a través de los elementos del array
   FOR i IN 1..v_errors_array.COUNT LOOP
      -- Obtener el objeto JSON dentro del array
      v_error_obj := v_errors_array.GET_OBJECT(i);

      -- Obtener propiedades del objeto JSON dentro del array
      v_account := v_error_obj.GET_NUMBER('Account');
      v_code := v_error_obj.GET_STRING('Code');

      -- Imprimir los resultados
      DBMS_OUTPUT.PUT_LINE('Error ' || i || ':');
      DBMS_OUTPUT.PUT_LINE('   Account: ' || v_account);
      DBMS_OUTPUT.PUT_LINE('   Code: ' || v_code);
   END LOOP;
END;

Cómo añadirle nuevas propiedades al objeto JSON:

Para añadir nuevas propiedades al objeto JSON, usaremos la función PUT():

BEGIN
   v_json_obj.PUT('Account.NewProperty', 'Nueva Propiedad');
   v_json_obj.PUT('AdditionalInfo', 'Información Adicional');
   
   DBMS_OUTPUT.PUT_LINE(v_json_obj.TO_STRING());
END;

Salida:

{
   "Account": {
      "Holder": "Gari Kaspárov",
      "CurrencyCode": "CRC",
      "ProductName": "Cuenta",
      "State": "1",
      "NewProperty": "Nueva Propiedad"
   },
   "IsSuccessful": true,
   "Errors": [
      {
         "Account": 123456789,
         "Code": "asde23"
      }
   ],
   "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89",
   "AdditionalInfo": "Información Adicional"
}

Eliminar propiedades de un json:

Para eliminar una propiedad del objeto json, usaremos la función REMOVE():

DECLARE
   v_json_str VARCHAR2(4000) := '{
      "Account": {
         "Holder": "Gari Kaspárov",
         "CurrencyCode": "CRC",
         "ProductName": "Cuenta",
         "State": "1"
      },
      "IsSuccessful": true,
      "Errors": [
         {
            "Account": 123456789,
            "Code": "asde23"
         },
         {
            "Account": 987654321,
            "Code": "qwerty"
         }
      ],
      "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89"
   }';

   v_json_obj JSON_OBJECT_T;
BEGIN
   -- Convertir el JSON de VARCHAR a JSON_OBJECT_T
   v_json_obj := JSON_OBJECT_T.PARSE(v_json_str);

   -- Eliminamos la propiedad 'State' del objeto 'Account'
   v_json_obj.GET_OBJECT('Account').REMOVE('State');

   DBMS_OUTPUT.PUT_LINE(v_json_obj.TO_STRING());
END;

Salida:

{
   "Account": {
      "Holder": "Gari Kaspárov",
      "CurrencyCode": "CRC",
      "ProductName": "Cuenta"
   },
   "IsSuccessful": true,
   "Errors": [
      {
         "Account": 123456789,
         "Code": "asde23"
      },
      {
         "Account": 987654321,
         "Code": "qwerty"
      }
   ],
   "OperationId": "31e32f8f-6da5-4d3c-8434-203d8f53ed89"
}

De esta manera eliminamos la propiedad «State» del objeto JSON.

Conclusiones:

El uso de objetos JSON para el transporte y manejo de la información, nos ayuda a mejorar la forma en como tratamos los datos, esto nos da un orden y mejor claridad sobre qué datos pertenecen a qué secciones, además de que facilita el desarrollo de APIs y el consumo de las mismas, siendo un estándar de uso para transportar la información.

Post Views: 265

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *