91 подписчик

Как валидировать структуру JSON во входных параметрах MySQL хранимой процедуры

8 апреля8 апр

19 мин

MySQL начиная с версии 5.7 включает поддержку JSON как нативного типа данных и набор функций для работы с JSON-документами. Это позволяет хранить и обрабатывать JSON напрямую в базе. Однако, гибкость JSON (динамическая структура, нестрогая схема) означает, что необходимо явно проверять структуру входящего JSON, особенно если он передается в хранимую процедуру. Валидация позволяет убедиться, что обязательные поля присутствуют и имеют правильное имя (учитывая регистр букв) и тип значения. Начиная с MySQL 8.0 функциональность JSON была существенно расширена (например, добавлены проверки по JSON Schema), но и в MySQL 5.7+ доступен ряд встроенных функций для ручной проверки структуры. Ниже рассмотрены подходы к нативной валидации JSON в хранимках для версий MySQL 5.7, 8.0 и выше. MySQL предоставляет несколько функций для проверки и разбора JSON-документов без использования внешнего кода: Кроме того, начиная с MySQL 8.0 появились расширения: Ниже мы рассмотрим, как практически применить эт

Оглавление

Поддержка JSON в MySQL и необходимость валидации
Базовые функции для проверки JSON
Проверка наличия обязательных полей и регистра ключей

Поддержка JSON в MySQL и необходимость валидации

Базовые функции для проверки JSON

MySQL предоставляет несколько функций для проверки и разбора JSON-документов без использования внешнего кода:

JSON_VALID(json_doc) – проверяет, является ли переданная строка корректным JSON. Возвращает 1, если JSON синтаксически валиден, и 0 в противном случае. Например, JSON_VALID('{"name": "John", "age": 30}') вернет 1 (валидный JSON), а отсутствие кавычек или скобок даст 0. Используя эту функцию в хранимой процедуре, можно сразу отклонить явно невалидный JSON.
JSON_CONTAINS_PATH(json_doc, 'one'/'all', path1, ...) – проверяет наличие указанного пути (поля) в JSON-документе. Параметр 'one' указывает, что хотя бы один из перечисленных путей должен существовать, 'all' – что все пути должны существовать. Функция возвращает 1, если условие выполнено (путь найден), или 0 если нет. Например, JSON_CONTAINS_PATH(doc, 'one', '$.name') вернет 1, если в JSON doc присутствует ключ name в корне, и 0, если такого поля нет. Эта функция удобна для проверки существования обязательных полей.
JSON_EXTRACT(json_doc, path) – извлекает значение по указанному JSON-пути. В SQL-запросах для краткости можно использовать оператор -> (и ->> для извлечения с преобразованием в строку). В контексте валидации хранимки JSON_EXTRACT позволяет получить значение поля и проверить его на NULL или передать в другие функции.
JSON_TYPE(json_val_or_expr) – возвращает строку, указывающую тип JSON-значения (например, OBJECT, ARRAY, STRING, INTEGER и т.д.). Если передать результат JSON_EXTRACT, можно определить тип конкретного поля. Например, для JSON @j = '{"a": [10, true]}' функция вернет следующие значения: JSON_TYPE(@j) → "OBJECT" (потому что корневой элемент – объект), JSON_TYPE(JSON_EXTRACT(@j, '$.a')) → "ARRAY" (поле a – массив), JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) → "INTEGER" (первый элемент массива число), JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) → "BOOLEAN". Таким образом, JSON_TYPE позволяет проверить, что значение поля имеет ожидаемый тип. (Важно: функция вернет NULL, если путь не найден или значение JSON NULL, поэтому обычно ее вызывают после проверки наличия поля.)
JSON_LENGTH(json_doc, [path]) – возвращает длину JSON-документа или элемента по пути. Для массива это количество элементов, для объекта – число ключей, для скаляра – 1. Например, JSON_LENGTH('{"a": 1, "b": {"c": 30}}') даст 2 (две пары ключ-значение на верхнем уровне), а JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') вернет 1 (в элементе b один ключ c). Эта функция полезна для определения количества элементов в массиве при итерации.
JSON_KEYS(json_doc, [path]) – возвращает JSON-массив ключей объекта на верхнем уровне или по заданному пути. Например, JSON_KEYS('{"x":1,"y":2}') вернет ["x", "y"]. Это может пригодиться для проверки имен полей (например, на наличие нежелательных/неизвестных ключей), хотя для проверки обязательных полей обычно проще использовать JSON_CONTAINS_PATH.

Кроме того, начиная с MySQL 8.0 появились расширения:

JSON_SCHEMA_VALID(schema, document) и JSON_SCHEMA_VALIDATION_REPORT(schema, document) – функции для проверки JSON-документа на соответствие заданной JSON Schema (соответствует стандарту JSON Schema draft-04). Если документ соответствует схеме, JSON_SCHEMA_VALID вернет 1; если нет – 0. Валидация по схеме позволяет одним вызовом проверить и наличие необходимых полей, и их типы, и даже диапазоны значений. Подробнее об использовании этих функций – ниже.

Ниже мы рассмотрим, как практически применить эти функции внутри хранимой процедуры для проверки структуры входного JSON.

Проверка наличия обязательных полей и регистра ключей

Первый шаг валидации – убедиться, что в JSON присутствуют все необходимые поля. Предположим, ожидается, что во входном JSON-параметре обязательно должен быть ключ "name". В хранимой процедуре можно сделать проверку с помощью JSON_CONTAINS_PATH или прямого извлечения:

IF JSON_CONTAINS_PATH(v_input_json, 'one', '$.name') = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Ошибка: отсутствует обязательное поле \"name\"';
END IF;

В этом фрагменте v_input_json – переменная (или параметр процедуры), содержащая JSON-документ. JSON_CONTAINS_PATH(... 'one', '$.name') = 0 означает "нет пути $.name в JSON", и если это так, мы генерируем ошибку с помощью SIGNAL. Используется код состояния SQLSTATE '45000', который обозначает необработанное пользовательское исключение, и устанавливается понятный текст ошибки через MESSAGE_TEXT.

Важно отметить, что поиск по JSON-пути чувствителен к регистру символов в именах ключей. То есть $.name и $.Name – это разные пути. JSON по стандарту различает регистр в именах полей, и MySQL соответственно будет считать, что поле "name" отсутствует, если в документе вместо него присутствует "Name". Таким образом, приведенная проверка одновременно гарантирует и наличие поля, и то, что оно написано в правильном регистре. Если, например, входной JSON содержит "Name": "Alice", то JSON_CONTAINS_PATH(... '$.name') вернет 0, и процедура бросит ошибку – effectively сообщив, что требуемого поля "name" нет (потому что "Name" не то же самое).

Примечание: При необходимости можно дополнительно проверять и случаи неверного регистра явно. Например, если известно, что некоторые клиенты могут прислать "Name" вместо "name", можно добавить проверку IF JSON_CONTAINS_PATH(v_input_json, 'one', '$.Name') = 1 THEN ... SIGNAL ... ' Поле \"Name\" должно быть в нижнем регистре (\"name\")'; END IF;. Однако во многих случаях достаточно просто требовать правильный ключ, как показано выше, и считать некорректный регистр отсутствием поля.

Для проверки нескольких обязательных полей можно либо вызывать JSON_CONTAINS_PATH с режимом 'all' и перечислением всех путей, либо делать последовательные проверки. Например:

-- Проверяем наличие сразу двух полей: $.name и $.age
IF JSON_CONTAINS_PATH(v_input_json, 'all', '$.name', '$.age') = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Отсутствует одно из обязательных полей: name или age';
END IF;

Выражение с 'all' вернет 1 только если оба пути существуют. Альтернативно, можно разбить на два IF и выдавать более конкретные сообщения для каждого поля отдельно.

Проверка типа данных полей

Следующий фокус – убедиться, что значение каждого ключа имеет ожидаемый тип (строка, число и т.д.). После того как наличие поля подтверждено, можно использовать функцию JSON_TYPE для получения типа.

Например, требуем, чтобы поле "name" было строкой (JSON string):

SET @name_type = JSON_TYPE(JSON_EXTRACT(v_input_json, '$.name'));
IF @name_type <> 'STRING' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Поле \"name\" имеет неверный тип (ожидается STRING)';
END IF;

Здесь JSON_EXTRACT(v_input_json, '$.name') извлекает значение поля, а JSON_TYPE(...) возвращает тип этого значения в виде строки. Возможные возвращаемые значения включают, в частности: 'OBJECT' для объектов, 'ARRAY' для массивов, 'STRING' для текстовых значений, 'INTEGER' для целых чисел, 'DOUBLE' для чисел с плавающей запятой, 'BOOLEAN' для true/false, 'NULL' для JSON-литерала null. Если поле "name" содержит, скажем, число вместо строки, JSON_TYPE вернет 'INTEGER' или 'DOUBLE' в зависимости от формата числа, и условие сработает, вызывая ошибку.

Аналогично, пусть поле "age" должно быть целым числом. Можно проверить так:

SET @age_type = JSON_TYPE(JSON_EXTRACT(v_input_json, '$.age'));
IF @age_type <> 'INTEGER' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Поле \"age\" имеет неверный тип (ожидается INTEGER)';
END IF;

Важно понимать, что в MySQL тип числа в JSON определяется автоматически при разборе: если значение выглядит как целое (без десятичной точки и экспоненты) и помещается в диапазон INT, то JSON_TYPE вернет 'INTEGER'. Если же число нецелое (например 5.2) или очень большое, может вернуться 'DOUBLE' или 'DECIMAL'. Если ваша бизнес-логика требует именно целое число, имеет смысл проверять именно на 'INTEGER' и трактовать 'DOUBLE' как ошибку. В иных случаях (когда достаточно, что поле числовое) можно проверять более мягко, например JSON_TYPE(...$.'field') IN ('INTEGER','DOUBLE','DECIMAL'). В JSON Schema (начиная с MySQL 8.0) есть отдельные типы "integer" vs "number", но при ручной проверке через JSON_TYPE это нужно учитывать.

Для булевых значений ожидается 'BOOLEAN', для JSON-объектов – 'OBJECT', для массивов – 'ARRAY' и т.д. Полный список типов, возвращаемых JSON_TYPE, приведен в документации. В хранимке после определения типа можно при несоответствии сгенерировать SIGNAL с сообщением об ошибке типа данных.

Отметим, что если обязательное поле отсутствует, вызов JSON_TYPE(JSON_EXTRACT(...)) вернет NULL (поскольку JSON_EXTRACT не найдет значение). Чтобы не спутать отсутствие поля с несоответствием типа, логику лучше строить так:

Сначала IF JSON_CONTAINS_PATH = 0 для отсутствующих полей → ошибка "поле отсутствует".
Затем IF JSON_TYPE(...) <> 'ожидаемый_тип' → ошибка "неверный тип".

Это гарантирует, что JSON_TYPE вызывается только когда поле точно есть (не NULL).

Валидация массива объектов внутри JSON

Нередко JSON-параметр может содержать массив объектов. Например, поле "children" может быть массивом, каждый элемент которого – объект с определенной структурой. Хранимая процедура может валидировать и такие вложенные структуры.

Допустим, ожидается, что в JSON есть поле "children" и оно должно быть массивом объектов, у каждого элемента которого, например, есть строковое поле "name" и числовое поле "age". Общий подход будет следующим:

Проверить, что поле "children" существует.
Проверить, что оно является массивом (JSON_TYPE = 'ARRAY').
Пройтись по элементам массива и для каждого убедиться, что это объект, и проверить его внутренние поля.

Шаги 1 и 2 выполняются аналогично предыдущему:

IF JSON_CONTAINS_PATH(v_input_json, 'one', '$.children') = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Отсутствует обязательное поле \"children\"';
END IF;
IF JSON_TYPE(JSON_EXTRACT(v_input_json, '$.children')) <> 'ARRAY' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Поле \"children\" должно быть массивом';
END IF;

Далее получим число элементов в массиве:

SET @child_count = JSON_LENGTH(v_input_json, '$.children');

Функция JSON_LENGTH вернет количество элементов массива children. После этого можно организовать цикл (Loop) для итерации по индексам массива от 0 до @child_count - 1 (в MySQL индексация массива в JSON начинается с 0). В псевдокоде на SQL это будет примерно так:

SET @i = 0;
children_loop: LOOP
IF @i >= @child_count THEN
LEAVE children_loop; -- выход из цикла
END IF;

-- Проверяем, что children[i] является объектом
IF JSON_TYPE(JSON_EXTRACT(v_input_json, CONCAT('$.children[', @i, ']'))) <> 'OBJECT' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('Элемент children[', @i, '] должен быть объектом');
END IF;

-- Пример: проверяем поле "name" внутри каждого объекта children[i]
IF JSON_CONTAINS_PATH(v_input_json, 'one', CONCAT('$.children[', @i, '].name')) = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('Объект children[', @i, '] не содержит поле \"name\"');
ELSEIF JSON_TYPE(JSON_EXTRACT(v_input_json, CONCAT('$.children[', @i, '].name'))) <> 'STRING' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('Поле name в children[', @i, '] имеет неверный тип');
END IF;

-- (Аналогично можно проверить другие ожидаемые поля внутри объекта)

SET @i = @i + 1;
END LOOP;

В этом фрагменте мы динамически формируем JSON-путь вида $.children[i].name для каждого индекса @i с помощью CONCAT (объединяя строковые части пути). Функция JSON_EXTRACT с таким путем извлекает нужное значение. Мы проверяем, что каждый элемент массива – объект, и что внутри него есть ключ "name" нужного типа. При любом несоответствии генерируется SIGNAL с сообщением, указывающим индекс проблемного элемента.

Такой подход можно расширить под конкретные требования к структуре объектов внутри массива (например, проверять наличие и тип других полей, допустимые диапазоны значений и т.д.). Если структура вложенных объектов сложная, вложенные проверки могут существенно увеличить код хранимой процедуры. Тем не менее, для разумного числа полей это выполнимо нативными средствами.

Стоит отметить, что в MySQL 8.0 появился удобный SQL-оператор JSON_TABLE для преобразования JSON-массива в табличный вид. Он позволяет напрямую развернуть массив JSON в строки результата, каждая со столбцами, соответствующими полям объекта. В контексте валидации JSON_TABLE можно использовать, например, в сочетании с WHERE, чтобы найти элементы, не удовлетворяющие условиям. Однако внутри процедур применение JSON_TABLE ограничено – нужно либо селектить результаты во временную таблицу, либо использовать курсор для обработки строк. В простых проверках обычно проще использовать цикл, как показано выше. Тем не менее, знание о JSON_TABLE может пригодиться: он позволяет объявить для каждого столбца тип, и при попытке извлечения несоответствующего типа будет возвращена ошибка или NULL в зависимости от указанных опций (ON ERROR). Это тоже способ выявить некорректные типы внутри массива, хотя и опосредованный.

Генерация ошибок в хранимой процедуре (SIGNAL)

В примерах выше для прерывания исполнения хранимой процедуры при обнаружении ошибки структуры мы использовали конструкцию SIGNAL. Команда SIGNAL позволяет выбросить пользовательское исключение с заданным кодом и сообщением. Код SQLSTATE '45000' зарезервирован для пользовательских ошибок и обозначает "необработанное пользовательское исключение". В общем случае можно выбрать другой код в зависимости от ситуации (например, определенные классы ошибок), но '45000' удобен как универсальный.

Синтаксис включает опциональный блок SET, где можно задать информационные поля об ошибке: MESSAGE_TEXT (текст сообщения), MYSQL_ERRNO (номер ошибки MySQL, можно указать произвольный в диапазоне пользовательских, например 1000+), и др. Минимально достаточно установить MESSAGE_TEXT. Пример из документации MySQL показывает использование SIGNAL внутри IF-условий в процедуре:

IF pval = 1 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred';
END IF;

В наших проверках мы сразу пишем SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'; внутри условий – это завершит выполнение процедуры с ошибкой. На стороне приложения такое исключение можно перехватить и выдать понятное сообщение.

Обратите внимание: как только выполняется SIGNAL ..., процедура немедленно прекращается с указанной ошибкой (если это ошибка, а не предупреждение SQLSTATE, начинающееся на '01'). Поэтому нет необходимости после SIGNAL делать LEAVE или что-то еще – код просто не пойдет дальше. Если же хочется не прерывать, а лишь зарегистрировать предупреждение, можно использовать SQLSTATE '01000' (warning) – тогда выполнение продолжится, но вызов SHOW WARNINGS отобразит предупреждение. Однако для целей валидации структуры обычно именно ошибка нужна, чтобы откатить действия и уведомить о проблеме.

Использование JSON Schema (MySQL 8.0+) для валидации

MySQL 8.0.17 добавил поддержку проверки JSON по схеме (JSON Schema) на уровне СУБД. Это означает, что вместо ручных проверок каждого поля можно определить JSON-схему, описывающую требуемую структуру, и проверить документ одной функцией. Например, зададим JSON Schema, требующую, чтобы объект имел поле "name" (строка) и "age" (число) и больше никаких обязательных полей:

SET @schema = '{
"type": "object",
"properties": {
"name": { "type": "string" },
"age": { "type": "number" },
"children": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": { "type": "string" },
"age": { "type": "number" }
},
"required": ["name", "age"]
}
}
},
"required": ["name", "age"]
}';

В этой схеме мы указали типы для name, age и описали, что children должен быть массивом объектов с обязательными name и age. Ключевое слово "required" определяет список обязательных полей как на верхнем уровне, так и внутри объектов массива. MySQL поддерживает атрибут "required" в JSON Schema, обеспечивая наличие указанных свойств.

Проверим JSON-документ @doc против этой схемы:

SET @doc = '{"name": "Alice", "age": 30, "children": [{"name": "Bob", "age": 5}]}';
SELECT JSON_SCHEMA_VALID(@schema, @doc);

Если документ удовлетворяет схеме, результат будет 1 (true). Если чего-то не хватает или тип не соответствует, функция вернет 0. Например, если в @doc убрать поле "name" или написать "Name" вместо него, проверка вернет 0, так как документ не соответствует схеме (не выполнено требование "required": ["name", ...]). В документации показан пример: схема требовала поля latitude/longitude, и при проверке документа без этих полей JSON_SCHEMA_VALID вернула 0.

Для получения подробностей несоответствия можно вызвать JSON_SCHEMA_VALIDATION_REPORT(@schema, @doc), которая вернет JSON-отчет о проверке (укажет, какие именно свойства отсутствуют или не прошли валидацию).

Преимущество подхода с JSON Schema: минимизация кода в хранимой процедуре. Можно, например, заранее сохранить JSON-схему в таблице или переменной и внутри процедуры делать:

IF JSON_SCHEMA_VALID(v_schema, v_input_json) = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'JSON не соответствует требуемой схеме';
END IF;

После чего при необходимости получить детали – либо парсить JSON_SCHEMA_VALIDATION_REPORT, либо логировать ее. Также JSON Schema позволяет задавать не только наличие и тип, но и диапазоны чисел, формат строк, вложенные требования и т.д., чего вручную было бы проверять громоздко.

Однако, у JSON Schema валидатора есть свои ограничения: не поддерживаются внешние ссылки $ref (выдают ошибку ER_NOT_SUPPORTED_YET), и сложные сценарии могут влиять на производительность. Тем не менее, для многих задач (валидировать формат входящего JSON API-запроса, например) – это мощный инструмент.

Различия между MySQL 5.7, 8.0 и новее в контексте JSON-валидации

MySQL 5.7 (начиная с 5.7.8) предоставил базовый функционал JSON: тип данных JSON и функции JSON_EXTRACT, JSON_TYPE, JSON_VALID, JSON_CONTAINS_PATH, JSON_CONTAINS, JSON_ARRAY, JSON_OBJECT и др. Средствами MySQL 5.7 можно выполнить все основные проверки, описанные выше, но приходится делать их "вручную" в коде хранимой процедуры. Также в MySQL 5.7 отсутствовали проверяемые ограничениями CHECK (они игнорировались), поэтому нельзя было навесить ограничения на столбец JSON для проверки структуры – оставалась только проверка в приложении или процедурах.

MySQL 8.0 существенно расширила работу с JSON:

Появился оператор JSON_TABLE для преобразования JSON в табличный формат, что упрощает запросы к JSON и потенциально валидацию/трансформацию данных.
Введены функции JSON_SCHEMA_VALID() и JSON_SCHEMA_VALIDATION_REPORT() для проверки соответствия JSON-документа JSON Schema. Это фактически встроенный валидатор схем, чего не было в 5.7.
Добавлены новые функции модификации JSON (JSON_SET, JSON_MERGE_PATCH и др.), агрегирования (JSON_ARRAYAGG, JSON_OBJECTAGG) – они не напрямую про валидацию, но расширяют нативную работу с JSON.
Начиная с MySQL 8.0.16 были включены в действие ограничения CHECK. Это означает, что можно задать правило на уровне столбца, например: CHECK (JSON_SCHEMA_VALID('schema', json_column)) – и СУБД будет сама отвергать вставку/обновление некорректного JSON. В 5.7 такой constraint написаться мог, но не применялся.
Производительность операций с JSON в 8.0 улучшена по сравнению с 5.7, и появился полнотекстовый индекс для JSON, но это уже детали реализации.

Новые версии (8.0.x, 8.1): По состоянию на 2025 год, актуальная линейка MySQL 8.x предоставляет все необходимое для нативной JSON-валидации. Существенных изменений в JSON-функциях в версиях 8.0.17+ не было, кроме постепенных оптимизаций. MySQL продолжает поддержку стандарта JSON Schema draft-04 без обновления до более новых версий схемы. Можно ожидать, что будущие релизы могут расширить поддержку JSON (например, обновить поддержку схемы до draft-07/2019-09 или добавить новые функции), но на данный момент основное различие – наличие JSON Schema валидатора и вспомогательных функций в 8.x против их отсутствия в 5.7.