今回は1つのカラムに複数の値を入れられるPostgreSQLのARRAY型を（試しに）使ってみます。
1つの列に複数の値を入れたい。。。
ブログ記事には複数のタグを付けることができます。そして、タグもまた複数の記事に紐付きます。
この関係は多対多で、RDBMSで表す際は中間テーブルを用いるのが定石だったりします。
しかし、アプリケーション側ではタグの一覧をJSONで返すなど、しばしば「こうだったら良いのにな？」があります。
ただし、物理テーブルをこのように正規化を無視して設計するのは柔軟性やスケーラビリティの面からよろしくないのは言うまでもありません。
そこで、今回はアプリケーションから参照するためだけのVIEWとして、実現してみようと思います。
PostgreSQLの配列型
1つのカラムに複数の値をいれるために、PostgreSQLではARRAY型を提供しています。
シンプルに、次のように使えます。
SELECT ARRAY[1, 2, 3]; SELECT ARRAY['programming', 'computer science'];
では、これを使ってVIEWを作ってみましょう。
VIEWで使ってみる
まずは、正規化済みのテーブルを作成しましょう。ER図は先ほども表示しましたが、以下のような作りになります。
それでは、DDLを書いていきましょう。
もし、コードを動かしたい場合はdb-fiddleを使ってみてください。
DB Fiddle - SQL Database Playground
まずは、articleとtagそして両者の中間テーブルであるarticle_tagを作ります。
CREATE TABLE article ( id SERIAL PRIMARY KEY, title TEXT ); CREATE TABLE tag ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE article_tag ( article_id INTEGER NOT NULL, tag_id INTEGER, PRIMARY KEY (article_id, tag_id) );
では、VIEWを書いていきます。
今回は、article_summaryという名前にします。
CREATE VIEW article_summary AS SELECT article.id as article_id, article.title, array_agg(tag.name) as tags FROM article_tag INNER JOIN article ON article_tag.article_id = article.id INNER JOIN tag ON article_tag.tag_id = tag.id GROUP BY article.id;
上記のように、array_agg(tag.name)を使えば、複数のレコードの値を集約する形でARRAY型にできます。
では、早速データを投入してみましょう。
INSERT INTO article (title) VALUES ('anonymous functions'), ('golang'),('sql'); INSERT INTO tag (name) VALUES ('programming'),('database'),('computer science'),('system development'); INSERT INTO article_tag (article_id, tag_id) VALUES (1, 1), (1, 3), (2, 1), (3,1), (3, 2), (3, 3);
先ほど作成したarticle_summaryからデータを取得してみます。
SELECT * FROM article_summary;
以下のように取得できます。
| article_id | title | tags |
|---|---|---|
| 1 | anonymous functions | programming,computer science |
| 2 | golang | programming |
| 3 | sql | programming,database,computer science |
まさに、はじめからアプリケーションが欲しい形でデータが取得できました。
欲張りすぎてはいけない
ここで、「programmingとcomputer scienceを含むレコードを抽出したい！」と思ったら、以下を使いたくなるかもしれません。
SELECT * FROM article_summary WHERE tags @> ARRAY['programming', 'computer science'];
※@>は左辺（tags）に右辺の配列（ARRAY['programming', 'computer science']）の要素が含まれるか（包含）を判定する演算子。
しかしこれは、やはり効率の問題でNG、、、やはり、以下のコードにすべきです。
SELECT * FROM article_tag INNER JOIN article ON article_tag.article_id = article.id INNER JOIN tag ON article_tag.tag_id = tag.id WHERE tag.name = 'programming' OR tag.name = 'computer science' ;
PostgreSQLのドキュメントにも、配列の要素をそれぞれレコードとした方が検索も便利なうえ、レコード数が多くなっても対応できるというような記述がありました。
Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.
PostgreSQL: Documentation: 14: 8.15. Arrays
ただし、VIEW＋ARRAYのパターンも便利なのは事実。
規模や用途などを考慮した上で、使える場面では活用を検討したいですね。
