「MySQL道普請便り」ではこれまでにもJSONに関連する記事を多数扱ってきましたが、MySQL 8.0で強化されたJSON関連機能には、まだ紹介しきれていないものが多く存在します。

JSON型のデータを扱う上での悩みの1つは、JSON形式であれば、内容にかかわらず更新処理が成功してしまう点です。通常はアプリケーション側でバリデーションを行うことが多いと思いますが、実はSQLだけで構造をチェックすることも可能です。

今回は、MySQLでJSONデータを扱う際に、特定のスキーマ形式のデータだけを受け入れたい場合に便利なJSON_SCHEMA_VALID関数を紹介します。

サンプルデータの準備

今回は実際にJSON_SCHEMA_VALID()関数を検証するために、以下のようなテーブルを準備します。

mysql> CREATE TABLE json_test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payload JSON
);

idはautoincrementなプライマリキー、payloadには正常または不正なJSONデータが入る想定です。このテーブルに、以下のように正常なJSONデータと不正なJSONデータを挿入します。正常な形式はname（文字列型⁠）⁠、age（数値型）を持つスキーマとします。

-- 正常なJSONデータ
INSERT INTO json_test (payload) VALUES ('{ "name": "Alice", "age": 25 }'),('{ "name": "Bob", "age": 30 }');

-- age が文字列（型違い）
INSERT INTO json_test (payload) VALUES
('{ "name": "Charlie", "age": "twenty" }');

-- name が存在しない
INSERT INTO json_test (payload) VALUES
('{ "age": 40 }');

-- age が NULL
INSERT INTO json_test (payload) VALUES
('{ "name": "Eve", "age": null }');

-- フィールドがない
INSERT INTO json_test (payload) VALUES
('{}');

今回は、上記のようなJSONデータを扱ってJSON_SCHEMA_VALID()関数を検証していきます。

JSON_SCHEMA_VALID関数

JSON_SCHEMA_VALID関数は、JSONデータが指定されたスキーマに適合しているかどうかを検証するための関数です。
通常、JSON型のカラムには、正しいJSON形式であれば、構造に関係なくどんなデータでも格納・更新できます。
しかし、JSON_SCHEMA_VALID関数を使うことで、そのようなJSONデータが、あらかじめ定義したスキーマに準拠しているかどうかをSQLレベルで検証できます。

それでは、具体的な使用例を見てみましょう。

mysql> SELECT id, JSON_SCHEMA_VALID('{
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "age": { "type": "integer", "minimum": 0 }
  },
  "required": ["name", "age"]
}', payload) as valid FROM json_test;
+----+-------+
| id | valid |
+----+-------+
|  1 |     1 |
|  2 |     1 |
|  3 |     0 |
|  4 |     0 |
|  5 |     0 |
|  6 |     0 |
+----+-------+
6 rows in set (0.00 sec)

この結果から、JSON_SCHEMA_VALID関数を使うことで、{"name": "文字列", "age": 数値}というスキーマに適合しないデータ（id = 3以降）についてはvalid列が0（false）となり、スキーマに準拠していないことが明確に判別できることがわかります。

JSON_SCHEMA_VALID関数の構文と動作

JSON_SCHEMA_VALID 関数の構文は以下のとおりです。

JSON_SCHEMA_VALID(json_schema, json_document)

• json_schema：JSON Schema（Draft 4形式）を文字列として指定します。
• json_document：検証対象のJSONオブジェクトを指定します。

返り値は以下のようになります。

• スキーマに適合している場合：1
• 適合していない場合：0
• いずれかの引数が NULL の場合：NULL

なお、引数のどちらかが不正なJSON形式である場合は、関数の実行時にエラーが発生します。MySQL 8.4.5現在、JSON_SCHEMA_VALID関数はJSON Schema Draft 4に準拠しています。

また、この関数はrequired属性にも対応しており、特定のプロパティの存在を必須とするルールをスキーマに含めることで、より厳密な検証が可能です。

JSON_SCHEMA_VALID関数で変数を使ったスキーマ定義を行う

JSONスキーマの定義は、変数に格納して使用することも可能です。たとえば、以下のように@schema変数にスキーマを定義し、それを JSON_SCHEMA_VALID関数に渡すことができます。

mysql> SET @schema = '{
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "age": { "type": "integer", "minimum": 0 }
  },
  "required": ["name", "age"]
}';

mysql> SELECT id, JSON_SCHEMA_VALID(@schema, payload) as valid FROM json_test;
+----+-------+
| id | valid |
+----+-------+
|  1 |     1 |
|  2 |     1 |
|  3 |     0 |
|  4 |     0 |
|  5 |     0 |
|  6 |     0 |
+----+-------+
6 rows in set (0.00 sec)

JSON_VALID関数との違い

似た関数として、第211回「MySQLで文字列で入ってしまったJSONを扱いたい」 で紹介したJSON_VALID関数があります。この関数は、対象のデータがJSONとして構文上正しいかどうかを検証するものです。

一方、JSON_SCHEMA_VALID関数は、JSONの構造や内容があらかじめ定義されたスキーマと一致しているかどうかを検証します。両者は名称が似ているため混同しやすいですが、それぞれの目的と検証対象が異なることに注意が必要です。

JSON_SCHEMA_VALID関数はCHECK制約で使える？

JSON_SCHEMA_VALID関数はCHECK制約と組み合わせることで、スキーマに適合しないJSONデータの登録を防ぐことが可能です。ただし、CHECK制約ではセッション変数（たとえば@schemaなど）を参照することができません。
そのため、スキーマの定義はSQL文の中に直接記述する必要があります。

このように、JSON_SCHEMA_VALID 関数を CHECK 制約と組み合わせることで、特定のスキーマに合致したJSONデータのみを許可するテーブル制約を実現できます。

mysql> CREATE TABLE json_checked (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   data JSON,
    ->   CHECK (JSON_SCHEMA_VALID(@schema, data) = 1)
    -> );
ERROR 3816 (HY000): An expression of a check constraint 'json_checked_chk_1' cannot refer to a user or system variable.

mysql> CREATE TABLE json_checked (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   data JSON,
    ->   CHECK (JSON_SCHEMA_VALID('{
    '>   "type": "object",
    '>   "properties": {
    '>     "name": { "type": "string" },
    '>     "age": { "type": "integer", "minimum": 0 }
    '>   },
    '>   "required": ["name", "age"]
    '> }', data) = 1)
    -> );
Query OK, 0 rows affected (0.03 sec)

まとめ

今回は、特定のスキーマ形式に従ったJSONデータを扱う方法として、JSON_SCHEMA_VALID 関数を紹介しました。JSON_SCHEMA_VALID関数を使えば、MySQLに格納されたJSONデータが、あらかじめ定義された構造に従っているかどうかを、SQLだけで検証できます。

また、JSON_VALID関数と組み合わせることで、構文と構造の両面での検証も可能になります。たとえば、開発時や監査用途において、「⁠構文的には正しいが、型や必須項目が不一致かもしれない」といったデータを検出する場面で活用できます。

より詳細な仕様や使用例については、公式ドキュメントのMySQL :: MySQL 8.0 リファレンスマニュアル :: 12.18.7 JSON スキーマ検証関数を参照してください。