MySQL 5.7 の新機能を見たらあまりにも多くて驚いたけど，簡単に動かしてみようと思って，今回は新たに追加された JSON 型を試してみた．
Docker で MySQL 5.7 を動かす
Docker Hub に書いてある手順の通りにコンテナを起動すると，ちゃんと MySQL 5.7 が入っていた．本当に Docker は便利だなー．
➜ ~ docker pull mysql ➜ ~ docker images REPOSITORY TAG IMAGE ID CREATED SIZE mysql latest e13b20a4f248 9 days ago 361.3 MB ➜ ~ docker run --name mysql -e MYSQL_ROOT_PASSWORD=xxxxxxxx -d mysql ➜ ~ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 2be55795004e mysql "/entrypoint.sh mysql" 13 seconds ago Up 13 seconds 3306/tcp mysql ➜ ~ docker exec -it mysql bash root@2be55795004e:/# mysql --version mysql Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using EditLine wrapper
MySQL に接続して，検証用のデータベースを作成しておく．
root@2be55795004e:/# mysql -uroot -pxxxxxxxx mysql> CREATE DATABASE sample; Query OK, 1 row affected (0.00 sec) mysql> use sample; Database changed
JSON 型
公式ドキュメントに沿って JSON 型を試してみた．
MySQL 5.7 から JSON 型を定義できるようになり，VARCHAR と同じようにデータを INSERT することができる．
また誤った JSON を INSERT しようとすると Invalid JSON text でエラーになる．アプリケーション側でチェックをすると思うけど，MySQL 側でもチェックされるから安心感があるなと思った．
mysql> CREATE TABLE t1 (jdoc JSON); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM t1; +--------------------------------------+ | jdoc | +--------------------------------------+ | {"key1": "value1", "key2": "value2"} | +--------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO t1 VALUES('[1, 2,'); ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value (or column) '[1, 2,'.
JSON_TYPE()
JSON_TYPE() 関数を使うと JSON の形式を返してくれる．どういう用途があるんだろう．
- ARRAY
- OBJECT
- STRING
mysql> SELECT JSON_TYPE('["a", "b", 1]'); +----------------------------+ | JSON_TYPE('["a", "b", 1]') | +----------------------------+ | ARRAY | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE('{"key1": "value1", "key2": "value2"}'); +---------------------------------------------------+ | JSON_TYPE('{"key1": "value1", "key2": "value2"}') | +---------------------------------------------------+ | OBJECT | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE('"hello"'); +----------------------+ | JSON_TYPE('"hello"') | +----------------------+ | STRING | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE(jdoc) FROM t1; +-----------------+ | JSON_TYPE(jdoc) | +-----------------+ | OBJECT | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE('hello'); ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0 in 'hello'.
JSON_ARRAY() / JSON_OBJECT()
JSON_ARRAY() 関数と JSON_OBJECT() 関数を使うと，パラメータから指定した形式で JSON を返してくれる．
mysql> SELECT JSON_ARRAY('a', 1, NOW()); +----------------------------------------+ | JSON_ARRAY('a', 1, NOW()) | +----------------------------------------+ | ["a", 1, "2016-02-26 09:57:12.000000"] | +----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'); +---------------------------------------+ | JSON_OBJECT('key1', 1, 'key2', 'abc') | +---------------------------------------+ | {"key1": 1, "key2": "abc"} | +---------------------------------------+ 1 row in set (0.00 sec)
よって，テーブルの各カラム情報を JSON に変換して返すこともできて，簡易的な API のようにも使える．実際に users テーブルを追加して，各カラム値を ARRAY で返す SQL を書いてみた．
mysql> CREATE TABLE users (id int(11), name varchar(255), created_at date); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO users VALUES (1, 'user1', NOW()), (2, 'user2', NOW()); Query OK, 2 rows affected, 2 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql> SELECT *, JSON_ARRAY(id, name, created_at) FROM users; +------+-------+------------+----------------------------------+ | id | name | created_at | JSON_ARRAY(id, name, created_at) | +------+-------+------------+----------------------------------+ | 1 | user1 | 2016-02-26 | [1, "user1", "2016-02-26"] | | 2 | user2 | 2016-02-26 | [2, "user2", "2016-02-26"] | +------+-------+------------+----------------------------------+ 2 rows in set (0.00 sec)
JSON_MERGE()
JSON_MERGE() 関数を使うと，複数の JSON をその名の通りに結合してくれる．
ただし，2個目の例の通り，重複するキーを結合する場合は [1, 4] のようにマージされて ARRAY になる．挙動は要注意かなと思う．
mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}'); +--------------------------------------------+ | JSON_MERGE('["a", 1]', '{"key": "value"}') | +--------------------------------------------+ | ["a", 1, {"key": "value"}] | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}'); +----------------------------------------------------+ | JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') | +----------------------------------------------------+ | {"a": [1, 4], "b": 2, "c": 3} | +----------------------------------------------------+ 1 row in set (0.00 sec)
JSON_EXTRACT()
JSON_EXTRACT() 関数を使うと，JSON に対して検索クエリを投げることができる．
$.key の形式で書く．$.* と書くと全てのキーを指定することになる．
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'); +---------------------------------------------------------+ | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') | +---------------------------------------------------------+ | "Aztalan" | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*'); +---------------------------------------------------------+ | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') | +---------------------------------------------------------+ | [1, 2, [3, 4, 5]] | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]'); +------------------------------------------------------------+ | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') | +------------------------------------------------------------+ | [3, 4, 5] | +------------------------------------------------------------+ 1 row in set (0.00 sec)
さらに $**.b の形式で書くと，第二階層にある b キーの値を配列で取得できる．
mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b'); +---------------------------------------------------------+ | JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') | +---------------------------------------------------------+ | [1, 2] | +---------------------------------------------------------+ 1 row in set (0.00 sec)
さらに MySQL 5.7.9 から -> オペレーターを使うことができる．そうすると jdoc->"$.key2" のように書くこともできる．
mysql> SELECT jdoc->"$.key2" FROM t1; +----------------+ | jdoc->"$.key2" | +----------------+ | "value2" | +----------------+ 1 row in set (0.00 sec)
ちゃんと値に対して比較もすることができて，多少複雑なクエリも書けそうだなと思った．
mysql> SELECT * FROM t1 WHERE jdoc->"$.key1" = 'value1'; +--------------------------------------+ | jdoc | +--------------------------------------+ | {"key1": "value1", "key2": "value2"} | +--------------------------------------+ 1 row in set (0.00 sec)
まとめ
公式ドキュメントを中心に簡単な SQL を投げて JSON 型を試してみた．今まで VARCHAR で管理してたシチュエーションに対して移行のメリットが少しはありそうな気がした．実際に活用してる事例などがあったら是非聞いてみたいなという感じ．
参考
前に作った Docker ハンズオン資料を久し振りに更新して boot2docker から Docker Toolbox に切り替えた．Docker の基礎的な部分を簡単に試せるように作っているので，良かったらどうぞ！
## .
## ## ## ==
## ## ## ## ## ===
/"""""""""""""""""\___/ ===
~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ / ===- ~~~
\______ o __/
\ \ __/
\____\_______/
