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