Всё чаще можно заметить хранение данных в формате JSON в реляционных базах данных. Использование JSON позволяет упростить структуру таблиц, заменяя сложные отношения между ними. Однако такое упрощение может привести к неожиданным проблемам с целостностью данных.
Если мы храним данные в JSON-колонках без строгой валидации, возникает риск получить неструктурированные данные или ошибки в ожидаемом нами формате. Например, ожидаемый массив строк может неожиданно содержать числа или другие типы данных. Из-за чего мы можем столкнуться с проблемами при обработке данных в приложении.
В MySQL валидировать содержимое JSON-колонок можно прямо "из коробки", а для PostgreSQL есть специальное расширение. Ниже рассмотрим пример использования этого расширения.
Представьте, что у вас есть таблица
products с колонкой attributes, где вы храните дополнительные характеристики продукта в формате JSON.
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
attributes JSON NOT NULL DEFAULT '{}'
);
Мы ожидаем, что поле
tags внутри этого JSON будет массивом строк. Однако без строгой проверки на уровне базы данных нет гарантии, что кто-то не запишет туда числа, объекты или вообще что-то неподходящее.Чтобы сохранить гибкость JSON и одновременно обеспечить строгую структуру данных, можно использовать валидацию JSON-схемы на уровне базы данных. Для этого нужно добавить constraint, который автоматически проверит соответствие содержимого JSON определенной схеме при каждой операции вставки или обновления.
Пример для PostgreSQL:
ALTER TABLE products ADD CONSTRAINT data_is_valid CHECK(
validate_json_schema(
'{
"type": "object",
"properties": {
"tags": {
"type": "array",
"items": { "type": "string" }
}
},
"additionalProperties": false
}',
attributes
)
);
Теперь, попытка вставить данные с невалидным значение для
tags приведёт к ошибке:
INSERT INTO products (..., attributes) VALUES
(..., '{}'), -- Пустой объект, допускается
(..., '{"tags": []}'), -- Пустой массив строк
(..., '{"tags": ["test"]}'); -- Массив со строкой
-- Результат: Операция успешна
INSERT INTO products (..., attributes) VALUES
(..., '{"tags": [2]}'); -- Массив с числом вместо строки
-- Ошибка: Нарушен constraint
#DatabaseTip #SQL #JSONSchema