PostgreSQL : 正規化されたテーブルをSQLだけで入れ子のJSONにする

月花です。

SQL芸人としての活動のメモです。

今回は、正規化された一対多のテーブルから、多の方を一行を連想配列のJSONにしながら、複数の行をJSONにする、入れ子構造のJSONを抽出します。
結果的になんてことない単純なクエリになったのですが、割とパズル的に悩んでしまったので、残しておきます。

3人の生徒に、それぞれに2回の試験があり、その合計6つの結果を生徒ごとに分割して描画したい、というシナリオで書いていきます。
例では、PHPでHTMLを出力することにします。

通常であれば、普通にJOINして6レコードをループし、
生徒ごとにまとめてさらに3回のループを行うか、
ソートしてから、前のループと生徒IDが違えば閉じタグと開きタグを出力する、
みたいなことになりますが、条件式多くて読みづらいです。
なので、生徒ごとにまとめるところまでSQLにやらせてしまいましょう。レコードの集約はJSONにすれば、言語に依存しません。

目次です。

テーブル構造
- 生徒テーブル students
- 試験テーブル exams
- 生徒-試験リレーションテーブル student_exams
この例での求めたいデータ
- 普通にJOINしてみた場合
- 理想の抽出結果
アプローチ
- 横に広がっている試験データを連想配列に集約する
- 生徒ごとに連想配列にした試験データを集約する
取れたレコードをPHPで扱う

テーブル構造のサンプル

例では、生徒テーブルと試験テーブル、そしてそれらを繋ぐ生徒-試験リレーションテーブルの3つのテーブルが存在しているとする。
そこから、生徒テーブルを軸にして、生徒数分のレコードの中に、複数の試験をまとめて収めるJSONを書いていく。

生徒テーブル students

student_id	name
1	田中
2	佐藤
3	木村

試験テーブル exams

exam_id	name	date	math_point	japanese_point	english_point
1	中間試験	2017-09-10	10	20	30
2	中間試験	2017-09-10	40	50	60
3	中間試験	2017-09-10	70	80	90
4	期末試験	2017-09-30	15	25	35
5	期末試験	2017-09-30	45	55	65
6	期末試験	2017-09-30	75	85	95

なお、本来であれば試験名と日付も正規化するべきであるが、今回の話にはそれほど影響しないので、簡略化のために今回は正規化しない。

生徒-試験リレーションテーブル student_exams

student_id	exam_id
1	1
2	2
3	3
1	4
2	5
3	6

この例での求めたいデータ

普通にJOINしてみた場合

SELECT
    s.*,
    e.*
FROM
    students s
    JOIN student_exams se ON s.student_id=se.student_id
    JOIN exams e ON se.exam_id = e.exam_id;

で、こうなる

student_id	name	exam_id	name	date	math_point	japanese_point	english_point
1	田中	1	中間試験	2017-09-10	10	20	30
2	佐藤	2	中間試験	2017-09-10	40	50	60
3	木村	3	中間試験	2017-09-10	70	80	90
1	田中	4	期末試験	2017-09-30	15	25	35
2	佐藤	5	期末試験	2017-09-30	45	55	65
3	木村	6	期末試験	2017-09-30	75	85	95

しかし、これでは、たとえばPHPでこれを処理する場合、愚直にやれば6回ループになり、テーブルならtrタグが6つできたりする。
そういうテーブルを描画するならそれでいいが、生徒ごとのブロックとして閉じタグを挟みたい場合に厄介となる。
なので、3つにどうにか収めるように配列を操作するループを別途書いたり、ソートしてから生徒が変わったかを検知するif文を書いたりしないといけないので、端的にいってクソである。
なので、3レコードにしたい。

理想の抽出結果

student_id	name	exam_json
1	田中	{試験内容のJSON}
2	佐藤	{試験内容のJSON}
3	木村	{試験内容のJSON}

こうなればよい。
このJSONには、例でいえば、配列長が2で、その中には試験のデータをいれた1つの連想配列がはいっていればいい。
3回のループで生徒ごとに区切りつつ、内部のループで2つの試験のデータを出力すればいいのだ。
この条件に合う結果のJSONは、たとえばこうなる。

"[
    {"student_id":1,"student_name":"田中",
     "exam_data":[
        {"exam_id":1,"exam_name":"中間試験","date":"2017-09-10","math_point":10,"japanese_point":20,"english_point":30},
        {"exam_id":4,"exam_name":"中間試験","date":"2017-09-30","math_point":15,"japanese_point":25,"english_point":35}
      ]
    },
    {"student_id":2,"student_name":"佐藤",
     "exam_data":[
        {"exam_id":2,"exam_name":"中間試験","date":"2017-09-10","math_point":40,"japanese_point":50,"english_point":60},
        {"exam_id":5,"exam_name":"中間試験","date":"2017-09-30","math_point":45,"japanese_point":55,"english_point":65}
      ]
    }
]"

長くなったが、このJSONを作り、上のレコードを抽出することを目的とする。

アプローチ

まず、これを実現するためには、2回の集約が必要となる。

横に広がっている試験データを連想配列に集約する
生徒ごとに連想配列にした試験データを集約する

横に広がっている試験データを連想配列に集約する

まずは、試験データを1レコードずつ連想配列に変換していく。
次のステップで生徒IDを使うので、ここで生徒-試験リレーションテーブルをJOINして、生徒IDも含めてしまおう。

student_id	exam_id	json
1	1	{試験内容のJSON}
1	4	{試験内容のJSON}
2	2	{試験内容のJSON}

（省略）

このためには、json_agg関数を用いる。
生徒を軸に、試験テーブルの1レコードごとに集約するために、GROUP BYには生徒ID、試験IDを指定して、試験テーブルのデータを集約する。
そのためのクエリは、単純に上記を落とし込んで、こうなる。

SELECT
    student_id,
    e.exam_id,
    json_agg(e) AS json
FROM student_exams se
    JOIN exams e ON se.exam_id=e.exam_id
GROUP BY student_id,e.exam_id
ORDER BY student_id ASC

すると結果は、このようになる。

student_id	exam_id	json
1	1	{[{"exam_id":1,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":10,"japanese_point":20,"english_point":30}]
2	4	[{"exam_id":4,"name":"期末試験","date":"2017-09-30T00:00:00","math_point":15,"japanese_point":25,"english_point":35}]
3	2	[{"exam_id":2,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":40,"japanese_point":50,"english_point":60}]

（省略）

ちなみに、json_agg関数の引数をカラムではなくテーブルにした場合、 table_name.* と同義になる。
すべてのカラムを出すしかないので、カラムを絞りたい場合は、サブクエリにするかWITH句で追い出しておく必要がある。

ここで、当初の目論見とはことなり、連想配列が配列長1の配列に入ってしまっていることに留意しておく。

生徒ごとに連想配列にした試験データを集約する

次に、生徒ごとに集約するので、今度はGROUP BYに生徒IDのみを指定し、再度json_aggにかけてやればよい。
postgresのjson_aggは内部でjson型を扱えるため、
JSONの中に「JSONの形をしたstring」が入ってしまい、最終的にjson_decodeした結果をjson_decodeする必要がある、
なんてことにならないようなツリー状にまとめてくれる。
そのため、そこのところは考慮せず、単純にさきほどのクエリをサブクエリとして、json_aggをかけてやるだけでよい。

SELECT
    student_id,
    json_agg(jsons.json) AS exam_json
FROM
    (
        --ここから
        SELECT
            student_id,
            e.exam_id,
            json_agg(e) AS json
        FROM student_exams se
            JOIN exams e ON se.exam_id=e.exam_id
        GROUP BY student_id,e.exam_id
        ORDER BY student_id ASC
        --ここまで先程のクエリ
    ) jsons
GROUP BY student_id;

これだけでよい。
そして結果は下記となる。

student_id	exam_json
1	{[[{"exam_id":1,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":10,"japanese_point":20,"english_point":30}], [{"exam_id":4,"exam_name":"期末試験","date":"2017-09-30T00:00:00","math_point":15,"japanese_point":25,"english_point":35}]]
2	[[{"exam_id":2,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":40,"japanese_point":50,"english_point":60}], [{"exam_id":5,"exam_name":"期末試験","date":"2017-09-30T00:00:00","math_point":45,"japanese_point":55,"english_point":65}]]
3	[[{"exam_id":3,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":70,"japanese_point":80,"english_point":90}], [{"exam_id":6,"exam_name":"期末試験","date":"2017-09-30T00:00:00","math_point":75,"japanese_point":85,"english_point":95}]]

生徒3人と試験6回のデータが3レコードとなって収まった。

SQLはここまでで、ここからはビジネスロジックのお話になる。
ここでは、PHPで行うとするが、JSONなので言語には依存しない。

取れたレコードをPHPで扱う

想定仕様は、生徒ごとに開きタグと閉じタグを出力し、生徒ごとのブロックを描画するものである。
取れたレコードを $records に入れたとすると、下記のようなPHPで扱う事ができる。

<?php

(省略)

// 生徒ごとのループ
foreach( $records as $record){
    // 開きタグ描画処理
    
    // jsonから配列に変換（オブジェクトに変換するなら第二引数は省略かfalse）
    $exam_array = json_decode($record['exam_json'], true);

    // 試験ごとのループ
    foreach( $exam_array as $exam_data_array ){
        // ここで、この中身は配列長が1のデータだったことを思い出そう
        // 仕様上そうなってしまうだけなので、先頭要素を取るだけで良い
        $exam_data = current( $exam_data_array );

        // $exam_data['name'] や、
        // $exam_data['date'] で、試験のデータが取れるので処理や描画
    }

    //閉じタグ描画処理
}

以上で、postgresに仕事をさせ、PHPに楽をさせることができた。

例では単純なものだったのでJOINが少なかったが、本来はもっと正規化してあるはずなのでサブクエリがごちゃつきがち。
WITH句やviewに、いかにわかりやすく追い出すかが出来るSQL芸人になれるかどうかのカギである。たぶん。