Cómo trabajar con JSON en MySQL

MySQL 8 finalmente ha llegado a la escena de los gestores de bases de datos y lo hace en plan grande; liberando entre sus nuevas características las siguientes:

1. Soporte a Window Functions
2. Soporte nativo a JSON Data Type
3. Mejoramiento en velocidad
4. Inclusión por defecto del juego de caracteres utf8mb4

¿Por qué JSON en bases de datos relacionales?

Dentro de la industria desde hace años, llegaron a irrumpir las bases de datos de tipo documento tales como MongoDB, las cuales mostraron que al no estar amarradas al esquema tradicional de SQL, podían ofrecer una velocidad de escritura y lectura aún muy superior a lo manejado por las bases de datos relacionales; sin embargo esa realidad se ha vuelto a modificar gracias a los últimos esfuerzos de MySQL gracias a su implementación nativa para guardar, modificar y eliminar datos en formato JSON (JavaScript Object Notation)

Las bases de datos NoSQL (Not only SQL), se basan en un esquema flexible de datos, en los cuales no necesitas declarar o crear primero dicho esquema para comenzar a almacenar información como también no es estrictamente necesario el proceso de normalización.

A través del siguiente ejemplo te guiaré en como implementar el almacenamiento de datos en formato JSON dentro de una base de datos relacional.

Recomendado: Curso bases de datos desde cero.

Al momento de hacer la declaración de tu tabla, sigues la mayoría de los pasos regulares

Versiones de MySQL que soportan el dato tipo JSON

• MySQL 5.7
• MySQL 8

Declaración dentro de una tabla de una columna tipo JSON

CREATE TABLE movies(
	 id BIGINT PRIMARY KEY AUTO_INCREMENT,
     titulo VARCHAR(100) UNIQUE NOT NULL,
     etiquetas JSON NOT NULL
)ENGINE=InnoDB;

Insertar contenido en formato JSON

INSERT INTO movies(titulo, etiquetas) 
VALUES('the world', '{"acerca" : {"genero": "acción", "cool": true}}');

+----+-----------+--------------------------------------------------+
| id | titulo    | etiquetas                                        |
+----+-----------+--------------------------------------------------+
|  2 | the world | {"acerca": {"genero": "accion", "cool": true}}   |
+----+-----------+--------------------------------------------------+

Select con JSON\_EXTRACT

Para poder acceder a determinados valores de nuestro JSON la función de extract nos lo facilita accediendo en el siguiente orden

SELECT titulo, JSON_EXTRACT(etiquetas, '$.acerca.genero') AS Genero FROM movies;

+-------------+-----------+
| titulo      | Genero    |
+-------------+-----------+
| the world   | "accion"  |
+-------------+-----------+

El operador corto ->brinda la misma funcionalidad que JSON\_EXTRACT

SELECT titulo, etiquetas->'$.acerca.genero' AS Genero FROM movies;

Actualización de registros tipo JSON

Si bien ya tenemos datos guardados, para poder modificarlos; hacemos uso del método JSON\_REPLACE el cual requiere 3 argumentos

1. El nombre de la columna

2. La clave exacta a donde se va a hacer la modificación en la estructura JSON

3. El valor que se va a introducir para hacer la modificación

UPDATE movies SET etiquetas = JSON_REPLACE(etiquetas, '$.acerca.genero', 'romance') 
WHERE titulo = 'the world';

+----+-----------+---------------------------------------------------+
| id | titulo    | etiquetas                                         |
+----+-----------+---------------------------------------------------+
|  2 | the world | {"acerca": {"genero": "romance", "genial": true}} |
+----+-----------+---------------------------------------------------+

Eliminación de un registro tipo JSON

Si ahora por ejemplo queremos eliminar un elemento, usando WHERE para comparar contra un registro tipo JSON debería ser del modo siguiente a través del método JSON\_EXTRACT

Teniendo en cuenta que usamos JSON\_EXTRACT para extraer precisamente el valor que usaremos para comparar y luego eliminar

DELETE FROM movies WHERE id = 1 AND JSON_EXTRACT(etiquetas, '$.acerca.genero') = "drama";

Eliminación de un valor dentro de una estructura JSON

Ahora en vez de eliminar un registro completo, solo deseamos eliminar el valor true de la clave genial de mi JSON; del modo siguiente

UPDATE movies SET etiquetas = JSON_REMOVE(etiquetas, '$.acerca.genero') WHERE id = 1;

Como puedes observar hoy más que nunca gracias a las últimas implementaciones en MySQL podemos tener datos almacenados en formato de documentos tal cual ocurre en las bases de datos no relacionales, pero con todos los beneficios que ya usamos y conocemos. Lo mejor del mundo SQL se une con el NoSQL. Si deseas conocer mas acerca de los comandos de las funciones JSON, te recomiendo el siguiente enlace con la documentación oficial: JSON functions

Recomendado: Curso de bases de datos documentales con MongoDB