1:N関連のテーブルデータをJSONでいい感じに取得する（MySQL）

ORMは使いたくないけど、1:N関連のテーブルデータをJOINしていい感じに取得したいというケースはそれなりにあるのではないかと思います。

## 問題

例えば、ユーザーと投稿テーブルがあって、この関連が1:Nになる時、
SQLをJOINして取得する結果は以下のようになります。

```:ユーザーテーブル
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
```

```:投稿テーブル
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id | int(11)      | NO   | MUL | NULL    |                |
| title   | varchar(255) | NO   |     | NULL    |                |
| body    | text         | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
```

```:クエリの取得結果
mysql> SELECT u.id, u.name, p.title, p.body FROM `ユーザー` AS u LEFT JOIN `投稿` AS p ON u.id = p.user_id;
+----+------+-------+------+
| id | name | title | body |
+----+------+-------+------+
|  1 | jon  | Foo   | TODO |
|  1 | jon  | Bar   | TODO |
|  1 | jon  | Baz   | TODO |
|  2 | tom  | NULL  | NULL |
+----+------+-------+------+
```

これをプログラムから扱う場合、以下のような形式になるでしょう。
_（以下のような文法・機能を持つプログラミング言語があるかはわかりません、雰囲気で読み取ってください）_

```js
let rows = db.exec(sql);
log(rows);
// [
//   { id: 1, name: "jon", title: "Foo", body: "TODO" },
//   { id: 1, name: "jon", title: "Bar", body: "TODO" },
//   { id: 1, name: "jon", title: "Baz", body: "TODO" },
//   { id: 2, name: "tom", title: null, body: null }
// ]
```

上記のデータは `id` と `name` が重複しているレコードを幾つか含んでおり無駄があります。
プログラムから扱う場合は以下のようにデータがネストしているほうが扱いやすいはずです。

```js
[
  {
    id: 1,
    name: "jon",
    posts: [
      {
        title: "Foo",
        body: "TODO"
      },
      {
        title: "Bar",
        body: "TODO"
      },
      {
        title: "Baz",
        body: "TODO"
      },
    ]
  },
  {
    id: 2,
    name: "jon",
    posts: null
  }
]
```

## 解決

MySQL5.7からはJSON型の登場にともない、データをJSONとして整形する関数が追加されています。
このJSONの整形関数を利用することで、一度のクエリからプログラムが望む形式のデータ構造をもたせることが可能になります。

```sql
SELECT
    JSON_OBJECT(
        "id", u.id,
        "name", u.name,
        "posts", (
            -- JSONにしてしまえば複数行を返すはずのサブクエリもスカラ・サブクエリとして扱えるようになる
            SELECT
                JSON_ARRAYAGG(JSON_OBJECT(
                    "title", p.title,
                    "body", p.body
                ))
            FROM
                (SELECT * FROM `投稿` WHERE u.id = user_id) AS p
        )
    ) AS json
FROM
    `ユーザー` AS u
;
```

このクエリの結果は以下のような形で受け取ることが出来ます。
理想的ですね！

```
+-------------------------------------------------------------------------------------------------------------------------------------------+
| json                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| {"id": 1, "name": "jon", "posts": [{"body": "TODO", "title": "Foo"}, {"body": "TODO", "title": "Bar"}, {"body": "TODO", "title": "Baz"}]} |
| {"id": 2, "name": "tom", "posts": null}                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------+
```

このようなデータ構造を得たい時、多くのプログラムは何らかのORMに頼ることになるかと思いますが、
ORMを利用するほどでもない小さなプログラムではこのようにSQLだけで解決してしまうのも一つの手段として取れるので覚えておくと便利ですね！