Yukii's Blog

Yet Another Tech Blog.

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

2019/03/02

MySQL で json カラムを用いた場合、その中身に json 配列を記録することができる。 普通に機能を開発していくと、この json 配列の中身から、その要素一つ一つを SQL 上のレコード1行に展開したい場合が出てくる。 それを実現するためには、 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   |
+----+-----------------------+-----------+

詳細は公式ドキュメント参照。

tags:  mysql