Валидация JSON Schema для столбцов

Теперь, когда в вашей базе данных появился JSON-столбец, возникает соблазн хранить данные, которые сложно моделировать, в JSON-столбце. Возможно, вы не захотите отказываться от безопасности определённой схемы, которая так сильно упрощает код вашего приложения. Действительно, JSON-столбец будет проверяться на структурную валидность, на соответствие стандарту SQL. Но можно также получить безопасность схемы-определения для JSON-столбцов, выполнив проверку по правилам JSON Schema.

Использование

MySQL

ALTER TABLE products ADD CONSTRAINT CHECK(
  JSON_SCHEMA_VALID(
    '{
      "$schema": "http://json-schema.org/draft-04/schema#",
      "type": "object",
      "properties": {
        "tags": {
          "type": "array",
          "items": { "type": "string" }
        }
      },
      "additionalProperties": false
    }',
    attributes
  )
);

INSERT INTO products (..., attributes) VALUES (..., '{}');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":["test"] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[2] }');
-- ERROR: Check constraint 'products_chk_1' is violated.

PostgreSQL (требуется postgres-json-schema)

ALTER TABLE products ADD CONSTRAINT data_is_valid CHECK(
  validate_json_schema(
    '{
      "type": "object",
      "properties": {
        "tags": {
          "type": "array",
          "items": { "type": "string" }
        }
      },
      "additionalProperties": false
    }',
    attributes
  )
);

INSERT INTO products (..., attributes) VALUES (..., '{}');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":["test"] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[2] }');
-- ERROR: new row for relation "products" violates check constraint "data_is_valid"
-- DETAIL: Failing row contains ({"tags": [2]}).

Подробное объяснение

JSON-столбцы доступны во всех современных базах данных с автоматической проверкой валидности структурного JSON и сохранением в эффективном для хранения виде. Но поскольку данные, которые сложно хранить в реляционной модели, теперь сохраняются в JSON-столбцах, возникает новый набор проблем. Давно известно, что бессхемная NoSQL-база данных просто переносит схему в приложение. Схема и множество её версий должны управляться во многих местах приложения. Но мы хотим избежать повторения этой ошибки при использовании JSON-столбцов.

Проблема проверки (сложного) JSON-документа на соответствие определённой модели решается уже давно. Стандарт JSON Schema позволяет описывать свойства и типы данных, что даёт эффективное решение для валидации JSON-документа. Раньше этот процесс выполнялся в приложении, но нет причин, по которым вы не можете или не должны делать это в базе данных. Ведь если вам потребуется изменить что-либо в базе данных вручную, логика валидации вашего приложения не будет использована. Добавление валидации JSON Schema к столбцам JSON объединяет возможности NoSQL и реляционной базы данных:

• Преимущество NoSQL: можно моделировать определённые данные в столбцах JSON, что было бы сложно сделать в реляционной базе данных.
• Преимущество реляционной базы данных: обеспечивается безопасность схемы данных и не нужно обрабатывать различные версии данных в приложении.

Дополнительные ресурсы

• JSON Schema: All details about how to write JSON validation schemas.
• Youtube: David Stokes presents MySQL JSON document validation on the Percona Live conference in 2021.
• MySQL Documentation: The JSON_SCHEMA_VALID function and it's usage.
• PostgreSQL Extension: A JSON schema validation function implemented in pure PL/pgSQL which can be added to any database.