📝

MySQLのJSON型　詳解

2023/09/13に公開

MySQL

JSON

tech

JSON型の特徴

JSON型はMySQLでJSONドキュメントを保存することができる型です。単にJSON形式の文字列を文字列型に格納するのと比べて以下の利点があります。

JSONカラムに格納するJSONドキュメントが自動で検証され、不正な場合にはエラーとなる
JSONカラムに格納されたJSONドキュメントは、ドキュメント要素へ素早くアクセスできるように最適化される。 MySQLはJSONドキュメント全体を解析することなく、キーまたは配列インデックスによりネストされた値を直接参照できる。

JSONとして有効な値

MySQLにおいてJSON型は厳密にJSON規格で検証され、無効な値が入力された場合はエラーになります。
以下にJSONとして有効な値を列挙します。配列、オブジェクトはネストさせることができます。

リテラル
JSON配列およびオブジェクトには、文字列または数値,NULL,true,falseといったリテラルを含むことができます。
配列
JSONの配列は、カンマで区切られ、[]で囲まれた値のリストが含まれます:

["abc", 10, null, true, false]

JSONオブジェクト
JSONのオブジェクトには、カンマで区切られ、{} で囲まれたキーと値のペアのセットが含まれます。キーは文字列でなければなりません。:

{"k1": "value", "k2": 10}

JSONオブジェクトの作成と検証

まず、MySQLデータベース内でJSONオブジェクトを作成および検証するための構文について説明します。JSONカラムの定義は以下のようにできます。

CREATE TABLE json_test (
  json JSON NULL
);

JSONデータは前出のJSON規格に厳密に従っていなければなりません。
例えば、次のように無効なJSONオブジェクトをjson_testテーブルに挿入しようとした場合：

INSERT INTO json_test (json) VALUES ('[1,2');

このデータを挿入しようとすると、配列の括弧が閉じられていないため下記のエラーが発生します。

> ERROR 3140 (22032) at line 5: Invalid JSON text: "Missing a comma or ']' after an array element." at position 4 in value for column 'json_test.json'.

有効なJSONオブジェクトを挿入した場合、問題なく処理できます：

INSERT INTO json_test (json) VALUES ('{"name": "Taro"}');

SELECT json FROM json_test;
> {"name": "Taro"}

JSONカラムからデータを取得する方法

MySQLデータベース内に有効なJSONデータがある場合、さまざまな関数と演算子を使用してデータを取得および操作できます。

このJSONオブジェクトから特定のキーに対応する値だけを取得したい場合、次のように->>演算子を使用できます：

SELECT `json`->>"$.name" as name FROM json_test;

| name |
|------|
| Taro |

->>演算子は、特定のパスにあるJSONオブジェクトを抽出するために使用されます。

JSONカラムのインデックス化

MySQLでJSONカラムを使用する際の重要な考慮事項の1つは、JSONカラムを直接インデックス化できないことです。代わりに、JSONオブジェクト内の特定のキーに対してインデックスを作成できます。

たとえば、次のコードを考えてみましょう：

ALTER TABLE json_test ADD INDEX my_index ((`json`->>"$.name"));

ここでは、カラム内のJSONオブジェクトのnameフィールドにインデックスを作成しています。これにより、この特定のJSONパスに関連するクエリの高速な検索をMySQLが実行できるようになります。

JSONカラムを使用する際のベストプラクティス

MySQLのJSONカラムは非常に強力ですが、従来のスキーマ設計の代替手段として使用すべきではありません。

スキーマがよく定義されていない場合やスキーマが頻繁に変更される場合にはJSONカラムを使用することが有用です。
たとえば、JSONカラムは、異なる応答形式を持つサードパーティサービスやAPIからのペイロードを保存する場合に適しています。また、リレーショナルデータベース設計に収まらないネストされたデータや階層データを保存する場合にも役立ちます。

JSONカラムを使用する際の注意点として、JSONデータの大量保存時に処理が重くなる可能性があることが挙げられます。MySQLの任意のデータ型と同様に、クエリを実行する際に必要なデータのみを選択し、不必要なJSONデータを取得しないようにしましょう。

まとめ

MySQLスキーマ内でJSONオブジェクトを作成および検証する方法を説明し、データを取得および操作するためのさまざまな関数と演算子の使用方法を解説しました。