JSON en Oracle 18c

Por Renato Coronado
INTRODUCCIÓN
Primero, ¿qué es JSON? JSON corresponde a las siglas Javascript Object Notation o Notación de Objetos de Javascript, es un formato de intercambio de archivos, que le facilita a los programadores el intercambio de datos. Resulta ser sencillo de leer y escribir, así como simple de generar para las máquinas.

Así mismo, JSON admite 6 tipos de valores:

  • Números.

  • Cadenas de texto

  • Booleanos

  • Arreglos

  • Null

  • Objetos

Ventajas que nos ofrece JSON: 

  • Fácil entendimiento

  • Su sencillez lo posiciona sobre XML.

  • Es mucho más ligero en las transacciones.

  • Se serializa y/o deserializa más rápido.

  • La mayoría de los lenguajes de programación le brindan soporte.


JSON Y ORACLE

Oracle 18c ahora admite JSON de forma nativa con características de bases de datos relacionales, que incluyen transacciones, indexación, consultas declarativas y vistas. Los datos JSON a menudo se han almacenado en bases de datos NoSQL. Oracle nos brinda el soporte nativo para JSON nos proporciona todos los beneficios de las funciones de bases de datos relacionales para usar con JSON, incluidas las transacciones, la indexación, las consultas declarativas y las vistas.

En Oracle Database, los datos JSON se almacenan utilizando los tipos de datos SQL comunes como lo son VARCHAR2, CLOB y BLOB (a diferencia datos XML, que se almacenan utilizando XMLType). Así mismo, Oracle recomienda se use una restricción de verificación para asegurarse de que los valores de columna sean instancias JSON válidas.

Por definición, los datos JSON textuales se codifican utilizando una codificación Unicode, ya sea UTF-8 o UTF-16. Puede usar datos textuales que se almacenan en un conjunto de caracteres no Unicode como si fueran datos JSON, pero en ese caso Oracle Database convierte automáticamente el conjunto de caracteres a UTF-8 cuando procesa los datos.


SOPORTE

Oracle 12.1 introdujo nuevos features relacionados a JSON como lo son:

  • Funciones condicionales IS JSON, JSON_EXISTS and JSON_CONTAINS.

  • Funciones: IS JSON, JSON_EXISTS and JSON_CONTAINS.

  • IS JSON, JSON_EXISTS and JSON_CONTAINS.

Actualmente Oracle 18c ha agregado lo siguiente:

  • Nuevo Manual - JSON Developers Guide.

  • Soporte SQL/JSON (JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG).

  • Condicionales y features de Oracle 12.1 son también soportadas.

  • Nuevos tipos de objetos PL/SQL: JSON_ELEMENT_T, JSON_OBJECT_T, JSON_SCALAR_T, JSON_KEY_LIST, que nos permiten manipular un documento JSON nativo desde PL/SQL.



IMPLEMENTACIÓN
ESCENARIO PROPUESTO:

Con la finalidad de una mejor comprensión de  JSON y sus funcionalidades en Oracle 18c, definiremos un escenario base, en donde desarrollaremos consultas, inserciones, lectura de data y demás.

Como escenario se planteará, no alejándonos del tema académico, una base de datos que registre las carreras universitarias para una universidad. 

Es importante que, cada ciclo a la hora de matrícula, se pueda registrar los profesores que tiene cada carrera(como bien sabemos, hay profesores que enseñan cursos en distintas facultades). Así mismo, últimamente se hizo una actualización de foto a los profesores mayores, dado que aún mantenían su foto de hace muchos años. Aquellos profesores que no han actualizado su imagen, el sistema toma una foto por defecto y no es necesario poner el link de la imagen actualizada. Este último requerimiento podría complicarse mucho, debido a que tenemos profesores de distintas edades: hay profesores que actualizaron su foto y la url de su imagen ha cambiado, mientras que otros tienen su foto tal cual y no necesita ser registrada. Así mismo, se tiene algunos profesores que están trabajando a la par que enseñan, se necesita saber de ese dato dado que con ellos se coordinará de forma más personalizada para que no se cruce con su horario de trabajo, en el caso de que el profesor esté trabajando, debe ser registrado y se pondrá la empresa en la que trabaja como referencia. En caso de dedicarse solo a la enseñanza, este campo no sería necesario.

Debido a que no se tiene un esquema fijo con respecto a la estructura(puede estar el campo img como no, como también puede estar el campo work como no), podría convertirse un proceso muy tedioso y engorroso modelarlo bajo un esquema relacional, es por ello que JSON podría ayudarnos a manejar este problema.

Presentaremos la estructura de la tabla Carrera, cabe recalcar que algunos campos han sido omitidos para su simplicidad: el objetivo es aprender acerca de la funcionalidad que tiene para ofrecernos JSON en Oracle.

(Indicamos JSON al costado debido a que el campo profesores almacenará un JSON de los profesores correspondientes).

A continuación mostraremos una posible entrada en el campo profesores en formato JSON(escogimos una muestra reducida para motivos de simplicidad, ya que se tiene muchísimos profesores):

{"carreerId":9,"teachers":[{"id":990,"last_names":"RICCIO CHAVEZ","names":"FRANCISCO MARTIN", "work": "ORACLE"},{"id":943,"img":"http://oliva.ulima.edu.pe/imagenes/fotos/52127.jpg","last_names": "TORRES PAREDES","names": "CARLOS MARTIN"},{"id":997,"last_names": "ZEVALLOS LUNA VICTORIA","names": "GUILLERMO","work": "ALICORP"},{"id":979,"img":"http://oliva.ulima.edu.pe/imagenes/fotos/20798.jpg","last_names":"IREY NUÑEZ","names":"JORGE LUIS", "work": "SUNAT"}]}
De forma gráfica:


CREACIÓN DE LA TABLA
Para la creación de la tabla, procederemos a ejecutar el siguiente query:
create table carrera(
id number,
nombre varchar2(40),
profesores CLOB
CONSTRAINT constraint_json CHECK (profesores is json));


Como se mencionó en la introducción, los datos JSON se almacenan utilizando los tipos de datos SQL comunes, así como también se recomienda una restricción para validar que el documento esté en formato JSON. Para ello, almacenamos el archivo JSON en un CLOB(Character Large Object. Para cadenas de más de 4000 gigabytes) generalmente utilizado para guardar documentos, y podemos observar la restricción constraint_json aplicada a la tabla.

INSERCIÓN DE DATOS
Para insertar un registro en la tabla carrera, ejecutaremos el siguiente query y luego haremos commit, para que los cambios sean guardados satisfactoriamente:

insert into carrera(id, nombre, profesores) values (9, 'Ingenieria de sistemas',
'{"carreerId":9,"teachers":[{"id":990,"last_names":"RICCIO CHAVEZ","names":"FRANCISCO MARTIN", "work": "ORACLE"},{"id":943,"img":"http://oliva.ulima.edu.pe/imagenes/fotos/52127.jpg","last_names": "TORRES PAREDES","names": "CARLOS MARTIN"},{"id":997,"last_names": "ZEVALLOS LUNA VICTORIA","names": "GUILLERMO","work": "ALICORP"},{"id":979,"img":"http://oliva.ulima.edu.pe/imagenes/fotos/20798.jpg","last_names":"IREY NUÑEZ","names":"JORGE LUIS", "work": "SUNAT"}]}');


CONSULTAS:
  • Consulta básica: Para hacer una consulta básica, ejecutamos el siguiente query:
SELECT * FROM CARRERA;
Se está mostrando todas las columnas de la tabla.
  • Consulta utilizando la función JSON_VALUE:

Esta función selecciona un valor de los datos JSON y lo devuelve como un valor SQL. Esta función tiene dos argumentos obligatorios y acepta cláusulas opcionales de devolución(returning) y error: el primer argumento es el documento JSON almacenado en los tipos de datos VARCHAR2, BLOB o CLOB, mientras que el segundo argumento PATH(Con el símbolo $, que indica el inicio del documento) seguida de cláusulas opcionales RETURNING, ON ERROR y ON EMPTY(En caso se desee ponerlas, sino utiliza lo que se tiene por default). 

Para demostrar la funcionalidad, ejecutaremos el siguiente query para poder mostrar la utilidad de esta función:

select nombre, JSON_VALUE(profesores, '$.carreerId') as ID_CARRERA from carrera;

Así mismo, gracias a Oracle 18c se puede obtener el valor de un atributo de un objeto JSON contenido en un arreglo. En este caso, haremos un query que nos muestre los nombres y los apellidos del primer profesor encontrado en el arreglo JSON. Ejecutaremos lo siguiente:
select nombre, JSON_VALUE(profesores, '$.teachers[0].names') AS NOMBRES, JSON_VALUE(profesores, '$.teachers[0].last_names') AS APELLIDOS from carrera;
Nota: si se consulta un valor que no tiene el elemento JSON, saldrá vacío.

  • Consulta utilizando JSON_QUERY:
Se utiliza JSON_QUERY para poder obtener fragmentos de un documento JSON. Para cada documento, devuelve un valor VARCHAR2(o el valor que se le especifique con RETURNING) cuyo contenido es devuelto en un arreglo JSON. Esta función recibe dos argumentos: el primero es el documento JSON almacenado, el segundo es el PATH, seguido de las cláusulas en caso hayan. 
Para poder obtener en un arreglo JSON los nombres de los profesores, ejecutaremos lo siguiente:

SELECT json_query(profesores, '$.teachers[*].names'
                               WITH WRAPPER) AS Nombres
  FROM carrera;
Nota: si el atributo solicitado no existe en el objeto JSON, a consultar, se obtendrá un arreglo solo de los que lo contengan, como es el caso de work:

SELECT json_query(profesores, '$.teachers[*].work'

                               WITH WRAPPER) AS Nombres

  FROM carrera;

  • Consulta utilizando JSON_EXISTS:
Esta función nos permite seleccionar filas en función del contenido de los documentos JSON. Generalmente se utiliza esta función en una cláusula WHERE de una instrucción SELECT(Más adelante lo demostraremos).Esta condición verifica la existencia de un valor particular dentro de los datos JSON: devuelve verdadero si el valor está presente y falso si está ausente. Más precisamente, json_exists devuelve verdadero si los datos a los que se dirige coinciden con uno o más valores JSON. Si no coinciden los valores JSON, devuelve falso. Para poder visualizar el JSON existente, ponemos el siguiente query:

SELECT profesores from carrera 

    where   json_exists(profesores, '$.carreerId');

Si se desean hacer filtros y buscar específicamente un JSON, podemos hacer consultas personalizadas estableciendo una condición de la siguiente manera:

SELECT profesores from carrera 

    where   json_exists(profesores, '$?(@.carreerId ==  9)');

Se pueden agregar más condiciones agregando un “&&” dentro de la condición del paréntesis.

Nota: Si no existe lo solicitado en la condición, no traerá data.

  • Consulta utilizando JSON_TABLE:
 Lo que hace esta función es proyectar datos JSON específicos en columnas de varios tipos de datos. Lo usa para mapear partes de un documento JSON en las filas y columnas de una nueva tabla virtual.
Estructura de una consulta utilizando JSON_TABLE:

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";


Para demostrar la utilizar de esta función, procedemos a ejecutar el siguiente query:

SELECT jt.*
  FROM carrera,
    json_table(profesores, '$.teachers[*]'
        COLUMNS (Nombres VARCHAR2(40) PATH '$.names',
                Apellidos VARCHAR2(60) PATH '$.last_names',
                ImagenUrl VARCHAR2(60) PATH '$.img',
                Trabajo VARCHAR2(60) PATH '$.work')) AS "JT";

También se le puede agregar una cláusula WHERE, que buscaremos en base a la tabla retornada, por ello buscaremos mediante el nombre de la columna, donde buscaremos ahora por trabajo “ORACLE”:

SELECT jt.*
  FROM carrera,
    json_table(profesores, '$.teachers[*]'
        COLUMNS (Nombres VARCHAR2(40) PATH '$.names',
                Apellidos VARCHAR2(60) PATH '$.last_names',
                ImagenUrl VARCHAR2(60) PATH '$.img',
                Trabajo VARCHAR2(60) PATH '$.work')) AS "JT"
        where   jt.Trabajo = 'ORACLE';

Así mismo, podemos implementar una cláusula where en donde le podemos agregar un JSON_EXISTS, de la siguiente manera

SELECT jt.*

  FROM carrera,

    json_table(profesores, '$.teachers[*]'

        COLUMNS (Nombres VARCHAR2(40) PATH '$.names',

                Apellidos VARCHAR2(60) PATH '$.last_names',

                ImagenUrl VARCHAR2(60) PATH '$.img',

                Trabajo VARCHAR2(60) PATH '$.work')) AS "JT"

        where   json_exists(profesores, '$?(@.carreerId ==  9)');



  • Funciones PL/SQL:

Función para agregar un profesor al JSON: Se hizo la siguiente función para poder añadir a un profesor nuevo a la lista de profesores. Para lo cual pasamos como parámetro el documento JSON como CLOB, los datos obligatorios de todo profesor(id, nombres, apellidos) y los datos opcionales como lo son img, work si no se ponen, por default serán nulos. Por motivos de simplicidad, hicimos una función que solo pueda agregar, pero así mismo, 
Oracle también nos brinda la forma de poder construir una función PL/SQL para poder editar, agregar más campos o eliminar el elemento. Se creó la siguiente función:

CREATE OR REPLACE FUNCTION addTeacher(teachers IN CLOB, id IN NUMBER, names IN VARCHAR2, last_names IN VARCHAR2, img IN VARCHAR2 DEFAULT NULL, work IN VARCHAR2 DEFAULT NULL) RETURN CLOB IS

  te_elem JSON_ELEMENT_T;

  teacher_add JSON_OBJECT_T;

  te_obj JSON_ARRAY_T;

  json_obj JSON_OBJECT_T;

BEGIN

    json_obj := JSON_OBJECT_T.parse(teachers);

    te_obj := json_obj.get_Array('teachers');

    te_elem := JSON_ELEMENT_T.parse('{"id":' || id || '}');

    IF (te_elem.is_Object) THEN

        teacher_add := treat(te_elem as JSON_OBJECT_T);

        IF (img IS NOT NULL) THEN

            teacher_add.put('img', img);

        END IF;

        teacher_add.put('last_names', last_names);

        teacher_add.put('names', names);

        IF (work IS NOT NULL) THEN

            teacher_add.put('work', work);

        END IF;

    END IF;

    te_obj.append(teacher_add);

    RETURN TO_CLOB (json_obj.to_string);

END;



Para poder poner a prueba la función, ingresamos a un profesor con los siguientes datos:
  • Id: 1200
  • Nombres: RENATO
  • Apellidos: CORONADO ALVAREZ
  • Imagen referencial: http://oliva.ulima.edu.pe/imagenes/fotos/228577.jpg
  • Trabajo: ITLAB
Así mismo, se buscará el profesor por su trabajo, para asegurarnos que no  esté en el JSON:

Posteriormente, ejecutaremos el siguiente query:
UPDATE carrera SET (profesores) = addTeacher(profesores, 1200, 'RENATO', 'CORONADO ALVAREZ', 'http://oliva.ulima.edu.pe/imagenes/fotos/228577.jpg', 'ITLAB');
Para poder verificar que la inserción fue correcta, vamos a hacer un select por columnas mediante JSON_TABLE(explicado anteriormente más arriba):


CONCLUSIÓN

Finalmente, llegamos a la conclusión que Oracle 18c tiene muchas funcionalidades para poder tratar documentos JSON de manera nativa, permitiéndonos parsear, ver, modificar y eliminar data contenida dentro del mismo artículo. Así mismo, vuelve mucho más accesible la información contenida, de manera que no sea necesario enlazar una base de datos NoSQL con Oracle, sino almacenar los datos no relacionales en una misma base de datos. Las funciones que se tienen como lo son JSON_EXISTS, JSON_VALUE, JSON_TABLE nos permite simplificar de manera significativa los querys, dándonos mucho más performance.

Si se desea saber más sobre JSON in Oracle, le sugerimos los siguientes links:


NUEVOS FEATURES EN ORACLE 20C PARA JSON

  • Nuevo tipo de datos: JSON:
- Optimizado para el procesamiento de consultas y DML.  
- Puede producir mejoras de rendimiento procesando este tipo de documento.
- Se podrá utilizar tipo JSON en lugar de VARCHAR2, CLOB o LOB. 
  • Nueva función SQL: JSON_TRANSFORM.
- Se podrá utilizar esta función para actualizar partes de un documento JSON. - Se especifica qué partes modificar, las modificaciones y cualquier valor nuevo. 
- JSON_TRANSFORM facilita que una aplicación modifique un documento JSON,
sin tener que analizarlo y reconstruirlo. En la mayoría de los casos, también evita un viaje de ida y vuelta entre el servidor y el cliente para todo el documento. 
  • Mejoras en la sintaxis SQL/JSON.
- Se puede expresar queries más complejas con mucha más facilidad.
- Nueva función: JSON_SCALAR: acepta una instancia escalar de un tipo de datos SQL y devuelve un valor JSON escalar como una instancia del tipo de datos JSON.
-  Los nuevos métodos de elementos de PATH JSON admiten JSON_SCALAR: float (),double (), binary (), ymInterval () y dsInterval ().
-  El PATH y la notación con punto JSON admite nuevos métodos: avg (), count (), minNumber (), maxNumber (), minString (),maxString () y sum ().
  • Se permitirá escalar JSON en el nivel superior del documento(Soporte RFC 8259).
- Los documentos JSON en Oracle Database ahora pueden tener un valor escalar JSON de nivel superior. - Anteriormente tenían que tener un objeto JSON o un valor de matriz. - Esta característica ayuda al soporte de Oracle JSON a cumplir con RFC 8259. - Este comportamiento no está habilitado de forma predeterminada. Para habilitarlo, configure el parámetro de inicialización de la base de datos compatible con 20. El valor predeterminado para Oracle Database Release 20c es 12.2. Si el parámetro es al menos de 20, los datos JSON que se almacenan textualmente (VARCHAR2, CLOB, BLOB) o como tipo de datos JSON respetando RFC 8259: permite escalares de nivel superior en documentos. Sin embargo, para una columna JSON puede usar una restricción de verificación IS JSON con palabras clave DISALLOW SCALARS para no permitir documentos que tengan valores escalares de nivel superior.
Si desea saber más sobre los nuevos features que traerá Oracle 20c, le sugerimos entrar al siguiente link:  https://docs.oracle.com/en/database/oracle/oracle-database/20/newft/oracle-database-new-features-guide-release-20c.pdf


Comentarios

Publicar un comentario

Entradas más populares de este blog

Real-Time Refresh(Vistas Materializadas) en Oracle

Imagen
Por Renato Coronado INTRODUCCIÓN Las vistas materializadas en Oracle nos permiten mejorar el rendimiento de consultas complejas almacenando las filas producidas por la consulta en el segmento de la tabla. De esa manera, muchas consultas pueden acceder a estos resultados precalculados. Es la mejor manera de ahorrar tiempo en análisis complejos, en uniones y agregaciones usadas para mostrar la data el dashboards y reportes así como el trabajo puede ser hecho una sola vez y después ser usado por múltiples usuarios corriendo las mismas consultas , sobre todo en un ambiente de producción donde los registros de una tabla son muchísimos. Si la vista materializada es up-to-date, y es más rápido hacerlo, Oracle puede reescribir la consulta de modo que obtenga la data para la vista materializada, incluso si la consulta está referenciando la tabla original. Cualquier cambio en la tabla, marca la vista materializada como obsoleta y el registro automático no puede pasar por default. Nosotros podemo...
Leer más