PostgreSQLのARRAY_AGGでユニークな配列を取得する

  • PostgreSQL
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

ARRAY_AGGにDISTINCTを付けると、集約結果を重複なしの配列として取得できる。よく使う書き方なのでメモしておく。

使い方

SELECT
    parent.id,
    parent.created_at,
    ARRAY_AGG(DISTINCT child.value) AS values
FROM
    parent
JOIN
    child ON parent.id = child.parent_id
GROUP BY
    parent.id, parent.created_at;

parentとchildをJOINすると、親レコード1件に対して子レコードが複数行紐づく。単純にARRAY_AGG(child.value)とすると、子レコード側の値が重複していればそのまま配列に入ってしまう。DISTINCTを挟むことで、集約前に重複を除いてから配列化できる。

順序を揃えたい場合はORDER BYが必要

ARRAY_AGG(DISTINCT ...)は要素の順序を保証しない。配列内の並び順も揃えたい場合はORDER BYを一緒に書く。

ARRAY_AGG(DISTINCT child.value ORDER BY child.value)

これを書かないと、実行するたびに配列内の並びが変わる可能性がある。

GROUP BYについて

GROUP BYには「SELECTに出す非集約カラムは全部書く」のがPostgreSQLのルール。今回parent.idとparent.created_atは両方SELECTに出しているので、両方ともGROUP BYに必要になる。

ただしPostgreSQLには例外があって、parent.idが主キーならparent.created_atは省略できる。

-- parent.idが主キーならこれでも動く
GROUP BY parent.id

主キーに関数従属するカラム(同じテーブルの他のカラム)は、主キーさえGROUP BYに入っていれば自動的に一意に定まるとPostgreSQLが判断してくれるため。

ただしこの省略ルールはPostgreSQL固有の挙動で、MySQLの古いバージョンなど他のDBでは同じ書き方がエラーになることがある。動くかどうかを主キー判定に頼らず、SELECTに出すカラムは律儀に全部GROUP BYに書いておいた方が、DB移植や他の人が読むときに事故りにくい。