Existen momentos en esta vida en que pasarle un número finito de parámetros a un stored procedure simplemente no alcanza. Un escenario cada vez más común es la necesidad de definir una función del lado del servidor de base de datos que pueda recibir un objeto del cual desconocemos inicialmente su longitud y debemos recorrerlo para extraer la información y hacer algo con ella.

El único prerrequisito es que debemos contar con una versión del motor de MySQL igual o mayor a 5.7.8 o bien una versión de MariaDB 10.2 o superior.

Empecemos por definir un hipotético stored procedure para ilustrar su uso:

DROP PROCEDURE IF EXISTS `sp_jsonTest`;
DELIMITER $$
CREATE PROCEDURE `sp_jsonTest` (
	IN pParametroJson    JSON
)
BEGIN 
    DECLARE vJsonEsValido INT;
    DECLARE vItems INT;
    DECLARE vIndex BIGINT UNSIGNED DEFAULT 0;
    
    # Variables para parseo del objeto JSON
    DECLARE vCampo1 VARCHAR(100);
    DECLARE vCampo2 VARCHAR(100);
    DECLARE vCampo3 DECIMAL(15, 2);
    
    SET vJsonEsValido = JSON_VALID(pParametroJson);
    
    IF vJsonEsValido = 0 THEN 
        # El objeto JSON no es válido, salimos prematuramente
        SELECT "JSON suministrado no es válido";
    ELSE 
        # Nuestro objeto es válido, podemos proceder
        SET vItems = JSON_LENGTH(pParametroJson);
        
        # El objeto es válido y contiene al menos un elemento
        IF vItems > 0 THEN 
            # Creamos una tabla temporal donde guardaremos momentáneamente
            # el contenido del objeto JSON para facilitar su uso
            DROP TEMPORARY TABLE IF EXISTS `tmp_jsonTest`;
            CREATE TEMPORARY TABLE `tmp_jsonTest` (
                `index`        INT(11) NOT NULL,
                `campo_1`	   VARCHAR(100) NOT NULL,
                `campo_2`      VARCHAR(100) NULL,
                `campo_3`      DECIMAL(15, 2) DEFAULT 0
            );
            
            WHILE vIndex < vItems DO
                SET vCampo1 = JSON_UNQUOTE(JSON_EXTRACT(pParametroJson, CONCAT('$[', vIndex, '].campo_1')));
                SET vCampo2 = JSON_UNQUOTE(JSON_EXTRACT(pParametroJson, CONCAT('$[', vIndex, '].campo_2')));
                SET vCampo3 = JSON_EXTRACT(pParametroJson, CONCAT('$[', vIndex, '].campo_3'));
                
                INSERT INTO `tmp_jsonTest` 
                	(`index`, `campo_1`, `campo_2`, `campo_3`)
                VALUES
                	(vIndex, vCampo1, vCampo2, vCampo3);
                    
                SET vIndex = vIndex + 1;
            END WHILE;
            
            # Aca podemos ya trabajar con el contenido de nuestro JSON
            # interactuando directamente sobre la tabla temporal utilizando
            # instrucciones SQL tradicionales
            
            # Antes de finalizar es conveniente deshacernos de la
            # tabla temporal para no dejar basura regada en la base de datos
            DROP TEMPORARY TABLE IF EXISTS `tmp_jsonTest`;
        END IF;
    END IF;
END $$
DELIMITER ;

Si bien parece un tanto complicado todo lo que está haciendo este stored procedure, en realidad es bastante trivial una vez se practica un poco con él. En primer lugar nos aseguramos que el objeto JSON suministrado sea válido y que contenga al menos un elemento y acto seguido lo insertamos a una tabla temporal que no es más que una conversión a formato de tabla SQL de la misma estructura del objeto JSON. Hagamos algunas pruebas para entender mejor lo que está sucediendo. Únicamente voy a comentar la línea siguiente al final del stored procedure para evitar que la tabla temporal sea eliminada y poder examinar el contenido que se extrajo del objeto JSON:

DROP TEMPORARY TABLE IF EXISTS `tmp_jsonTest`;

Para invocar este stored procedure utilizamos precedemos el nombre del mismo con la palabra reservada CALL de la siguiente manera:

CALL sp_jsonTest('');

Si intentamos ejecutarlo de esta manera, veremos como nuestro stored procedure no intenta hacer nada pues hará una validación de integridad del parámetro de tipo JSON que le hemos suministrado:

Hagamos un pequeño objeto JSON que nos permita evaluar el resto de nuestro stored procedure:

[{
    "campo_1": "Item 1, Campo 1",
    "campo_2": "Item 1, Campo 2",
    "campo_3": 100.00
}, {
    "campo_1": "Item 2, Campo 1",
    "campo_2": "Item 2, Campo 2",
    "campo_3": 333.33
}, {
	"campo_1": "Item 3, Campo 1",
    "campo_2": "Item 3, Campo 2",
    "campo_3": 123456.00
}]

Y la manera de invocar nuestro stored procedure utilizando este objeto es suministrándolo como un string:

Ahora bien, puesto que provisionalmente he comentado la línea que elimina mi tabla temporal luego de la ejecución del stored procedure puedo hacer un SELECT a dicha tabla para examinar el contenido luego de la ejecución:

Acá básicamente hemos construido un stored procedure que nos sirve de traducción entre una estructura JSON y una tabla estándar de SQL. Esto es bastante útil para traer grandes cantidades de información desde un entorno donde un objeto JSON es fácil de construir y dejar que un stored procedure haga todo el trabajo del lado de la base de datos en vez de hacer cientos de invocaciones a un endpoint desde un aplicativo web. En aspectos de rendimiento no hay comparación, siempre el motor de base de datos va a ganar en rapidez e integridad una vez tiene todo el dataset y puede realizar su trabajo dentro de un entorno transaccional.

Finalmente, este stored procedure no hace más que rascar la superficie. Únicamente nos hemos enfocado en el paso de traducción de un objeto JSON a una tabla temporal de SQL. La implementación de esta técnica ya dependerá del caso puntual que sea necesario según la circunstancia.