まえがき
Qiita始めて大して経ってないけど、なにやらAdvent Calendarが盛り上がっていて、たまたまMySQL5.7のJSONのことを簡単にまとめておこうと記事を書いていたらMySQL Casual Advent Calendar 2016を偶然見つけたので、勝手に参加してみました(参加に問題があったらご連絡ください)。
概要
MySQL5.7から導入されたJSON型というのを試してみました。なお、ここでは簡単なテストのみなので、JSON型の負荷やコストなどの評価等はいっさいやっておりません(DB屋じゃないし…)。
テーブル作成
単純なテストなので、凝らずに1カラムJSON型だけでテーブルを作ります。
mysql> CREATE TEMPORARY TABLE `json_test` (`col` JSON);
Query OK, 0 rows affected (0.00 sec)
JSONのINSERT
特別なことはなにもなく、ふつうにINSERTすればよいようですね。
mysql> INSERT INTO `json_test` VALUES ('{"x":10, "y":20}'), ('{"x":20, "y":40}'), ('{"x":30, "y":60}'), ('{"x":40, "y":80}'), ('{"x":50, "y":100}');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
INSERTしたらSELECT
SELECTをすると、どういうデータが返ってくるかというと、もうそのまんま。
mysql> SELECT * FROM `json_test`;
+---------------------+
| col |
+---------------------+
| {"x": 10, "y": 20} |
| {"x": 20, "y": 40} |
| {"x": 30, "y": 60} |
| {"x": 40, "y": 80} |
| {"x": 50, "y": 100} |
+---------------------+
5 rows in set (0.00 sec)
WHERE句も使える
JSON_EXTRACT()関数はJSON操作関数のうち、JSONデータ内を検索する機能を持っています。WHERE句でJSON_EXTRACT()関数を使うことで、保存先のカラムにあるJSONデータをparseした上で、内部のデータとの比較演算をしてSELECTを処理してくれます(というかMySQL内部ではバイナリデータで保持しているので厳密にはparseではないか…)。
ここでは「JSON_EXTRACT('col', '$.x')」という関数呼び出しをしていますが、これはcolカラムにあるJSON型のデータから、キーxのデータを抽出するという意味になります。
JSON_EXTRACT()関数の第2引数にあるprefix「$.」は、「$」がJSONドキュメント全体を意味し、「.」で階層的にオブジェクト名を表していくという構文のようです。
抽出した値が30以上のレコードをSELECTしています。
mysql> SELECT * FROM `json_test` WHERE JSON_EXTRACT(`col`, '$.x') >= 30;
+---------------------+
| col |
+---------------------+
| {"x": 30, "y": 60} |
| {"x": 40, "y": 80} |
| {"x": 50, "y": 100} |
+---------------------+
3 rows in set (0.00 sec)
演算もできる
ここまでくると、あたりまえのような使い方ができるのがわかります。
要するに「SELECT x + y AS sum_x_y FROM table WHERE x >= 10 AND y >= 10;」みたいなことを、JSON型において、さきほどのJSON_EXTRACT()関数を用いれば可能ということです。
前述のSELECTとJSON_EXTRACT()を思い出しながら、下の文をみてもらえば、やっていることはわかると思います。
mysql> SELECT JSON_EXTRACT(`col`, '$.x') + JSON_EXTRACT(`col`, '$.y') AS `x_plus_y` FROM `json_test` WHERE JSON_EXTRACT(`col`, '$.x') >= 30;
+----------+
| x_plus_y |
+----------+
| 90 |
| 120 |
| 150 |
+----------+
3 rows in set (0.00 sec)
更新もできる
JSON内部の値を更新することも可能です。JSON_SET()関数を使うことで、要素を特定した上でその値に更新をかけることができます。
JSON_EXTRACT()関数から予想できるように、JSON_SET()関数では第1、第2引数まではJSON_EXTRACT()と同じようにカラムとJSON内のオブジェクト名を指定し、第三引数で更新する値を指定します。
ただしJSON_SET()関数はこのまま更新をかけるわけではなく、更新したJSONデータ全体を返り値として出力するので、改めてcolカラムに入れる必要があります。そこでSET句にて「col = JSON_SET(...)」として使います(なぜJSON_SET()で直接値を更新しないのかは、末尾の余談をご参照ください)。
ここではcolカラムのJSON内の要素yの値を2に更新したJSONデータ全体をSET句にてcolに代入しています。WHERE句では、前述でみてきたとおりのJSON_EXTRACT()関数による値の抽出を用いた条件指定です。
mysql> UPDATE `json_test` SET `col` = JSON_SET(`col`, '$.y', 2) WHERE JSON_EXTRACT(`col`, '$.x') >= 20;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
SELECT文で全データを出力してみましょう。
JSONデータ中の要素xの値が20以上のレコードについて、要素yの値が2で上書き更新されているのがわかります。
mysql> SELECT * FROM `json_test`;
+--------------------+
| col |
+--------------------+
| {"x": 10, "y": 20} |
| {"x": 20, "y": 2} |
| {"x": 30, "y": 2} |
| {"x": 40, "y": 2} |
| {"x": 50, "y": 2} |
+--------------------+
5 rows in set (0.00 sec)
要素に加算する
JSON内の要素yに+10するには、JSON_EXTRACT()関数とJSON_SET()関数を組み合わせればできますね。
mysql> UPDATE `json_test` SET `col` = JSON_SET(`col`, '$.y', JSON_EXTRACT(`col`, '$.y') + 10) WHERE JSON_EXTRACT(`col`, '$.x') >= 20;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM `json_test`;
+--------------------+
| col |
+--------------------+
| {"x": 10, "y": 20} |
| {"x": 20, "y": 12} |
| {"x": 30, "y": 12} |
| {"x": 40, "y": 12} |
| {"x": 50, "y": 12} |
+--------------------+
5 rows in set (0.00 sec)
要素を挿入する
既存のJSONデータに要素を挿入することもできます。JSON_INSERT()関数によって、挿入する際の要素名とその値を指定することで、指定のJSONデータへの要素を挿入します。
なおJSON_OBJECT()関数は、キーと値のペアを引数としてJSONオブジェクトを生成します。JSON_OBJECT(key, value)という形式で使用し、keyとvalueはJSON_OBJECT(key, value, key, value, ...)のように交互に複数指定できます。
また、わかりやすいように、前述までのデータは一旦削除して、新しいデータで確認しています。
mysql> TRUNCATE `json_test`;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `json_test` VALUES ('{"alpha":{"x":10, "y":20}, "bravo":[123,234,456]}');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM `json_test`;
+---------------------------------------------------------+
| col |
+---------------------------------------------------------+
| {"alpha": {"x": 10, "y": 20}, "bravo": [123, 234, 456]} |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> UPDATE `json_test` SET `col` = JSON_INSERT(`col`, '$.charlie', JSON_OBJECT('z', 999));
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM `json_test`;
+--------------------------------------------------------------------------------+
| col |
+--------------------------------------------------------------------------------+
| {"alpha": {"x": 10, "y": 20}, "bravo": [123, 234, 456], "charlie": {"z": 999}} |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
余談:JSON_*()関数の第1引数はJSON型カラム名だけに限らない
JSON_EXTRACT()やJSON_SET()などのJSON操作関数の第1引数は、JSON型のカラムを指定するとは限りません。JSONデータ型であればカラム名以外でも問題ありません。
たとえば以下の例では、JSON_SET()の第1引数において、JSON_OBJECT()関数を使って「{"alpha": {"x":10, "y":20}}」に相当するデータを生成しています。
第2引数では要素alpha中の要素xを指定。ここの値を第3引数となる30で置き換えるということをしています(あくまで例なので、このSQLに意味があるかは議論しません)。
mysql> INSERT INTO `json_test` VALUES (JSON_SET(JSON_OBJECT('alpha', JSON_OBJECT('x', 10, 'y', 20)), '$.alpha.x', 30));
Query OK, 1 row affected (0.00 sec)
SELECT文でJSON_SET()関数の結果を確認すると、第1引数の「{"alpha": {"x":10, "y":20}}」ではなく要素xを第3引数の30で置き換えた「{"alpha": {"x": 30, "y": 20}}」がカラムに保存されているのがわかります。
mysql> SELECT * FROM `json_test`;
+-------------------------------+
| col |
+-------------------------------+
| {"alpha": {"x": 30, "y": 20}} |
+-------------------------------+
1 row in set (0.00 sec)
JSON_SET()関数が直接値を更新せずに、更新したJSONデータ全体を返す理由というのは、必ずしも操作対象がJSON型のカラムにとどまらないという点からも納得ができるものと思われます。
次回は@atsuizoさんです。