Consultando Json en columna de bases de datos relacionales. Haciendo consultas a Json en Oracle

Estamos acostumbrados a pensar en modelos relacionales para nuestros sistemas y en el camino tenemos el problema de diccionarios y estructuras que no queremos guardar en una columna hasta que pensamos que sería ideal guardar y consultar datos no relacionales y que mantengan una estructura variable como Json.

Esta pregunta en la mayoría de casos se responde en bases de datos no relacionales y a su vez en estructuras Json para explotar sus múltiples ventajas y sencillez.

Pero no existe solamente esta respuesta. También podemos lograrlo desde nuestro contexto relacional.

Hoy les traemos una implementación de persistencia y consulta de Json en columnas de Bases de Datos Relacionales de Oracle.

Esta es una serie de artículos que ejemplifican cómo utilizar Json en Bases de datos Oracle, Mysql y Postgres.

A partir de la versión Oracle Database 12.1.0.2 brinda soporte para persistencia de Json dentro de bases de datos relacionales.

Es importante aclarar que en Oracle no existe un tipo Json nativo pero estas versiones incorporaron soporte a partir de funciones nativas.

Para este artículo utilizamos como ejemplo el siguiente modelo.

Debemos implementar un sistema que permita guardar información relativa a una Persona y los registros de las Pesadas que se realiza la misma en balanzas para darle seguimiento a un plan de dieta saludable.

Teniendo esta información vamos a necesitar consultar como va el plan de dieta de cierta Persona y su progreso a partir de la información de las pesadas. Además necesitamos el peso promedio de las personas asociadas al sistema.

Model uml

Las pesadas tienen la siguiente información.

[
  {
    "id": 1,
    "balanza": "bal1",
    "peso": 80
  },
  {
    "id": 2,
    "balanza": "bal1",
    "peso": 78
  },
  {
    "id": 3,
    "balanza": "bal2",
    "peso": 77
  }
]

Primero vamos a crear la consulta para persistir este modelo.

En la creación de la tabla podemos notar que Pesadas es CLOB. Recordando que Oracle no tiene tipo de dato nativo para Json, normalmente debemos utilizar un tipo de dato que permita almacenar gran volumen de datos por eso elegimos CLOB.

CREATE TABLE PERSONA
(
  ID NUMBER NOT NULL
, NOMBRE VARCHAR2(20) NOT NULL
, APELLIDO VARCHAR2(20) NOT NULL
, USUARIO VARCHAR2(20) NOT NULL
, EMAIL VARCHAR2(50) NOT NULL
, PESADAS CLOB
, CONSTRAINT PERSONA_PK PRIMARY KEY  (ID) ENABLE
);

Luego, debemos crear una CONSTRAINT para que Oracle valide PESADAS como un Json. Esto impedirá que tengamos en el campo PESADAS json no válidos.

ALTER TABLE PERSONA ADD CONSTRAINT PC
CHECK  (PESADAS is JSON)  ENABLE;

 

Ya tenemos el modelo en Oracle.
Insertemos datos de prueba:

ALTER TABLE PERSONA ADD CONSTRAINT PC
CHECK  (PESADAS is JSON)  ENABLE;
```

>Ya tenemos el modelo en Oracle.
 Insertemos datos de prueba:

```sql
INSERT INTO PERSONA (ID,NOMBRE,APELLIDO,USUARIO,PESADAS,EMAIL) values (1,'alam','brito','alambrito'
,
'{"id": 1,"balanza": "bal1","pesoActual": 77 ,"pesadas":
[{
"fecha": "2020-01-01",
"peso": 80
}, {
"fecha": "2020-02-01",
"peso": 77
}
]}'
,'alambrito@cip.com');

Insert into PERSONA (ID,NOMBRE,APELLIDO,USUARIO,PESADAS,EMAIL) values (2,'Abran','la puerta','abranlapuerta'
,
'{"id": 1,"balanza": "bal1","pesoActual": 100,"pesadas":
[{
"fecha": "2020-01-01",
"peso": 120
}, {
"fecha": "2020-06-01",
"peso": 100}
]}'
,'abranlapu@cip.com');

Ahora vamos a consultar como va el plan de dieta de “Alam” y su progreso a partir de la información de las pesadas.

SELECT pesos FROM persona ,
JSON_TABLE(pesadas, '$.pesadas[*]'COLUMNS("pesos" NUMBER (10) PATH '$.peso')) pesos
WHERE NOMBRE = 'alam';

Resultado:

     PESOS
----------
        80
        77

En esta consulta hemos utilizado la función JSON_TABLE para consultar valores de un arreglo dentro de un Json.

Podríamos además consultar el peso promedio de las personas que acuden al sistema.

SELECT  avg(JSON_VALUE(pesadas, '$.pesoActual' RETURNING NUMBER)) as promedio FROM persona;

 

Resultado

  PROMEDIO
----------
      88.5

En la consulta que vemos, utilizamos la función JSON_VALUE para obtener un elemento del Json y luego le hacemos avg para el promedio.

SELECT pesos FROM SYSTEM.persona , 
JSON_TABLE(pesadas, '$.pesadas[*]'COLUMNS("pesos" NUMBER (10) PATH '$.peso')) pesos 
WHERE NOMBRE = 'alam'

Ventajas

1. A partir de la versión Oracle 12.1.0.2 podemos consultar Json en base de datos relacionales sin utilizar otros tipos de almacenamiento.
2. Utilizando las funciones de Oracle para Json, las consultas son rápidas y emulan comportamientos relacionales.
3. Las consultas SQL a Json son muy similares a las de otros motores como Mysql o Postgres.
4. Los campos internos de Json son indexables para ganar en performance.

Desventajas

1. Las versiones anteriores de Oracle 12.1.0.2 no cuentan con todo el potencial descrito.
2. Las funciones de Oracle para Json necesitan un estudio previo para su utilización.
3. Las funciones de Json en base de datos relacionales varían para Oracle, Mysql o Postgres.
4. Algunos framework no brindan soporte para funciones de Json, por lo que se deben ejecutar consultas nativas.

Conclusiones

En el presente artículo hemos mostrado cómo crear y consultar campos Json en una base de datos relacionales Oracle. A partir de esto tenemos la oportunidad de darle mayor valor y protagonismo a nuestras bases de datos. En próximos artículos estaremos ejemplificando cómo mejorar la performance de las consultas Json e implementar y consultar Json en bases de datos Mysql o Postgres.