JSON_SCHEMA_VALIDATION_REPORT

La función JSON_SCHEMA_VALIDATION_REPORT es el complemento de JSON_SCHEMA_VALID. Mientras que JSON_SCHEMA_VALID solo dice si un documento pasa o no la validación, JSON_SCHEMA_VALIDATION_REPORT devuelve un informe detallado que explica exactamente qué regla del esquema se ha violado y en qué parte del documento. Esta información es fundamental para depurar datos inválidos, generar mensajes de error claros y auditar la calidad de los datos.

Sintaxis

JSON_SCHEMA_VALIDATION_REPORT(esquema, documento)

La función recibe los mismos argumentos que JSON_SCHEMA_VALID: un esquema JSON Schema y un documento JSON a validar. Devuelve un objeto JSON con el resultado del informe.

Comportamiento básico

Cuando un documento es válido, el informe es muy simple:

SET @esquema = '{
    "type": "object",
    "required": ["nombre", "precio"],
    "properties": {
        "nombre": {"type": "string"},
        "precio": {"type": "number", "minimum": 0}
    }
}';
 
SELECT JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(@esquema, '{"nombre": "iPhone 15 Pro", "precio": 1299.99}')
) AS informe;

{
  "valid": true
}

El informe solo contiene la propiedad "valid" con valor true. No hay errores que reportar.

Cuando el documento es inválido, el informe incluye detalles del error:

SELECT JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(@esquema, '{"nombre": "Cable USB-C", "precio": -5}')
) AS informe;

{
  "valid": false,
  "reason": "The JSON document location '#/precio' failed requirement 'minimum' at JSON Schema location '#/properties/precio'",
  "schema-location": "#/properties/precio",
  "document-location": "#/precio",
  "schema-failed-keyword": "minimum"
}

El informe proporciona cinco campos clave cuando la validación falla:

La propiedad valid indica false cuando el documento no cumple el esquema. La propiedad reason es un mensaje legible en inglés que describe el error. La propiedad schema-location indica en qué parte del esquema está la regla violada. La propiedad document-location señala qué parte del documento causó el error. La propiedad schema-failed-keyword identifica la palabra clave del esquema que falló (en este caso "minimum").

Estructura del informe de error

Veamos diferentes tipos de errores y cómo se reportan:

Propiedad requerida faltante

SELECT JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(@esquema, '{"nombre": "MacBook Air M3"}')
) AS informe;

{
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
}

El schema-failed-keyword es "required" y la ubicación apunta a la raíz del documento (#), indicando que al objeto raíz le falta una propiedad obligatoria.

Tipo de dato incorrecto

SELECT JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(@esquema, '{"nombre": 42, "precio": 100}')
) AS informe;

{
  "valid": false,
  "reason": "The JSON document location '#/nombre' failed requirement 'type' at JSON Schema location '#/properties/nombre'",
  "schema-location": "#/properties/nombre",
  "document-location": "#/nombre",
  "schema-failed-keyword": "type"
}

El nombre debería ser un string pero se proporcionó un número. La ubicación del documento señala exactamente la propiedad problemática: #/nombre.

Valor fuera de rango

SET @esquema_rango = '{
    "type": "object",
    "properties": {
        "edad": {"type": "integer", "minimum": 18, "maximum": 120},
        "descuento": {"type": "number", "minimum": 0, "maximum": 100}
    }
}';
 
SELECT JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(@esquema_rango, '{"edad": 15, "descuento": 50}')
) AS informe;

{
  "valid": false,
  "reason": "The JSON document location '#/edad' failed requirement 'minimum' at JSON Schema location '#/properties/edad'",
  "schema-location": "#/properties/edad",
  "document-location": "#/edad",
  "schema-failed-keyword": "minimum"
}

Es importante notar que MySQL reporta solo el primer error encontrado. Si el documento tiene múltiples errores, solo verás el primero en el informe.

Caso práctico: mensajes de error para usuarios

Puedes usar el informe de validación para generar mensajes de error comprensibles en tu aplicación:

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200),
    especificaciones JSON
);
 
SET @esquema_producto = '{
    "type": "object",
    "required": ["color", "peso"],
    "properties": {
        "color": {"type": "string", "minLength": 1},
        "peso": {"type": "number", "minimum": 0.001},
        "garantia_meses": {"type": "integer", "minimum": 0, "maximum": 120},
        "dimensiones": {
            "type": "object",
            "properties": {
                "alto": {"type": "number", "minimum": 0},
                "ancho": {"type": "number", "minimum": 0},
                "profundidad": {"type": "number", "minimum": 0}
            }
        }
    }
}';

SELECT
    doc AS documento_enviado,
    JSON_SCHEMA_VALIDATION_REPORT(@esquema_producto, doc)->>'$.valid' AS valido,
    JSON_SCHEMA_VALIDATION_REPORT(@esquema_producto, doc)->>'$.schema-failed-keyword' AS regla_violada,
    JSON_SCHEMA_VALIDATION_REPORT(@esquema_producto, doc)->>'$.document-location' AS campo_problematico
FROM (
    SELECT '{"color": "Rojo", "peso": 0.5, "garantia_meses": 24}' AS doc
    UNION ALL SELECT '{"peso": 0.3}'
    UNION ALL SELECT '{"color": "Azul", "peso": -1}'
    UNION ALL SELECT '{"color": "", "peso": 0.1}'
    UNION ALL SELECT '{"color": "Negro", "peso": 0.2, "garantia_meses": 200}'
) datos;

documento_enviado	valido	regla_violada	campo_problematico
{"color": "Rojo", "peso": 0.5, "garantia_meses": 24}	true	NULL	NULL
{"peso": 0.3}	false	required	#
{"color": "Azul", "peso": -1}	false	minimum	#/peso
{"color": "", "peso": 0.1}	false	minLength	#/color
{"color": "Negro", "peso": 0.2, "garantia_meses": 200}	false	maximum	#/garantia_meses

Cada fila muestra la regla específica que se violó y el campo exacto del documento. Tu aplicación puede traducir estos códigos a mensajes en español: required en # significa "faltan campos obligatorios", minimum en #/peso significa "el peso debe ser positivo", etc.

Caso práctico: auditoría de datos existentes

Cuando migras datos o importas información de fuentes externas, JSON_SCHEMA_VALIDATION_REPORT te ayuda a identificar y categorizar los problemas:

CREATE TABLE importacion_proveedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    proveedor VARCHAR(100),
    datos_producto JSON
);
 
INSERT INTO importacion_proveedores (proveedor, datos_producto) VALUES
('Proveedor A', '{"sku": "A001", "nombre": "Monitor 27 4K", "precio": 449.99, "stock": 25}'),
('Proveedor A', '{"sku": "A002", "nombre": "Teclado", "precio": 0, "stock": 80}'),
('Proveedor B', '{"sku": "B001", "nombre": "", "precio": 79.99, "stock": 120}'),
('Proveedor B', '{"sku": "B002", "precio": 129.99, "stock": -5}'),
('Proveedor C', '{"sku": "C001", "nombre": "Ratón", "precio": 59.99, "stock": 200}');

SET @esquema_importacion = '{
    "type": "object",
    "required": ["sku", "nombre", "precio", "stock"],
    "properties": {
        "sku": {"type": "string", "minLength": 1},
        "nombre": {"type": "string", "minLength": 1},
        "precio": {"type": "number", "minimum": 0.01},
        "stock": {"type": "integer", "minimum": 0}
    }
}';
 
SELECT
    id,
    proveedor,
    datos_producto->>'$.sku' AS sku,
    CASE
        WHEN JSON_SCHEMA_VALID(@esquema_importacion, datos_producto) = 1 THEN 'OK'
        ELSE CONCAT(
            'Error en ',
            COALESCE(
                JSON_SCHEMA_VALIDATION_REPORT(@esquema_importacion, datos_producto)->>'$.document-location',
                'documento'
            ),
            ' (',
            COALESCE(
                JSON_SCHEMA_VALIDATION_REPORT(@esquema_importacion, datos_producto)->>'$.schema-failed-keyword',
                'desconocido'
            ),
            ')'
        )
    END AS estado_validacion
FROM importacion_proveedores;

id	proveedor	sku	estado_validacion
1	Proveedor A	A001	OK
2	Proveedor A	A002	Error en #/precio (minimum)
3	Proveedor B	B001	Error en #/nombre (minLength)
4	Proveedor B	B002	Error en # (required)
5	Proveedor C	C001	OK

Este informe te permite saber exactamente qué registros tienen problemas y cuál es la naturaleza de cada error, facilitando la comunicación con los proveedores para que corrijan sus datos.

Caso práctico: resumen de errores por tipo

Para tablas grandes, puedes agrupar los errores por tipo para obtener una vista general de la calidad de los datos:

SELECT
    JSON_SCHEMA_VALIDATION_REPORT(@esquema_importacion, datos_producto)->>'$.schema-failed-keyword' AS tipo_error,
    COUNT(*) AS cantidad
FROM importacion_proveedores
WHERE JSON_SCHEMA_VALID(@esquema_importacion, datos_producto) = 0
GROUP BY tipo_error;

tipo_error	cantidad
minimum	1
minLength	1
required	1

Este resumen indica que hay un registro con valor por debajo del mínimo, uno con texto demasiado corto y uno al que le faltan campos obligatorios. En una tabla con miles de registros, este tipo de análisis te permite priorizar las correcciones.

Caso práctico: validación con objetos anidados

El informe señala correctamente la ubicación dentro de estructuras anidadas:

SET @esquema_pedido = '{
    "type": "object",
    "required": ["cliente", "direccion"],
    "properties": {
        "cliente": {"type": "string"},
        "direccion": {
            "type": "object",
            "required": ["calle", "ciudad", "cp"],
            "properties": {
                "calle": {"type": "string", "minLength": 1},
                "ciudad": {"type": "string"},
                "cp": {"type": "string", "minLength": 5, "maxLength": 5}
            }
        }
    }
}';
 
SELECT JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(@esquema_pedido, '{
        "cliente": "María García",
        "direccion": {"calle": "Gran Vía 28", "ciudad": "Madrid", "cp": "123"}
    }')
) AS informe;

{
  "valid": false,
  "reason": "The JSON document location '#/direccion/cp' failed requirement 'minLength' at JSON Schema location '#/properties/direccion/properties/cp'",
  "schema-location": "#/properties/direccion/properties/cp",
  "document-location": "#/direccion/cp",
  "schema-failed-keyword": "minLength"
}

La ubicación #/direccion/cp señala exactamente que el campo cp dentro del objeto direccion tiene un valor demasiado corto (3 caracteres cuando el mínimo es 5). La ruta del esquema también es precisa, apuntando a la definición de cp dentro de direccion.

Manejo de NULL

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

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

esquema_null	documento_null
NULL	NULL

Si el documento es válido contra un esquema vacío, el informe siempre es positivo:

SELECT JSON_SCHEMA_VALIDATION_REPORT('{}', '{"cualquier": "cosa"}') AS informe;

informe
{"valid": true}

Combinación con otras funciones

Puedes extraer campos específicos del informe para crear reportes más estructurados:

SELECT
    id,
    proveedor,
    CASE
        WHEN r->>'$.valid' = 'true' THEN 'Valido'
        ELSE 'Invalido'
    END AS estado,
    r->>'$.schema-failed-keyword' AS keyword,
    REPLACE(r->>'$.document-location', '#/', '') AS campo
FROM (
    SELECT
        id,
        proveedor,
        JSON_SCHEMA_VALIDATION_REPORT(@esquema_importacion, datos_producto) AS r
    FROM importacion_proveedores
) validacion
WHERE r->>'$.valid' = 'false';

id	proveedor	estado	keyword	campo
2	Proveedor A	Invalido	minimum	precio
3	Proveedor B	Invalido	minLength	nombre
4	Proveedor B	Invalido	required

La columna campo se obtiene eliminando el prefijo #/ de la ubicación del documento, produciendo nombres de campo legibles. Para el error required, el campo queda vacío porque la ubicación apunta a la raíz del documento.

Es importante recordar que JSON_SCHEMA_VALIDATION_REPORT reporta solo el primer error encontrado. Si un documento tiene múltiples problemas, necesitarás corregir el primer error y volver a validar para descubrir los siguientes. Esto es por diseño: el informe identifica un problema a la vez para que puedas resolverlo de forma iterativa.

Con esto completamos la sección de funciones JSON de MySQL. En la siguiente sección veremos cómo importar y exportar datos.

Escrito por Eduardo Lázaro