Aplicar constraints en columnas tipo JSON en MySQL

En los últimos años, MySQL ha mejorado considerablemente su soporte para el tipo de datos JSON, lo que permite almacenar y manipular datos estructurados dentro de una base de datos relacional. Sin embargo, al manejar columnas JSON, es importante asegurarse de que los datos sean válidos y cumplan con las reglas de negocio, para lo cual se pueden aplicar constraints (restricciones) directamente sobre los campos JSON.

En este artículo, exploraremos las distintas formas de aplicar restricciones en columnas JSON usando MySQL. ¡Vamos a ello!

1. Verificar que el valor sea un objeto o un array

Para asegurarte de que un campo JSON contenga específicamente un objeto o un array, puedes utilizar la función JSON_TYPE(), que devuelve el tipo de valor JSON (como ARRAY, OBJECT, STRING, etc.).

CONSTRAINT check_json_type CHECK (JSON_TYPE(column_name) = 'OBJECT');

Este constraint asegura que el campo column_name siempre contenga un objeto JSON.

2. Validar el tamaño de un array

Si estás trabajando con arrays dentro de un campo JSON y quieres asegurarte de que tengan un tamaño mínimo o máximo, puedes usar la función JSON_LENGTH().

CONSTRAINT check_array_size CHECK (
    JSON_TYPE(column_name) = 'ARRAY' AND 
    JSON_LENGTH(column_name) BETWEEN 2 AND 5
);

Este constraint garantiza que el array tenga entre 2 y 5 elementos.

3. Asegurarse de que todos los elementos del array sean de un tipo específico

Otra restricción importante es validar que todos los elementos dentro de un array JSON sean de un tipo específico, como strings o números. Para esto, puedes combinar JSON_EXTRACT() con JSON_TYPE().

CONSTRAINT check_array_numbers CHECK (
    JSON_TYPE(column_name) = 'ARRAY' AND
    JSON_TYPE(JSON_EXTRACT(column_name, '$[0]')) = 'NUMBER' AND
    JSON_TYPE(JSON_EXTRACT(column_name, '$[1]')) = 'NUMBER'
);

Este ejemplo asegura que los primeros dos elementos del array sean números. Puedes agregar más validaciones según el tamaño del array.

4. Validar campos específicos dentro de un objeto JSON

Si necesitas asegurarte de que ciertos campos dentro de un objeto JSON existan y sean del tipo correcto, puedes utilizar JSON_EXTRACT() y JSON_TYPE() para verificar los tipos de los valores.

CONSTRAINT check_json_fields CHECK (
    JSON_TYPE(JSON_EXTRACT(column_name, '$.name')) = 'STRING' AND
    JSON_TYPE(JSON_EXTRACT(column_name, '$.age')) = 'NUMBER'
);

Este constraint asegura que el campo name es un string y que el campo age es un número.

5. Validar la presencia de un campo específico

Si deseas asegurarte de que un campo específico exista dentro del JSON, puedes usar JSON_CONTAINS_PATH() para verificar la existencia de ese campo.

CONSTRAINT check_json_contains_email CHECK (
    JSON_CONTAINS_PATH(column_name, 'one', '$.email')
);

Este constraint valida que el campo email esté presente dentro del objeto JSON.

6. Validar la estructura completa con JSON Schema (MySQL 8.0.17+)

A partir de MySQL 8.0.17, puedes usar JSON_SCHEMA_VALID() para asegurarte de que un campo JSON cumpla con un esquema específico. Esto es útil cuando trabajas con estructuras complejas y quieres asegurarte de que todos los datos sean válidos.

CONSTRAINT check_json_schema CHECK (
    JSON_SCHEMA_VALID('{
        "type": "array",
        "items": {
            "type": "string"
        }
    }', column_name)
);

Este ejemplo valida que el campo JSON sea un array y que todos sus elementos sean strings.

7. Verificar que un valor esté en un conjunto permitido

Puedes usar JSON_UNQUOTE() para deserializar un valor JSON y asegurarte de que esté en un conjunto específico de valores permitidos.

CONSTRAINT check_json_enum CHECK (
    JSON_UNQUOTE(JSON_EXTRACT(column_name, '$.status')) IN ('active', 'inactive', 'pending')
);

Esto valida que el campo status solo pueda tomar los valores 'active', 'inactive' o 'pending'.

8. Evitar valores NULL en campos JSON

Puedes agregar restricciones para asegurarte de que un campo JSON no tenga valores NULL. Esto se hace utilizando JSON_TYPE() y asegurándote de que el valor no sea NULL.

CONSTRAINT check_price_not_null CHECK (
    JSON_TYPE(JSON_EXTRACT(column_name, '$.price')) IS NOT NULL
);

9. Validar que un número esté en un rango específico

Finalmente, si estás trabajando con números dentro de un objeto JSON, puedes validar que estos se encuentren dentro de un rango permitido.

CONSTRAINT check_age_range CHECK (
    JSON_TYPE(JSON_EXTRACT(column_name, '$.age')) = 'NUMBER' AND
    JSON_UNQUOTE(JSON_EXTRACT(column_name, '$.age')) BETWEEN 18 AND 65
);

Este constraint asegura que el campo age en el objeto JSON esté entre 18 y 65.

Conclusión

MySQL ofrece una gran flexibilidad para trabajar con datos JSON, pero también es importante mantener la integridad de los datos. Con estas restricciones, puedes asegurar que los valores almacenados en campos JSON sigan las reglas de tu modelo de negocio, desde la verificación de tipos y tamaños de arrays, hasta la validación completa de esquemas con JSON_SCHEMA_VALID().