配列でJOINする in SQL

まえがき

『group∋item』のような関係があったとき皆さんはそれをどう表現しますか？
一般的にはitemテーブルにgroupへの外部キーを張ることで表現すると思います。
一方で、最近のSQLには配列があるので逆にgroupにitem_idの配列を持たせるという方法もあります。^[1]
配列で持たせることで順序の管理がしやすくなったり、同じitemを複数のgroupで共有できたりといった利点があります。
しかし、groupにitem_idの配列を持たせた場合、groupに紐づけてitemを取得するにはどのように書けばよいかがあまり知られていないのではないかと思います。
そこで、この記事ではその場合にどうやってgroupと一緒にitemの配列を取得するかを紹介します。

この記事は『PostgreSQLで配列のidとjoinして並び替えるのが難しい』を参考にしています。

動作確認はPostgreSQL 15.3で行っています。

テーブル定義

まず以下のようにテーブルを定義します。

CREATE TABLE items (
    item_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE groups (
    group_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    item_ids INTEGER ARRAY
);

INTEGER GENERATED BY DEFAULT AS IDENTITY は標準SQLにおける連番の書き方です。
参考：『Identity columns』

データ投入

INSERT INTO
    items (name)
VALUES
    ('item_1'),
    ('item_2'),
    ('item_3'),

INSERT INTO
    groups (name, item_ids)
VALUES
    ('group_1', ARRAY[1, 2]),
    ('group_2', ARRAY[3, 2]);

このgroupsのitem_idsで紐づけて以下のようにitemsテーブルのデータを取得する方法を紹介するというのがこの記事の内容になります。

group_id	name	items
1	group_1	[{ item_id: 1, name: "item_1" }, { item_id: 2, name: "item_2" }]
2	group_2	[{ item_id: 3, name: "item_3" }, { item_id: 2, name: "item_2" }]

サブクエリを使う書き方

groupsのitem_idsで紐づけてitemsテーブルのデータを取得するには、サブクエリを使って以下のように書くことができます。

SELECT
    groups.group_id,
    groups.name,
    (
        SELECT
            json_agg(items)
        FROM
            items
        WHERE
            items.item_id = any(groups.item_ids)
    ) AS items
FROM
    groups;

items.item_id = any(groups.item_ids) は等号が成り立つ要素がgroups.item_idsに存在するときTRUEとなります。^[2]
json_aggは入力 (items) をJSONの配列として格納します。^[3]

ただし、この書き方では配列内の順序が維持される保証がありません。
つまり groups.item_ids と異なる順番でitemsの中身が並ぶ可能性があります。
配列の順序を維持させようとすると以下のようなクエリになります。

SELECT
    groups.group_id,
    groups.name,
    (
        SELECT
            json_agg(items ORDER BY item_ids.i)
        FROM
            unnest(groups.item_ids) WITH ORDINALITY AS item_ids(item_id, i)
        JOIN items USING (item_id)
    ) AS items
FROM
    groups;

順を追って上のコードを説明していきましょう。

まず unnest(groups.item_ids) WITH ORDINALITY AS item_ids(item_id, i) から説明します。
unnest(groups.item_ids) で配列item_idsをテーブル (より正確には行の集合) に変換しています。^[4]
WITH ORDINALITY でテーブルの列として配列のインデックス (1始まり) を加えています。^[5]
つまり、groups.item_idsの中身とインデックスを列として持つテーブルが作成されています。
AS item_ids(item_id, i) で上記のテーブルのエイリアスを指定しています。テーブルにitem_ids、groups.item_idsの中身にitem_id、配列のインデックスにi というエイリアスを指定しています。

次に JOIN items USING (item_id) でitemsをJOINして、json_agg(items ORDER BY item_ids.i) AS items で集約しています。
ORDER BY item_ids.i でitem_idsのインデックスと同じ順序で配列の要素が並ぶようにしています。[^4]

これでやりたかったクエリを作成することができました。

他の方法

配列の順序を問わないなら、以下のようにGROUP BYで書くこともできます。

SELECT
    groups.group_id,
    groups.name,
    json_agg(items) AS items
FROM
    groups
JOIN
    items ON items.item_id = ANY(groups.item_ids)
GROUP BY
    groups.group_id;

配列の順序を維持する場合、前のセクションでサブクエリを使った場合と異なる場所で配列を作成することもできます。

SELECT
    groups.group_id,
    groups.name,
    item_arrays.item_array AS items
FROM
    groups
LEFT JOIN LATERAL (
    SELECT
        json_agg(items ORDER BY item_ids.i) AS item_array
    FROM
        unnest(groups.item_ids) WITH ORDINALITY AS item_ids(item_id, i)
    JOIN items USING (item_id)
) item_arrays ON true
ORDER BY
    groups.group_id;

JOINする集合が unnest(groups.item_ids)の箇所でgroupsを参照しています。
このようにJOINする集合が前のFROM句のテーブルに依存しているときにLATERALを指定する必要があるのでLEFT JOIN LATERAL と書いています。^[6]

おわりに

配列で紐づけてJOINする方法を調べてみたところ、必要な知識が多く調べるのが大変だったので記事にしました。
この記事がこれから学習する人のショートカットになれば幸いです。

脚注

1. itemテーブルに対して検索する予定がないなら、そもそもitemのテーブルを作成せず、groupはitemを複合型の配列として直接持つという方法もあります。
   可能ならitemテーブルを作らないほうがシンプルなんじゃないかと最近思います。

2. https://www.postgresql.jp/document/15/html/functions-comparisons.html#id-1.5.8.32.20

3. https://www.postgresql.jp/document/15/html/functions-aggregate.html

4. https://www.postgresql.jp/document/15/html/functions-array.html

5. https://www.postgresql.jp/document/15/html/functions-srf.html

6. https://www.postgresql.jp/document/15/html/queries-table-expressions.html#QUERIES-LATERAL