Amazon Web Services ブログ
MySQL5.7互換のAmazon AuroraでJSONを利用する
MySQL 5.7でのJSONサポートについて重要な点は?
MySQL 5.6では、数値、日付と時刻、文字列(文字とバイト)の型、および空間データ型をサポートしています。これらの型は広くサポートされていますが、これらの基本データ型は、アプリケーションを進化を作成する際の柔軟性を制限します。
MySQL 5.6を使用している場合は、アプリケーションに機能を追加する計画する際に2つの選択肢があります。最初のオプションは、アプリケーションで現在必要なすべてのフィールドを含む完全なスキーマを設定することです。その後アプリケーションで新しいフィールドが必要な場合は、スキーマを更新してその列を追加する必要があります。このアプローチにはいくつかの利点があります。新しいフィールドにインデックスを作成することができます。また、Amazon Auroraのfast DDLのような機能により、列を追加する際の影響を最小限に抑えることができます。ただし、データベース・スキーマの変更を実行し、その変更に対応するためにSQL文を更新する必要があります。
2番目のオプションは、文字列を使用して柔軟なフィールドセットをエンコードし、アプリケーションレイヤーで文字列を解析することです。柔軟性はありますが、この方法ではデータを解析するのに無駄なコストがかかります。
この様な場面ではJSONが適しており、必要とされる柔軟性を提供することで優れた方法を提供します。 JSONは、データを解析するためのコードを書く必要がないという利点も提供します。ORMまたは言語ランタイムで処理が可能です。JSONサポートはMySQL 5.7.8で導入されました。
これらの利点に加えて、JSONをネイティブ・タイプとしてMySQLで使用することで、データベースはJSONカラムに保存されているJSONドキュメントを自動的に検証できます。無効なドキュメントではエラーが発生します。ネイティブタイプのJSONでは、データベース中でJSON形式を最適化することもできます。JSONカラムに格納されたJSONドキュメントは、ドキュメント要素への高速な読み取りアクセスを可能にする内部形式に変換されます。サーバーが後でこのバイナリ形式で格納されたJSON値を読み取る必要がある場合、その値をテキスト表現から解析する必要はありません。バイナリ形式は、サーバーがサブオブジェクトまたはネストされた値をキーまたは配列のインデックスで直接参照できるように構成されています。これは、ドキュメント内の前後の値をすべて読み取らずに行います。
Amazon AuroraはMySQL 5.7との互換性をサポートしています。つまり、MySQL 5.7互換のAuroraを利用してJSONデータ型を使用したアプリケーションを開発できるようになりました。
この記事の残りの部分では、JSONデータ型とMySQL互換のAuroraを使用する電化製品のECサイトのサンプルアプリケーションをご紹介します。
スキーマの作成
電化製品は、ラップトップ、携帯電話、プリンター、テレビ、DVDなど多様なもを取り扱います。また、製品の属性もどうように多くなります。このため、さまざまな機能や属性を検索できるように、製品属性を正規化された形式で保存するのは難しいくなります。たとえば、製品比較のためにこれを行えるようにします。
まず、店舗用のデータベースを作成します。
簡単にするため、データベースにはブランド、カテゴリ、製品という3つのテーブルのみ作成します。brands
とcategories
テーブルにはJSONフィールドがありませんので、先に進むために説明は省かせて頂きます。
完成したテーブルは以下のようになります。
ここからJSONを利用するパートになります。products
テーブルはJSONカラムに異なるプロダクトの属性を持ちます。
JSONデータのインサート
私たちの最初の製品に関する情報を保存しましょう。属性をJSONオブジェクトとして直接挿入します。
MySQL WorkbenchのようなGUIツールを使用している場合は、クエリの末尾にある;を\G
で置き換えます。
INSERTクエリでJSONオブジェクトを整形 しようとすると、かなり面倒な作業になることがあります。 これを簡単にするためにJSON_OBJECT
関数を使うことができます。
JSON_OBJECT
関数はkey-valueペアのリストを使用できます。
JSON_OBJECT(key1,value1,key2,value2,key3,value3,……,key(n),value(n)
はJSONオブジェクトを返します。
JSON_OBJECT
関数を利用して2つ目の商品の情報を登録してみましょう。
次にもう一つの便利な関数をご紹介します。JSON_ARRAY
はvalueのセットを渡すとJSON配列を返します。
さらに他にも、JSON_MERGE
を使うことで複数のJSONオブジェクトをまとめて1つのJSONオブジェクトを生成することが出来ます。これは、キーと値のペアをオブジェクトとして取得する場合に便利です。JSON_MERGE
を使用して次の製品に関するデータを挿入しましょう。
JSON_OBJECT
とJSON_MERGE
を組み合わせて使用すると、作業が簡単になります。
もう少し行を足してみましょう。
ここではいくつかの行を追加したので、次にJSONオブジェクトに対してクエリを実行してみましょう。
Selecting and filtering JSON data
次の様なSELECTクエリを書くのは簡単です。
ただし、これはデータベースをクエリするのに最も効率的または有用な方法ではありません。使用する関数はJSON_EXTRACT
です。 たとえば、所有しているすべてのAndroid搭載端末を検索するとします。
ここで注意すべき3つのことがあります。 まず、JSON_EXTRACT
はJSONから比較とフィルタリングに使用できる属性を抽出できます。
次に、select句で属性を読み取るためのもう一つの短縮形が利用できます: attributes->'$.operating_system'
。
加えて、JSON_EXTRACT
の結果にはまだ引用符が含まれています。これは次の関数JSON_UNQUOTE
で解決できます。JSON_UNQUOTE
を使ってクエリを書き直し、違いを見てみましょう。
MySQLはJSONカラムのインデックスをサポートしていませんが、仮想列を使用して簡単に回避できます。
それぞれのデバイスのメモリを格納する生成列を追加しましょう。
そして、そのカラムにインデックスを追加します。
問合せが16 GBのメモリーを持つすべてのデバイスをフェッチするための実行計画を見てみましょう。実行計画からわかるように、これはJSON列から派生した仮想列に作成したインデックスを使用します。
もう1つの便利な関数は、JSONオブジェクトからキーを返すJSON_KEYS
です。 私たちがiPad用に保存する属性を確認したいとします。
Updating JSON data
JSON値を更新する方法を見てみましょう。
JSON_INSERT
関数を使って、サポートされているネットワークに関する情報を追加しましょう。
ご覧のとおり、JSONにネットワーク属性が追加されています。
JSON_INSERT
はJSONに属性を追加します。たとえば、operating_system
のような既存の属性を更新したいとします。この場合、JSON_REPLACE
関数を使用します。
現在の製品情報は以下のようになります。
JSON_INSERT
はJSONに属性を追加します。 JSON_REPLACE
は、すでに存在する属性を更新します。属性が存在するかどうかわからない場合は、JSON_SET
関数を使用します。 既に存在する場合はJSON_SET
関数が属性を更新し、それ以外の場合は属性を追加します。
更新を実行する際に便利なもう1つの関数は、JSON_ARRAY_APPEND
です。この関数は、JSONの配列属性に追加します。属性がスカラー属性の場合、JSON_ARRAY_APPEND
はそれを配列に変換します。
前の例から続けましょう。電話機でサポートされているネットワークにGSMを追加したいとします。
ネットワーク属性は配列に変換され、更新された値を持ちます。
JSONオブジェクトの削除操作
JSONオブジェクトの削除操作は、2つの側面があります。JSONから属性を削除することができます。または、JSONオブジェクトの属性に基づいて行自体を削除することもできます。
JSONから属性を削除するには、JSON_REMOVE
関数を使用します。 たとえば、電話機の仕様からネットワークに関連する情報を削除します。
JSONに含まれる情報に基づいて行を削除するには、前にWHERE
句でフィルタリングに使用したJSON_EXTRACT
関数を使用します。 たとえば、画面サイズが5未満のすべてのデバイスを削除するとします。
JSON_EXTRACT
関数を使ってDELETEを試してみましょう。
要約すると、データをJSONに挿入する関数は、JSON_OBJECT
、JSON_ARRAY
、およびJSON_MERGE
です。 データのフィルタリングに役立つ関数はJSON_EXTRACT
です。 JSONを更新するには、関数JSON_INSERT
、JSON_REPLACE
、JSON_SET
、およびJSON_ARRAY_APPEND
を使用可能です。JSON属性とオブジェクトを削除するには、JSON_REMOVE
を使用するか、WHERE
句を使用して削除します。
この機能に対する皆様のフィードバックをお待ちしております。下記のコメントを残すか、aurora-pm@amazon.comまで質問やフィードバックをお寄せください。
About the Author
Sirish Chandrasekaran is a product manager for Amazon Aurora at Amazon Web Services.
Yashada Jadhav is a database engineer for Amazon Aurora at Amazon Web Services.
翻訳は星野が担当しました (原文はこちら)