【MySQL】 group_concatでjsonを作る

とある事情で複数行のレコードを一つのjsonに押し込みたかったのでgroup_concatでjsonを作成しました。

やりたいこと

Authors
id	name
1	author1

Books
id	name	author_id
1	book1	1
2	book2	1
以上のAuthorsテーブルとBooksテーブルから

id	name	books
1	author1	[{"id":1,"name":"book1"},{"id":2,"name":"book2"}]

のような配列型のjsonを含むデータを作ります。

SQL

.sql

select 
Authors.id, 
Authors.name, 
concat('[', trim(trailing ',' from group_concat('{"id":', Books.id, ',"name":"', Books.name, '"},')), ']')
from Authors inner join Books on Books.author_id = Authors.id
group by Authors.id;

解説

group_concatは連結した文字列を返す集約関数です。
これを使って、複数行のレコードを一つの文字列にまとめます。
イメージとしては、1レコードに対するconcatを集約されたレコードに対して繰り返して全部繋げる感じです。

これだけだと最後のカンマが余るのでtrim関数にtrailingオプションをつけて一番右のカンマを削除します。

最後にconcatで[]で囲って完了です。

参考リンク

trim関数やgroup_concat関数の詳細については以下のリンクを参考にしてください。
trim
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_trim
group_concat
https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_group-concat