🔧 Целостность данных при использовании JSON-колонок в базе данных

Всё чаще можно заметить хранение данных в формате 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