JSON_SCHEMA_VALID

La función JSON_SCHEMA_VALID verifica si un documento JSON cumple con las reglas definidas en un esquema JSON Schema. JSON Schema es un estándar que permite describir la estructura esperada de un documento JSON: qué propiedades debe tener, qué tipos de datos son válidos, qué rangos de valores se aceptan y qué campos son obligatorios. MySQL 8.0.17 incorporó esta función para que puedas aplicar validación de datos directamente en la base de datos.

Sintaxis

JSON_SCHEMA_VALID(esquema, documento)

La función recibe dos argumentos: el primero es un string JSON que contiene el esquema (según el estándar JSON Schema Draft 4), y el segundo es el documento JSON a validar. Devuelve 1 si el documento cumple con el esquema, o 0 si no lo cumple.

Ambos argumentos deben ser JSON válido. Si cualquiera es NULL, la función devuelve NULL.

Comportamiento básico

Empecemos con un esquema simple que valida un objeto de producto:

SET @esquema = '{
    "type": "object",
    "properties": {
        "nombre": {"type": "string"},
        "precio": {"type": "number"},
        "stock": {"type": "integer"}
    },
    "required": ["nombre", "precio"]
}';

Este esquema dice que el documento debe ser un objeto con propiedades nombre (string), precio (número) y stock (entero), donde nombre y precio son obligatorios.

SELECT
    JSON_SCHEMA_VALID(@esquema, '{"nombre": "iPhone 15 Pro", "precio": 1299.99, "stock": 45}') AS completo,
    JSON_SCHEMA_VALID(@esquema, '{"nombre": "MacBook Air M3", "precio": 1399.00}') AS sin_stock,
    JSON_SCHEMA_VALID(@esquema, '{"nombre": "Cable USB-C"}') AS sin_precio,
    JSON_SCHEMA_VALID(@esquema, '{"nombre": "Funda", "precio": "gratis"}') AS precio_invalido;

completo	sin_stock	sin_precio	precio_invalido
1	1	0	0

El primer documento pasa porque tiene todas las propiedades con tipos correctos. El segundo pasa porque stock no es requerido. El tercero falla porque le falta precio (que es obligatorio). El cuarto falla porque precio es una cadena en lugar de un número.

Elementos del esquema JSON

MySQL soporta los siguientes elementos del estándar JSON Schema Draft 4:

La propiedad type define el tipo esperado. Los valores posibles son "string", "number", "integer", "boolean", "array", "object" y "null".

La propiedad required es un array con los nombres de las propiedades obligatorias:

SET @esquema_usuario = '{
    "type": "object",
    "required": ["email", "nombre"],
    "properties": {
        "email": {"type": "string"},
        "nombre": {"type": "string"},
        "edad": {"type": "integer", "minimum": 0, "maximum": 150},
        "activo": {"type": "boolean"}
    }
}';
 
SELECT
    JSON_SCHEMA_VALID(@esquema_usuario, '{"email": "maria@email.com", "nombre": "María", "edad": 28}') AS valido,
    JSON_SCHEMA_VALID(@esquema_usuario, '{"nombre": "Carlos"}') AS falta_email,
    JSON_SCHEMA_VALID(@esquema_usuario, '{"email": "ana@email.com", "nombre": "Ana", "edad": -5}') AS edad_negativa;

valido	falta_email	edad_negativa
1	0	0

Las propiedades minimum y maximum definen rangos válidos para números. La edad -5 falla porque el mínimo es 0.

Para cadenas, puedes usar minLength, maxLength y pattern (expresiones regulares):

SET @esquema_email = '{
    "type": "object",
    "properties": {
        "email": {
            "type": "string",
            "pattern": "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\\\.[a-zA-Z]{2,}$"
        },
        "telefono": {
            "type": "string",
            "minLength": 9,
            "maxLength": 15
        }
    }
}';

La propiedad enum restringe un valor a una lista fija:

SET @esquema_pedido = '{
    "type": "object",
    "properties": {
        "estado": {
            "type": "string",
            "enum": ["pendiente", "procesando", "enviado", "entregado", "cancelado"]
        },
        "prioridad": {
            "type": "string",
            "enum": ["baja", "normal", "alta", "urgente"]
        }
    }
}';
 
SELECT
    JSON_SCHEMA_VALID(@esquema_pedido, '{"estado": "enviado", "prioridad": "alta"}') AS valido,
    JSON_SCHEMA_VALID(@esquema_pedido, '{"estado": "devuelto", "prioridad": "alta"}') AS estado_invalido;

valido	estado_invalido
1	0

El estado "devuelto" no está en la lista de valores permitidos.

Caso práctico: restricción CHECK con JSON Schema

Una de las aplicaciones más potentes de JSON_SCHEMA_VALID es usarla en restricciones CHECK para que MySQL rechace automáticamente documentos inválidos al insertarlos:

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    atributos JSON,
    CHECK (JSON_SCHEMA_VALID('{
        "type": "object",
        "required": ["color"],
        "properties": {
            "color": {"type": "string"},
            "peso": {"type": "number", "minimum": 0},
            "dimensiones": {
                "type": "object",
                "properties": {
                    "alto": {"type": "number"},
                    "ancho": {"type": "number"},
                    "profundidad": {"type": "number"}
                }
            }
        }
    }', atributos))
);

Ahora MySQL valida cada inserción contra el esquema:

-- Esta inserción funciona correctamente
INSERT INTO productos (nombre, atributos) VALUES
('iPhone 15 Pro', '{"color": "Titanio Natural", "peso": 0.187}');
 
-- Esta inserción falla: falta la propiedad "color" requerida
INSERT INTO productos (nombre, atributos) VALUES
('Cable USB-C', '{"longitud": "1m"}');
-- Error: Check constraint 'productos_chk_1' is violated.
 
-- Esta inserción falla: peso es negativo
INSERT INTO productos (nombre, atributos) VALUES
('MacBook Air', '{"color": "Plata", "peso": -1.5}');
-- Error: Check constraint 'productos_chk_1' is violated.

La restricción CHECK garantiza que ningún documento inválido entre en la tabla, proporcionando la misma seguridad de tipos que las columnas relacionales pero con la flexibilidad del esquema JSON.

Caso práctico: validar configuraciones de aplicación

Las aplicaciones que almacenan configuraciones en JSON pueden validar que los valores sean correctos antes de aplicarlos:

SET @esquema_config = '{
    "type": "object",
    "required": ["base_datos", "cache"],
    "properties": {
        "base_datos": {
            "type": "object",
            "required": ["host", "puerto"],
            "properties": {
                "host": {"type": "string", "minLength": 1},
                "puerto": {"type": "integer", "minimum": 1, "maximum": 65535},
                "pool_size": {"type": "integer", "minimum": 1, "maximum": 100},
                "ssl": {"type": "boolean"}
            }
        },
        "cache": {
            "type": "object",
            "required": ["driver"],
            "properties": {
                "driver": {"type": "string", "enum": ["redis", "memcached", "file"]},
                "ttl": {"type": "integer", "minimum": 0}
            }
        }
    }
}';

SELECT JSON_SCHEMA_VALID(@esquema_config, '{
    "base_datos": {"host": "db.empresa.com", "puerto": 3306, "ssl": true},
    "cache": {"driver": "redis", "ttl": 3600}
}') AS config_valida;

config_valida
1

SELECT JSON_SCHEMA_VALID(@esquema_config, '{
    "base_datos": {"host": "db.empresa.com", "puerto": 99999},
    "cache": {"driver": "mongodb"}
}') AS config_invalida;

config_invalida
0

La segunda configuración falla porque el puerto excede 65535 y el driver "mongodb" no está en la lista permitida.

Caso práctico: validación en lote

Puedes usar JSON_SCHEMA_VALID para verificar la integridad de datos existentes en una tabla:

CREATE TABLE respuestas_api (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(200),
    payload JSON,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO respuestas_api (endpoint, payload) VALUES
('/webhook/pago', '{"evento": "pago_completado", "monto": 1299.99, "moneda": "EUR", "referencia": "PAY-001"}'),
('/webhook/pago', '{"evento": "pago_fallido", "monto": -50, "moneda": "EUR"}'),
('/webhook/pago', '{"evento": "reembolso", "monto": 49.99, "moneda": "INVALID"}'),
('/webhook/pago', '{"evento": "pago_completado", "monto": 899.99, "moneda": "USD", "referencia": "PAY-002"}');

SET @esquema_pago = '{
    "type": "object",
    "required": ["evento", "monto", "moneda"],
    "properties": {
        "evento": {"type": "string", "enum": ["pago_completado", "pago_fallido", "reembolso"]},
        "monto": {"type": "number", "minimum": 0},
        "moneda": {"type": "string", "enum": ["EUR", "USD", "GBP"]},
        "referencia": {"type": "string"}
    }
}';
 
SELECT
    id,
    endpoint,
    payload->>'$.evento' AS evento,
    JSON_SCHEMA_VALID(@esquema_pago, payload) AS es_valido
FROM respuestas_api;

id	endpoint	evento	es_valido
1	/webhook/pago	pago_completado	1
2	/webhook/pago	pago_fallido	0
3	/webhook/pago	reembolso	0
4	/webhook/pago	pago_completado	1

Los registros 2 y 3 no pasan la validación: el monto negativo y la moneda "INVALID" no cumplen el esquema. Para encontrar solo los registros inválidos:

SELECT id, payload
FROM respuestas_api
WHERE JSON_SCHEMA_VALID(@esquema_pago, payload) = 0;

id	payload
2	{"evento": "pago_fallido", "monto": -50, "moneda": "EUR"}
3	{"evento": "reembolso", "monto": 49.99, "moneda": "INVALID"}

Manejo de NULL

Si cualquiera de los argumentos es NULL, la función devuelve NULL:

SELECT
    JSON_SCHEMA_VALID(NULL, '{"clave": "valor"}') AS esquema_null,
    JSON_SCHEMA_VALID('{"type": "object"}', NULL) AS documento_null;

esquema_null	documento_null
NULL	NULL

Un esquema vacío ('{}') acepta cualquier documento JSON válido:

SELECT
    JSON_SCHEMA_VALID('{}', '{"cualquier": "cosa"}') AS objeto,
    JSON_SCHEMA_VALID('{}', '[1, 2, 3]') AS array,
    JSON_SCHEMA_VALID('{}', '42') AS numero;

objeto	array	numero
1	1	1

Combinación con otras funciones

Puedes combinar JSON_SCHEMA_VALID con procedimientos almacenados para crear funciones de validación reutilizables:

SET @esquema_producto = '{
    "type": "object",
    "required": ["nombre", "precio"],
    "properties": {
        "nombre": {"type": "string", "minLength": 1},
        "precio": {"type": "number", "minimum": 0.01}
    }
}';
 
SELECT
    JSON_SCHEMA_VALID(@esquema_producto, doc) AS valido,
    doc
FROM (
    SELECT '{"nombre": "iPhone", "precio": 1299.99}' AS doc
    UNION ALL
    SELECT '{"nombre": "", "precio": 100}'
    UNION ALL
    SELECT '{"precio": 50}'
) datos;

valido	doc
1	{"nombre": "iPhone", "precio": 1299.99}
0	{"nombre": "", "precio": 100}
0	{"precio": 50}

El segundo documento falla porque el nombre tiene longitud 0 (y minLength es 1). El tercero falla porque le falta la propiedad nombre requerida.

Para saber exactamente por qué un documento falla la validación, MySQL ofrece la función JSON_SCHEMA_VALIDATION_REPORT, que veremos en el siguiente artículo.

En el siguiente artículo veremos JSON_SCHEMA_VALIDATION_REPORT para obtener detalles de errores de validación.

Escrito por Eduardo Lázaro