1:Nのレコードを配列で取得するSQL

ネストしたテーブル設計では、1:Nの構造になるレコードが頻発します。
その場合の素朴なSQLは以下のような記述になるでしょう。

SELECT categories.name, articles.title FROM categories
  JOIN articles ON categories.id = articles.category_id;

多くの場合、カテゴリー1件に対して記事N件となりますが、このSQLでは、同じカテゴリー名が重複するN行のレコードが返ってきます。

集約関数のarray_agg()を用いると、N件のレコードを配列に変換できます。

SELECT categories.name, array_agg(articles.title) AS title FROM categories
  JOIN articles ON categories.id = articles.category_id
  GROUP BY categories.name;

array_agg()を利用するとネスト構造のデータを1クエリで取得でき、n+1クエリを避けられます。

⁋ 2021/07/23↻ 2024/11/07
中馬崇尋
Chuma Takahiro
sqldefのメリットと使い方
sqldefはRDBMSのスキーマをconfigに沿って更新するCLIツールです。 先行実装 ridge……
COPYコマンドの自明ではない挙動
PostgreSQLの COPYコマンドは、テーブルデータをCSVファイルなどの形式でインポート／エク……
ストアド関数の自明ではない挙動
PostgreSQLはリファレンスが非常に充実していますが、サポートする機能が多彩であるため個別のケ……
テーブル・パーティショニングの導入
PostgreSQLのパーティショニングはバージョンアップごとに強化されてきています。 アプリケーショ……
PostgreSQLの配列でバルクインサート
複数行を一括でINSERTする際、配列を利用できます。配列型のカラムではなく、複数行を投入する方法で……