とある事情で複数行のレコードを一つの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
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