1対多の結合をしてもJSON関数を使って行数を増やさずSELECTする

1対多の結合をしても1の方でマッチする行数のみ取得する

SQLで1対多の結合をすると行数が多の方に合わせて取得できるが、プログラム上では1の方のテーブルを基準に処理をしたい場合にJSON関数(JSON_OBJECT, JSON_ARRAYAGG)を使って1の方のテーブルを基準にしたデータを取得する方法を書く。

version: MySQL 8

JSON_OBJECT

JSON_OBJECT([key, val[, key, val] ...])を使うとkey valueでJSONを作成できる。

SELECT json_object(
            "id", id,
            "name", name) x
FROM orders; 

JSON_ARRAYAGG

JSON_ARRAYAGG(col_or_expr)を使うと複数のJSONをarrayでまとめられる。先ほどJSON_OBJECTで取得した結果を一つのJSONにまとめてみる。

SELECT json_arrayagg(json_object(
            "id", id,
            "name", name)) x
FROM orders; 

結合の代わりにSELECT句にサブクエリを書く

JSON_OBJECT, JSON_ARRAYAGGを使うと複数のデータを一つのJSON arrayで取得できるので、結合の代わりにSELECT句でサブクエリを書くことで、1対多の結合をしても1の方のデータ基準で取得することを実現できる。

通常の結合パターン

注文と注文明細テーブルがあるとする。2名から1件ずつ2件注文がきたが、user_id u1が一度に2つの商品を注文しているとする。

SELECT order_id, user_id FROM orders;

SELECT order_id, order_detail_id, item_id, price FROM order_details;

これを結合すると、注文明細テーブルの方の行数に膨れてしまう。

SELECT
    o.order_id,
    o.user_id,
    d.item_id,
    d.price
FROM orders o
JOIN order_details d
USING (order_id);

サブクエリ

結合の代わりにJSON関数とサブクエリを使って書き直すと以下のようになり、注文テーブル基準のデータが取得できる。

SELECT
    o.order_id,
    o.user_id,
    (select json_arrayagg(json_object(
            "item_id": d.item_id,
            "price", d.price))
         from order_details d
         where d.order_id = o.order_id) details
FROM orders o;

件数が多い時はSELECT句のサブクエリではなくインラインビューと結合する

WHERE句で十分対象が絞れてSELECTする件数が少ない時はサブクエリが高速だが、SELECT件数が多いとその分サブクエリの実行回数が増えるためパフォーマンスが悪化する。

(今回の「注文 : 注文明細」の例は不適切だが)多の方のテーブルの件数があまり多くない場合や多の方のテーブルをWHERE句である程度絞れる場合、インラインビューを採用することでパフォーマンスを上げることができるが、その時もGROUP BYと組み合わせることでJSON_OBJECT, JSON_ARRAYAGGを使える。

SELECT
    o.order_id,
    o.user_id,
    dx.details
FROM orders o
LEFT OUTER JOIN
    (select
        order_id,
        json_arrayagg(json_object(
            "item_id": item_id,
            "price", price)) details
    from order_details
    group by order_id) dx
ON o.order_id = dx.order_id;

GROUP_CONCAT

ちなみにMySQLのバージョンが古くてJSON関数が使えない場合は、GROUP_CONCATで代用するといい。多の方のデータをカンマ(あるいは指定したセパレーター)で区切られた文字列として取得できる。