🔧 Целостность данных при использовании 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

🗑 Удаление дублирующихся строк

Со временем в любом приложении могут появиться дублирующиеся записи. Иногда их удаление реализуют на уровне приложения, хотя это можно сделать с помощью одного SQL-запроса.

Удалить дубликаты вручную не так просто, как может показаться. Обычно для этого используется команда GROUP BY с функциями MIN(id) или MAX(id), чтобы оставить одну строку.

⚠️ Будьте крайне аккуратны при выполнении DML-запросов. Во избежании потери данных рекоммендуем сделать backup таблицы, которую планируете изменить.

DELETE FROM contacts
WHERE id NOT IN (
   SELECT MIN(id)
   FROM contacts
   GROUP BY firstname, lastname, email
);

Однако такой метод не всегда подходит, если есть дополнительные требования:
* Нужно сохранить не все дубликаты, а лишь часть, например, последние пять.
* Иногда важно не просто удалить первые или последние строки — дополнительные поля могут задавать приоритет, например, мы не хотим удалять пользователя, подтвердившего email, ради того, который этого не сделал.

Чтобы учесть все требования, можно воспользоваться оконными функциями:
1. Строки разбиваются на группы по дублирующимся полям, создавая отдельные группы для каждого уникального набора значений.
2. Каждая группа сортируется по важным полям, например, по дате создания.
3. Для каждой строки в группе с помощью функции ROW_NUMBER назначается порядковый номер.
4. Можно удалить любые строки с номером, превышающим необходимый, например, если нужно оставить только пять последних записей.

Тогда, запрос позволяющий сохранить не все дубликаты, а лишь часть, например, последние пять, будет выглядеть следующим образом:

WITH duplicates AS (
   SELECT id, ROW_NUMBER() OVER(
       PARTITION BY firstname, lastname, email
       ORDER BY age DESC
       ) AS rownum
   FROM contacts
)
DELETE FROM contacts
USING duplicates
WHERE contacts.id = duplicates.id AND duplicates.rownum > 5;

А запрос, для удаления дубликатов с учетом факта подтверждения email следующим образом:

WITH duplicates AS (
   SELECT
       id,
       ROW_NUMBER() OVER(
           PARTITION BY email
           ORDER BY email_confirmed DESC
       ) AS rownum
   FROM contacts
)
DELETE FROM contacts
USING duplicates
WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

#DatabaseTip #SQL