49
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

posted at

updated at

MySQL5.7でJSON型を試してみた

まえがき

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すればよいようですね。

JSONの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をすると、どういうデータが返ってくるかというと、もうそのまんま。

JSON型のread
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しています。

WHERE句によるJSON型のread
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()を思い出しながら、下の文をみてもらえば、やっていることはわかると思います。

JSON_EXTRACT()関数で抽出した値同士の加算値をread
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()関数による値の抽出を用いた条件指定です。

UPDATEとJSON_SET()関数によるJSON内の値の更新
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で上書き更新されているのがわかります。

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()関数を組み合わせればできますね。

要素yを+10する
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, ...)のように交互に複数指定できます。

また、わかりやすいように、前述までのデータは一旦削除して、新しいデータで確認しています。

JSON_INSERT()関数で要素を挿入する
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に意味があるかは議論しません)。

第1引数をJSON型カラム名ではなくJSON_OBJECT()関数で生成したJSONオブジェクトを指定する
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}}」がカラムに保存されているのがわかります。

JSON_SET()関数の結果を確認
mysql> SELECT * FROM `json_test`;
+-------------------------------+
| col                           |
+-------------------------------+
| {"alpha": {"x": 30, "y": 20}} |
+-------------------------------+
1 row in set (0.00 sec)

JSON_SET()関数が直接値を更新せずに、更新したJSONデータ全体を返す理由というのは、必ずしも操作対象がJSON型のカラムにとどまらないという点からも納得ができるものと思われます。

次回は@atsuizoさんです。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
49
Help us understand the problem. What are the problem?