MySQL で json 配列の要素一つ一つをレコードとする

Published: 2019/3/2

MySQL で json カラムを用いた場合、その中身に json 配列を記録することができる。普通に機能を開発していくと、この json 配列の中身から、その要素一つ一つを SQL 上のレコード１行に展開したい場合が出てくる。それを実現するためには、 MySQL 8.0.4(?) から導入された JSON_TABLE を用いると良い、という話。

例えば、以下のようなテーブルがあったとする。

create table entries(
  id integer primary key,
  tags json
);

insert into entries values
(1, '["algorithm", "java"]'),
(2, '["java", "android"]');

mysql> select * from entries;
+----+-----------------------+
| id | tags                  |
+----+-----------------------+
|  1 | ["algorithm", "java"] |
|  2 | ["java", "android"]   |
+----+-----------------------+

これに対して、以下のようなクエリを実行すれば、各 tags の要素をレコードに展開できる。

select * from
  entries,
  JSON_TABLE(tags, '$[*]' columns (tag VARCHAR(255) path '$')) as jt;

+----+-----------------------+-----------+
| id | tags                  | tag       |
+----+-----------------------+-----------+
|  1 | ["algorithm", "java"] | algorithm |
|  1 | ["algorithm", "java"] | java      |
|  2 | ["java", "android"]   | java      |
|  2 | ["java", "android"]   | android   |
+----+-----------------------+-----------+