SQL練習室
← SQLガイド

PostgreSQLの配列とunnestとは?ANY・@>・&&での検索と行への展開

PostgreSQLでは、1つの列に複数の値を配列としてまとめて保存できます。記事に付けるタグのように、数が決まっていない値を別の表に分けずに持てるのが特徴です。配列の作り方、取り出し方、検索の仕方、行への展開(unnest)を、記事とタグの表で順に確認します。配列型はPostgreSQLの拡張機能が中心で、MySQLやSQLiteには配列型の列がありません。

例で使う表

articlesは記事1件が1行で、tagsがtext[](テキストの配列)の列です。表示では配列が{sql,beginner}のように波カッコで囲まれます。下書き(id 5)のtagsは要素が0個の空配列です。ORDER BYのないSQLの行順は保証されません。

articles:記事5件
idtitletags
1SQL入門{sql,beginner}
2JOIN解説{sql,join}
3Python入門{python,beginner}
4ウィンドウ関数{sql,advanced,join}
5下書き{}

配列・unnestの基本構文

sql
SELECT id, title, tags
FROM articles
WHERE tags = ARRAY['sql', 'beginner'];

ARRAY[...]と配列リテラルで配列を書く

配列はARRAY['sql','beginner']のように書きます。文字列で'{sql,beginner}'と書く配列リテラルでも同じ値を表せ、空の配列は'{}'またはARRAY[]::text[]です。配列同士を=で比べると、要素が同じ順序で並んでいるときだけtrueです。tagsが{sql,beginner}の記事1だけが一致し、要素は同じでも順序の違う配列とは一致しません。順序を問わず含むかを調べたいときは、後で扱う@>を使います。

sql
SELECT id, tags
FROM articles
WHERE tags = ARRAY['sql', 'beginner'];
実行結果
idtags
1{sql,beginner}

添字tags[1]で要素を取り出す(1始まり)

配列の要素はtags[1]のように添字で取り出します。PostgreSQLの添字は1から始まり、0ではありません。tags[1]は最初のタグ、tags[2]は2番目のタグです。要素の数より大きい添字を指定してもエラーにはならずNULLが返るので、空配列の下書きはtags[1]もtags[2]もNULLです。

sql
SELECT title, tags[1] AS first_tag, tags[2] AS second_tag
FROM articles
ORDER BY id;
実行結果
titlefirst_tagsecond_tag
SQL入門sqlbeginner
JOIN解説sqljoin
Python入門pythonbeginner
ウィンドウ関数sqladvanced
下書きNULLNULL

= ANY(配列)でタグを1つ指定して検索する

'join' = ANY(tags)は、tagsのどれかの要素がjoinと等しいときにtrueになります。左辺に探したい値、右辺に配列を置く形です。JOIN解説とウィンドウ関数の2件が一致します。1つのタグで絞り込む場面では、この書き方が最も読みやすくなります。逆にANY(tags) = 'join'のように左右を入れ替えることはできません。

sql
SELECT id, title
FROM articles
WHERE 'join' = ANY(tags)
ORDER BY id;
実行結果
idtitle
2JOIN解説
4ウィンドウ関数

@>はすべてを含む、&&はどれかを含む

tags @> ARRAY['sql','join']は、tagsが右の配列の要素をすべて含むときtrueです。tags && ARRAY['sql','join']は、共通する要素が1つでもあればtrueです。記事1はsqlだけを持つので、@>はfalse、&&はtrueになります。JOIN解説とウィンドウ関数は両方を持つので、どちらもtrueです。AND条件には@>、OR条件には&&と覚えると使い分けられます。

sql
SELECT
  id,
  tags @> ARRAY['sql', 'join'] AS has_all,
  tags && ARRAY['sql', 'join'] AS has_any
FROM articles
ORDER BY id;
実行結果
idhas_allhas_any
1falsetrue
2truetrue
3falsefalse
4truetrue
5falsefalse

array_lengthとcardinalityで要素数を数える

array_length(tags, 1)は1次元目の長さを返し、cardinality(tags)は要素の総数を返します。通常の配列では同じ値ですが、空配列では違います。空配列の下書きは、array_lengthがNULL、cardinalityが0です。タグが0個の記事を探したいときは、cardinality(tags) = 0と書けます。array_length(tags, 1) = 0では、NULLとの比較になって1行も取得できません。

sql
SELECT
  title,
  array_length(tags, 1) AS len,
  cardinality(tags) AS card
FROM articles
ORDER BY id;
実行結果
titlelencard
SQL入門22
JOIN解説22
Python入門22
ウィンドウ関数33
下書きNULL0

unnestで配列を行に展開して集計する

unnest(tags)は、配列の要素を1要素1行に展開します。FROMに並べると、記事の行と展開した行が組み合わされ、SQL入門は2行、ウィンドウ関数は3行になります。展開後はGROUP BYで普通の列と同じようにタグごとの記事数を数えられます。空配列の下書きは展開すると0行になり、どのタグにも数えられません。

sql
SELECT tag, COUNT(*) AS n
FROM articles, unnest(tags) AS tag
GROUP BY tag
ORDER BY n DESC, tag;
実行結果
tagn
sql3
beginner2
join2
advanced1
python1

配列・unnestでよくある間違い

空配列やNULLの配列の要素数をarray_lengthで調べる

array_length(tags, 1)は空配列でNULLを返すため、= 0で空配列を探すと1行も一致しません。要素数が0の行を探すときはcardinality(tags) = 0を使います。tags自体がNULLの行はcardinalityもNULLになるので、必要ならIS NULLを別に確認します。

sql
SELECT id, title
FROM articles
WHERE array_length(tags, 1) = 0;

実際に1問解いてみる

ARRAY[...]

tagsがsql、beginnerの順にちょうど2要素で並んでいる記事の全列を取得してください。要素は同じでも順序が違う記事は含めません。

テーブル

読み込み中…

実行結果

関連構文

配列・unnestについてよくある質問

配列の添字はなぜ0ではなく1から始まるのですか?
PostgreSQLの配列は、既定で添字が1から始まります。tags[1]が最初の要素です。範囲外の添字を指定してもエラーにはならずNULLが返るので、0から数えるつもりで書くと意図しないNULLになる点に注意してください。
= ANY(配列)と@>はどう使い分けますか?
探したい値が1つなら、= ANY(配列)が読みやすい書き方です。複数の値を「すべて含む」「どれかを含む」で調べるなら、@>と&&を使います。配列にGINインデックスを使いたい場合も、@>や&&を選びます。
array_lengthとcardinalityの違いは何ですか?
array_lengthは次元を指定して長さを返し、空配列ではNULLになります。cardinalityは全要素の数を返し、空配列では0になります。1次元の配列で要素数を知りたいだけなら、cardinalityのほうが空配列の扱いで迷いません。
配列の要素を1つずつ別の行にするにはどうしますか?
unnest(配列)を使います。FROM articles, unnest(tags) AS tagのように並べると、記事ごとのタグが1行ずつに展開されます。展開した行はGROUP BYで集計でき、array_aggで配列に戻すこともできます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。