MySQL8.0から追加された機能の一つに JSON_TABLE()
関数がある。これを使うとJSON型のデータを表形式に出力することができる。
MySQLをドキュメントストアとして使う場合、この関数を使用することで別のテーブルとJOINできるようになるので重要な関数だ。
今回はこれを使ってJSONデータをCSVに変換する簡単な方法を紹介する。
どうしてこうなった
内閣府から提供されているRESAS-APIから市町村データを取得してCSVにしたい、というタスクがあった。 以下の2つのAPIで都道府県と市町村の一覧が取得できるので、都道府県ごとに結合して一つのCSVファイルを作ることを目指した。
このAPIは簡単に利用登録ができ、発行されるAPI_KEYを使って以下のようにAPIをコールできる。
$ export API_KEI=<your_api_key> $ curl -H "X-API-KEY: $API_KEY" -X GET 'https://opendata.resas-portal.go.jp/api/v1/prefecture'
MySQL8.0を利用する
MySQLは5.7からJSON型を利用できるので、とりあえずAPIから取得したJSONをそのままテーブルに入れてみよう、ということで以下のようにテーブルを作成した。
CREATE TABLE `j_prefectures` ( `doc` json DEFAULT NULL ) ENGINE=InnoDB; CREATE TABLE `j_cities` ( `doc` json DEFAULT NULL ) ENGINE=InnoDB;
とてもシンプル。
ワンライナーでMySQLにJSONを入れる
都道府県のAPIは1回コールすればJSONが取得できるので、それをワンライナーでそのままテーブルにINSERTする。 テーブルもシンプルなのでSQLもシンプルにできるため、プログラミング言語を使う必要はない。
$ mysql -u root -p[password] resas < <(echo "insert into j_prefectures (\`doc\`) values ('" $(curl -H 'X-API-KEY: '$API_KEY -X GET 'https://opendata.resas-portal.go.jp/api/v1/prefectures') "')" )
簡単に解説しておく。
mysql -u root -p < file.sql
という構文でfile.sql
に書かれたSQL文を実行できる。
bashの機能で<( command )
とするとcommand
の出力をファイルとして扱ってくれる。
なのでecho
とcurl
でINSERT文を作ってmysql
に渡している。
また -p[password]
とコマンドラインからパスワードを渡すことができるが、コマンド履歴に残るので注意が必要だ。
同様に市町村一覧もAPIから取得してINSERTする。市町村のAPIはprefCode
を指定しないといけないので1から47まで値をかえてコールする。
これもワンライナーで実行できる。
$ while read code; do mysql -u root -p[password] resas < <(echo "insert into j_cities(\`doc\`) values ('" $(curl -H 'X-API-KEY: '$API_KEY -X GET 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode='$code) "')"); done < <(seq 1 47)
JSON_TABLE() を使って表にする
JSON_TABLE()を使うと、都道府県と市町村をそれぞれ表で出力することができる。
select prefecture.* from j_prefectures, JSON_TABLE( `doc`, "$.result[*]" columns ( prefCode int path '$.prefCode', prefName varchar(64) path '$.prefName' ) ) prefecture; +----------+-----------+ | prefCode | prefName | +----------+-----------+ | 1 | 北海道 | | 2 | 青森県 | | 3 | 岩手県 | | 4 | 宮城県 | | 5 | 秋田県 |
select cities.* from j_cities, JSON_TABLE( `doc`, "$.result[*]" columns ( prefCode int path '$.prefCode', cityCode varchar(64) path '$.cityCode', cityName varchar(64) path '$.cityName', bigCityFlag varchar(4) path '$.bigCityFlag' ) ) cities; +----------+----------+--------------------+-------------+ | prefCode | cityCode | cityName | bigCityFlag | +----------+----------+--------------------+-------------+ | 1 | 01100 | 札幌市 | 2 | | 1 | 01101 | 札幌市中央区 | 1 | | 1 | 01102 | 札幌市北区 | 1 | | 1 | 01103 | 札幌市東区 | 1 | | 1 | 01104 | 札幌市白石区 | 1 |
JSON_TABLE()のバグ
じつは JSON_TABLE() で作成された表へのアクセスはroot権限のユーザーしかできない。
このバグはMySQL Bugs: #90610: ERROR 1142 (42000) when using JSON_TABLEで報告されていて 8.0.13
で修正されるようだ。
CTEでラップしてJOINする
MySQL8.0からCTEが利用できるので、JSON_TABLE()で作成した表どうしをJOINすることができる。
with prefectures as ( select pref_tbl.* from j_prefectures, JSON_TABLE( `doc`, "$.result[*]" columns ( prefCode int path '$.prefCode', prefName varchar(64) path '$.prefName' ) ) pref_tbl ), cities as ( select city_tbl.* from j_cities, JSON_TABLE( `doc`, "$.result[*]" columns ( prefCode int path '$.prefCode', cityCode varchar(64) path '$.cityCode', cityName varchar(64) path '$.cityName', bigCityFlag varchar(4) path '$.bigCityFlag' ) ) city_tbl ) select * from prefectures join cities using (prefCode); +----------+-----------+----------+--------------------+-------------+ | prefCode | prefName | cityCode | cityName | bigCityFlag | +----------+-----------+----------+--------------------+-------------+ | 1 | 北海道 | 01100 | 札幌市 | 2 | | 1 | 北海道 | 01101 | 札幌市中央区 | 1 | | 1 | 北海道 | 01102 | 札幌市北区 | 1 | | 1 | 北海道 | 01103 | 札幌市東区 | 1 | | 1 | 北海道 | 01104 | 札幌市白石区 | 1 |
CSVで出力する
これでAPIから取得したJSONを結合して一つの表にできたのでCSVファイルに出力する。
select * from prefectures join cities using (prefCode) into outfile '/var/lib/mysql-files/prefecture.csv' fields terminated by ',' ;
secure-file-prev による制限
secure-file-prev
システム変数によって SELECT ... INTO OUTFILE
などのデータの入出力が制限されているため、出力先には気をつける必要がある。
MySQL :: MySQL 8.0 Reference Manual :: 5.1.6 Server Command Options
SHOW VARIABLES
で確認できるので、出力先はこれに指定されているディレクトリにしよう。
mysql> show variables like 'secure%'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec)
まとめ
MySQL8.0で強化された機能を使うことで、JSONを使うタスクを簡単に処理できるので、この記事を読んだ人は是非試してみよう。