Edited at

MySQL5.7でJSONを使ってみる

More than 1 year has passed since last update.

MySQL5.7でJSONが使えるようになったというので、少し理解を深めようと思います。

作業メモレベルで恐縮なのですが記載します。


ドキュメントについて

公式のドキュメントは以下となります。

http://dev.mysql.com/doc/refman/5.7/en/json.html

他のリンクも色々と見ていたのですが、動かないことが多かったので徒労に終わりました。。


MySQL5.7のインストール

ubuntu14.04にて作業しています。

以下のような感じでインストールしました。


$ wget http://dev.mysql.com/get/mysql-apt-config_0.3.5-1ubuntu14.04_all.deb
$ sudo dpkg -i mysql-apt-config_0.3.5-1ubuntu14.04_all.deb
$ sudo apt-get update
$ sudo apt-get install mysql-server-5.7


実際にやってみる

mysqlを起動し、公式ドキュメントの通りに動かすと程よく動きます。


JSON_TYPE

引数にあるものを確認するものみたいです。


mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING |
+----------------------+
1 row in set (0.00 sec)


JSON_ARRAY

引数にあるものを配列に変換するようです。


mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-08-19 17:38:20.000000"] |
+----------------------------------------+
1 row in set (0.00 sec)


JSON_OBJECT

引数にあるものをJSONに変換するようです。


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_MERGE

引数にあるものをマージするようです。


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)

JSON_MERGEを使う場合、ちょっと癖がありそうですね。詳細は以下URLとなります。

http://dev.mysql.com/doc/refman/5.7/en/json.html#json-normalization


JSON_EXTRACT

JSONの値を検索します。$.key名で値を探し、ない場合はNULLが返ってきます。


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('{"id": 14, "name": "Aztalan"}', '$.hoge');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.hoge') |
+---------------------------------------------------------+
| NULL |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ネストしたJSONを検索する場合は以下のようにすると良いみたいでした。


mysql> SELECT JSON_EXTRACT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','$.f4.f6');
+---------------------------------------------------------------------+
| JSON_EXTRACT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','$.f4.f6') |
+---------------------------------------------------------------------+
| "foo" |
+---------------------------------------------------------------------+
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)

配列の中を指定する場合、$.c[1]のようにすると値を取得することが出来ます。


mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[1]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[1]') |
+------------------------------------------------------------+
| 4 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

配列の中をすべて取ってくる場合、$.c[*]とすると良いみたいです。


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)

aとかcにある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)


JSON_UNQUOTE

文字列に付いているダブルクォートを取り除きます。前述にあるJSON_EXTRACTで取得した値はダブルクォートが付いているので、JSON_UNQUOTEを付けることによって取り除くことが出来るようです。


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_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'));
+-----------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name')) |
+-----------------------------------------------------------------------+
| Aztalan |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

また、バージョンによって使い方が変わってくるようでした。以下については、5.7.9から利用できる方法です。

mysql> SELECT JSON_UNQUOTE(a.json_data->'$.name') FROM (SELECT '{"id": 14, "name": "Aztalan"}' AS json_data) a;

+-------------------------------------+
| JSON_UNQUOTE(a.json_data->'$.name') |
+-------------------------------------+
| Aztalan |
+-------------------------------------+
1 row in set (0.00 sec)

5.7.13以降から以下のような方法で取得することが出来るようです。

mysql> SELECT a.json_data->>'$.name' FROM (SELECT '{"id": 14, "name": "Aztalan"}' AS json_data) a;

+------------------------+
| a.json_data->>'$.name' |
+------------------------+
| Aztalan |
+------------------------+
1 row in set (0.00 sec)

表でまとめると以下のようになります。

version
query

5.7.8
JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.name'))

5.7.9
JSON_UNQUOTE(json_data->'$.name')

5.7.13
json_data->>'$.name'


とりあえず、検索は出来そうな感じもしますが引き続き調査しながらメモを残すようにしていきます。