MySQL でテーブル定義を JSON形式で吐き出す

諸事情でテーブル名と、テーブルのカラムを以下のような JSON 形式で出す必要があった。

{
  "<テーブル名1>": [
    "<カラム名1>",
    "<カラム名2>",
    ...
  ],
  "<テーブル名2>": [
    "<カラム名1>",
    "<カラム名2>",
    ...
  ]
}

最初は MySQL でテーブル定義を csv に吐き出して、スプレッドシートに貼り付けて、GAS で JSON 形式に変換する… みたいな回りくどいことをやっていたけど、JSON 周りの関数を使えばどうやら MySQL 一発でいけそうだった。

SELECT
    JSON_OBJECTAGG(
        c1.table_name, 
        (
            SELECT JSON_ARRAYAGG(column_name)
                FROM information_schema.columns as c2
                WHERE c2.table_name = c1.table_name
        )
    )
FROM information_schema.columns as c1
WHERE table_schema = '<DB名>'
;

これらの関数は MySQL 5.7.22 以降なら使える模様だけど、sort には未対応っぽいのでいい感じに並べ替えたいなら別な方法を取る必要があるかも。

Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.

MySQL :: MySQL 8.0 Reference Manual :: 12.19.1 Aggregate Function Descriptions