MariaDBでJSON型を使う

使いたい人生だった

Qittaのとある記事みてMySQL5.7から追加されたJSON型で遊んでみたくなった。さっそく手元のDBで試そうと思ったら

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON

ﾌｧｰｰｰｰｰｰｰｰｰｰｰってそういえば手元のDBMySQLじゃなくてMariaDBだったは（

で終わり、は流石にアレなのでMariaDBのDynamic Columnsで遊んでみた

環境

• MariaDB 10.2

試す

まずは適当なjson_testテーブルを作成（正確にはJSONではないがJSON型があると思って作ってしまったので

CREATE TABLE `json_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `json` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

１つデータを追加してみる

INSERT INTO json_test VALUES (1, COLUMN_CREATE('name', 'taro', 'color', 'red'));

いけたのでもう１つ適当に

INSERT INTO json_test VALUES (2, COLUMN_CREATE('name', 'hanako', 'color', 'black'));

見てみよう

mysql> SELECT * from json_test
+----+-------------------------------------+
| id | json                                |
+----+-------------------------------------+
|  1 |           S namecolor!taro!red     |
|  2 |           s namecolor!hanako!black |
+----+-------------------------------------+

お、おう？

中身も表示させたい場合は COLUMN_JSONを使うといい感じ。JSONで取れるの便利

mysql> select id, COLUMN_JSON(json) from json_test;
+----+-----------------------------------+
| id | COLUMN_JSON(json)                 |
+----+-----------------------------------+
|  1 | {"name":"taro","color":"red"}     |
|  2 | {"name":"hanako","color":"black"} |
+----+-----------------------------------+

JSONの特定の要素のみ取得したい場合はCOLUMN_GET

mysql> select id, COLUMN_GET(json, 'name' AS char) AS name from json_test;
+----+--------+
| id | name   |
+----+--------+
|  1 | taro   |
|  2 | hanako |
+----+--------+

JSONに対してWHEREもかけることができる

SELECT id, COLUMN_JSON(json) FROM json_test WHERE COLUMN_GET(json, 'name' AS CHAR) = 'taro';
+----+-------------------------------+
| id | COLUMN_JSON(json)             |
+----+-------------------------------+
|  1 | {"name":"taro","color":"red"} |
+----+-------------------------------+

今までにないカラムを追加してみる

INSERT INTO json_test VALUES (3, COLUMN_CREATE('name', 'piyo', 'color', 'blue', 'age', 33));

見てみよう

mysql> select id, COLUMN_JSON(json) from json_test;
+----+-----------------------------------------+
| id | COLUMN_JSON(json)                       |
+----+-----------------------------------------+
|  1 | {"name":"taro","color":"red"}           |
|  2 | {"name":"hanako","color":"black"}       |
|  3 | {"age":33,"name":"piyo","color":"blue"} |
+----+-----------------------------------------+

値のないものはNULLになる

mysql> select id, COLUMN_GET(json, 'age' AS integer) AS name from json_test;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 |   33 |
+----+------+