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など多様なもを取り扱います。また、製品の属性もどうように多くなります。このため、さまざまな機能や属性を検索できるように、製品属性を正規化された形式で保存するのは難しいくなります。たとえば、製品比較のためにこれを行えるようにします。

まず、店舗用のデータベースを作成します。

CREATE DATABASE online_store;
USE online_store

簡単にするため、データベースにはブランド、カテゴリ、製品という3つのテーブルのみ作成します。brandsとcategoriesテーブルにはJSONフィールドがありませんので、先に進むために説明は省かせて頂きます。

CREATE TABLE brands (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO brands (name) VALUES ('Apple');
INSERT INTO brands (name) VALUES ('Samsung');
INSERT INTO brands (name) VALUES ('Lenovo');
INSERT INTO brands (name) VALUES ('LG');
INSERT INTO brands (name) VALUES ('ASUS');

INSERT INTO categories (name) VALUES ('Phones');
INSERT INTO categories (name) VALUES ('Desktop');
INSERT INTO categories (name) VALUES ('Laptop');
INSERT INTO categories (name) VALUES ('Tablets');

完成したテーブルは以下のようになります。

mysql> SELECT * FROM categories;
+----+---------+
| id | name    |
+----+---------+
|  1 | Phones  |
|  2 | Desktop |
|  3 | Laptop  |
|  4 | Tablets |
+----+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM brands;
+----+---------+
| id | name    |
+----+---------+
|  1 | Apple   |
|  2 | Samsung |
|  3 | Lenovo  |
|  4 | LG      |
|  5 | ASUS    |
+----+---------+
5 rows in set (0.00 sec)

ここからJSONを利用するパートになります。productsテーブルはJSONカラムに異なるプロダクトの属性を持ちます。

CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
brand_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
attributes JSON NOT NULL);

JSONデータのインサート

私たちの最初の製品に関する情報を保存しましょう。属性をJSONオブジェクトとして直接挿入します。

INSERT INTO products 
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Samsung 3 Chromebook',2,3,
'{
"dimensions":{"w":11.4,"d":8,"h":0.7},
"weight":2.54,
"color":"black",
"CPU":"Intel Celeron N3060 / 1.6 GHz",
"processor_count":2,
"operating_system":"chrome",
"memory":4,
"Storage":"16 GB SSD"
}'
);

mysql> select * from products \G
*************************** 1. row ***************************
 id: 1
 name: Samsung 3 Chromebook
 brand_id: 2
category_id: 3
 attributes: {"CPU": "Intel Celeron N3060 / 1.6 GHz", "color": "black", "memory": 4, "weight": 2.54, "Storage": "16 GB SSD", "dimensions": {"d": 8, "h": 0.7, "w": 11.4}, "processor_count": 2, "operating_system": "chrome"}
1 row in set (0.00 sec)

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関数を利用して２つ目の商品の情報を登録してみましょう。

INSERT INTO products 
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Lenovo Notebook',3,3,JSON_OBJECT(
"dimensions",JSON_OBJECT("w",12.6,"d",8.8,"h",0.6),"weight",3.53,"color","platinum silver","CPU","Intel Core i7 (7th Gen) 7500U / 2.7 GHz","processor_count",2,"operating_system","Windows 10","memory",16,"storage","512 GB SSD","interfaces",JSON_ARRAY("USB","Thunderbolt","HDMI","Audio Jack"))
);

mysql> select * from products where id=2 \G
*************************** 1. row ***************************
         id: 2
       name: Lenovo Notebook
   brand_id: 3
category_id: 3
 attributes: {"CPU": "Intel Core i7 (7th Gen) 7500U / 2.7 GHz", "color": "platinum silver", "memory": 16, "weight": 3.53, "storage": "512 GB SSD", "dimensions": {"d": 8.8, "h": 0.6, "w": 12.6}, "interfaces": ["USB", "Thunderbolt", "HDMI", "Audio Jack"], "processor_count": 2, "operating_system": "Windows 10"}
1 row in set (0.00 sec)

次にもう一つの便利な関数をご紹介します。JSON_ARRAYはvalueのセットを渡すとJSON配列を返します。

さらに他にも、JSON_MERGEを使うことで複数のJSONオブジェクトをまとめて１つのJSONオブジェクトを生成することが出来ます。これは、キーと値のペアをオブジェクトとして取得する場合に便利です。JSON_MERGEを使用して次の製品に関するデータを挿入しましょう。

INSERT INTO products 
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Galaxy Tab',2,4,
JSON_MERGE(
'{"display_size":10.1}',
'{"operating_system":"Android Marshmallow"}',
'{"storage":"16 GB"}',
'{"color":"White"}',
'{"memory":2}',
'{"camera":"8 MegaPixel"}'
));

mysql> select * from products where id=3 \G
*************************** 1. row ***************************
         id: 3
       name: Galaxy Tab
   brand_id: 2
category_id: 4
 attributes: {"color": "White", "camera": "8 MegaPixel", "memory": 2, "storage": "16 GB", "display_size": 10.1, "operating_system": "Android Marshmallow"}
1 row in set (0.00 sec)

JSON_OBJECTとJSON_MERGEを組み合わせて使用すると、作業が簡単になります。

INSERT INTO products 
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Lenovo Tab',3,4,
JSON_MERGE(
JSON_OBJECT("display_size",10.1),
JSON_OBJECT("operating_system","Android"),
JSON_OBJECT("storage","16 GB"),
JSON_OBJECT("color","Black"),
JSON_OBJECT("memory",16),
JSON_OBJECT("camera","5 MegaPixel")
));


mysql> select * from products where id=4 \G
*************************** 1. row ***************************
         id: 4
       name: Lenovo Tab
   brand_id: 3
category_id: 4
 attributes: {"color": "Black", "camera": "5 MegaPixel", "memory": 16 , "storage": "16 GB", "display_size": 10.1, "operating_system": "Android"}
1 row in set (0.00 sec)

もう少し行を足してみましょう。

INSERT INTO products
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Asus Vivobook',5,3,'{"CPU": "Intel Pentium mobile processor N4200", "color": "black", "memory": 4, "weight": 4.10, "storage": "500 GB SSD", "graphics": "Intel HD Graphics 500", "dimensions": {"d": 11.5, "h": 2.6, "w": 19.8}, "interfaces": ["USB", "Thunderbolt", "HDMI", "Audio Jack"], "processor_count": 4, "operating_system": "Windows 10"}'),
('Macbook Pro',1,3,'{"CPU": "Intel Core i7", "color": "Silver", "memory": 16, "weight": 4.49, "storage": "256 GB SSD", "graphics": "Intel Iris Pro Graphics", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "interfaces": ["USB", "Thunderbolt", "Audio Jack"], "processor_count": 4, "operating_system": "Mac OS X"}'),
('Apple iPad',1,4,'{"color": "Space Gray", "camera": "1.2 MegaPixel", "memory": 16, "storage": "16 GB", "display_size": 9.7, "operating_system": "iOS 10"}'),
('Apple iPad',1,4,'{"color": "Space Gray", "camera": "1.2 MegaPixel", "memory": 32, "storage": "16 GB", "display_size": 9.7, "operating_system": "iOS 10"}'),
('S8',2,1,'{"color": "Rose Pink", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Nougat"}'),
('Note',2,1,'{"color": "Black ", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "dimensions": {"d": 0.3, "h": 3, "w": 6.3}, "Screen Size": 5.8, "operating_system": "Android Nougat"}'),
('iPhone 7 plus',1,1,'{"color": "Silver ", "camera": "12 MegaPixel", "memory": 32, "weight": "120 g", "dimensions": {"d": 3.1, "h": 0.29, "w": 6.2}, "Screen Size": 5.5, "operating_system": "iOS 10"}'),
(' iPhone 6',1,1,'{"color": "Silver ", "camera": "12 MegaPixel", "memory": 16, "weight": "150 g", "dimensions": {"d": 3.1, "h": 0.29, "w": 6.2}, "Screen Size": 4.7, "operating_system": "iOS 10"}');

ここではいくつかの行を追加したので、次にJSONオブジェクトに対してクエリを実行してみましょう。

Selecting and filtering JSON data

次の様なSELECTクエリを書くのは簡単です。

SELECT * FROM products WHERE attributes like '{"color": "Black", "camera": "5 MegaPixel", "memory": 16, "storage": "16 GB", "display_size": 10.1, "operating_system": "Android"}'\G
*************************** 1. row ***************************
         id: 4
       name: Lenovo Tab
   brand_id: 3
category_id: 4
 attributes: {"color": "Black", "camera": "5 MegaPixel", "memory": 16 , "storage": "16 GB", "display_size": 10.1, "operating_system": "Android"}
1 row in set (0.00 sec)

ただし、これはデータベースをクエリするのに最も効率的または有用な方法ではありません。使用する関数はJSON_EXTRACTです。たとえば、所有しているすべてのAndroid搭載端末を検索するとします。

SELECT name,attributes->'$.operating_system' as operating_system FROM products WHERE JSON_EXTRACT(attributes,'$.operating_system') like '"Android%';
+------------+-----------------------+
| name       | operating_system      |
+------------+-----------------------+
| Galaxy Tab | "Android Marshmallow" |
| Lenovo Tab | "Android"             |
| S8         | "Android Nougat"      |
| Note       | "Android Nougat"      |
+------------+-----------------------+
4 rows in set (0.00 sec)

ここで注意すべき3つのことがあります。まず、JSON_EXTRACTはJSONから比較とフィルタリングに使用できる属性を抽出できます。

次に、select句で属性を読み取るためのもう一つの短縮形が利用できます: attributes->'$.operating_system'。

加えて、JSON_EXTRACTの結果にはまだ引用符が含まれています。これは次の関数JSON_UNQUOTEで解決できます。JSON_UNQUOTEを使ってクエリを書き直し、違いを見てみましょう。

SELECT name,JSON_UNQUOTE(attributes->'$.operating_system') as operating_system FROM products WHERE JSON_UNQUOTE(JSON_EXTRACT(attributes,'$.operating_system')) like 'Android%';

+------------+---------------------+
| name       | operating_system    |
+------------+---------------------+
| Galaxy Tab | Android Marshmallow |
| Lenovo Tab | Android             |
| S8         | Android Nougat      |
| Note       | Android Nougat      |
+------------+---------------------+
4 rows in set (0.00 sec)

MySQLはJSONカラムのインデックスをサポートしていませんが、仮想列を使用して簡単に回避できます。

mysql> alter table products add key idx_attributes (attributes);
ERROR 3152 (42000): JSON column 'attributes' cannot be used in key specification.

それぞれのデバイスのメモリを格納する生成列を追加しましょう。

mysql> alter table products add column memory int as (attributes->'$.memory');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

そして、そのカラムにインデックスを追加します。

mysql> alter table products add key idx_memory (memory);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

問合せが16 GBのメモリーを持つすべてのデバイスをフェッチするための実行計画を見てみましょう。実行計画からわかるように、これはJSON列から派生した仮想列に作成したインデックスを使用します。

mysql> EXPLAIN SELECT name,memory FROM products where memory=16;
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_memory    | idx_memory | 5       | const |    5 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

もう1つの便利な関数は、JSONオブジェクトからキーを返すJSON_KEYSです。私たちがiPad用に保存する属性を確認したいとします。

mysql> select id,name,JSON_KEYS(attributes) from products where name like 'Apple iPad';
+----+------------+------------------------------------------------------------------------------+
| id | name       | JSON_KEYS(attributes)                                                        |
+----+------------+------------------------------------------------------------------------------+
|  7 | Apple iPad | ["color", "camera", "memory", "storage", "display_size", "operating_system"] |
|  8 | Apple iPad | ["color", "camera", "memory", "storage", "display_size", "operating_system"] |
+----+------------+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Updating JSON data

JSON値を更新する方法を見てみましょう。

mysql> select * from products where id=9 \G
*************************** 1. row ***************************
         id: 9
       name: S8
   brand_id: 2
category_id: 1
 attributes: {"color": "Rose Pink", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Nougat"}
     memory: 64
1 row in set (0.00 sec)

JSON_INSERT関数を使って、サポートされているネットワークに関する情報を追加しましょう。

mysql> UPDATE products SET attributes=JSON_INSERT(attributes,"$.network","CDMA") where id=9;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ご覧のとおり、JSONにネットワーク属性が追加されています。

mysql> select * from products where id=9 \G
*************************** 1. row ***************************
         id: 9
       name: S8
   brand_id: 2
category_id: 1
 attributes: {"color": "Rose Pink", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "network": "CDMA", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Nougat"}
     memory: 64
1 row in set (0.00 sec)

JSON_INSERTはJSONに属性を追加します。たとえば、operating_systemのような既存の属性を更新したいとします。この場合、JSON_REPLACE関数を使用します。

mysql> UPDATE products SET attributes=JSON_REPLACE(attributes,"$.operating_system","Android Oreo") where id=9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

現在の製品情報は以下のようになります。

mysql> select * from products where id=9 \G
*************************** 1. row ***************************
         id: 9
       name: S8
   brand_id: 2
category_id: 1
 attributes: {"color": "Rose Pink", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "network": "CDMA", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Oreo"}
     memory: 64
1 row in set (0.01 sec)

JSON_INSERTはJSONに属性を追加します。 JSON_REPLACEは、すでに存在する属性を更新します。属性が存在するかどうかわからない場合は、JSON_SET関数を使用します。既に存在する場合はJSON_SET関数が属性を更新し、それ以外の場合は属性を追加します。

mysql> UPDATE products SET attributes=JSON_SET(attributes,"$.color","Gray") WHERE id=9;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM products WHERE id=9 \G
*************************** 1. row ***************************
         id: 9
       name: S8
   brand_id: 2
category_id: 1
 attributes: {"color": "Gray", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "network": "CDMA", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Oreo"}
     memory: 64
1 row in set (0.00 sec)

更新を実行する際に便利なもう1つの関数は、JSON_ARRAY_APPENDです。この関数は、JSONの配列属性に追加します。属性がスカラー属性の場合、JSON_ARRAY_APPENDはそれを配列に変換します。

前の例から続けましょう。電話機でサポートされているネットワークにGSMを追加したいとします。

mysql> UPDATE products SET attributes=JSON_ARRAY_APPEND(attributes,"$.network","GSM") WHERE id=9;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ネットワーク属性は配列に変換され、更新された値を持ちます。

mysql> SELECT * FROM products WHERE id=9 \G
*************************** 1. row ***************************
         id: 9
       name: S8
   brand_id: 2
category_id: 1
 attributes: {"color": "Gray", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "network": ["CDMA", "GSM"], "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Oreo"}
     memory: 64
1 row in set (0.00 sec)

JSONオブジェクトの削除操作

JSONオブジェクトの削除操作は、2つの側面があります。JSONから属性を削除することができます。または、JSONオブジェクトの属性に基づいて行自体を削除することもできます。

JSONから属性を削除するには、JSON_REMOVE関数を使用します。たとえば、電話機の仕様からネットワークに関連する情報を削除します。

mysql> UPDATE products SET attributes=JSON_REMOVE(attributes,"$.network") WHERE id=9;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from products where id=9 \G
*************************** 1. row ***************************
         id: 9
       name: S8
   brand_id: 2
category_id: 1
 attributes: {"color": "Gray", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Oreo"}
     memory: 64
1 row in set (0.00 sec)

JSONに含まれる情報に基づいて行を削除するには、前にWHERE句でフィルタリングに使用したJSON_EXTRACT関数を使用します。たとえば、画面サイズが5未満のすべてのデバイスを削除するとします。

mysql> SELECT name,attributes->'$."Screen Size"' as screen_size FROM products WHERE JSON_EXTRACT(attributes,'$."Screen Size"')<5;
+----------+-------------+
| name     | screen_size |
+----------+-------------+
| iPhone 6 | 4.7         |
+----------+-------------+
1 row in set (0.00 sec)

JSON_EXTRACT関数を使ってDELETEを試してみましょう。

mysql> DELETE FROM products WHERE JSON_EXTRACT(attributes,'$."Screen Size"')<5;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT name,attributes->'$."Screen Size"' as screen_size FROM products WHERE JSON_EXTRACT(attributes,'$."Screen Size"')<5;
Empty set (0.00 sec)

要約すると、データを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.

翻訳は星野が担当しました (原文はこちら)

Amazon Web Services ブログ