PostgreSQLで配列を扱う

PostgreSQLで配列を扱う

基本がわかったところで、SQLクエリで実際に配列を作る方法を深掘りしていこう。ここからが一番面白いところ！

ARRAY[]コンストラクタをSELECTで使う

ARRAY[]コンストラクタは、SELECTクエリで明示的に配列を作りたいときに超便利。PostgreSQLに「ねぇ、これ配列だよ！」って伝える感じ。

-- 数字の配列を作成
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- 文字列の配列を作成
SELECT ARRAY['月曜日', '火曜日', '水曜日'] AS weekdays;

ARRAY[]が{}構文より優れてるポイント

1. 型を明示できる：

-- ARRAY[]なら型を明示できる
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- {}はちょっと注意が必要
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. 複雑なクエリでも読みやすい：

SELECT
product_name,
ARRAY[category, subcategory, brand] AS product_hierarchy
FROM products;

例：数字の配列を作る

まずは定番から。例えば数字の配列を作りたい場合：

SELECT ARRAY[1, 2, 3, 4, 5] AS my_array;

結果はこうなる：

注目ポイント：PostgreSQLは配列を{}形式で返すよ ― これは配列だよっていうPostgreSQL流の見せ方。ちょっとクセあるけど、すぐ慣れる！

例：文字列の配列を作る

数字じゃなくて文字列が欲しいなら、クォートを付けるだけ：

SELECT ARRAY['りんご', 'バナナ', 'オレンジ'] AS fruits;

結果：

ちなみに、PostgreSQLはこういうの大好き。キリル文字でも他のアルファベットでも、配列はちゃんと動くから安心して！

例：他の型（例えば日付）のデータで配列を作る

じゃあ、日付の配列を作りたいときは？めっちゃ簡単：

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS important_dates;

結果：

::DATEに注目。PostgreSQLに「これはDATE型だよ」って明示してる。これがないと、ただの文字列として扱われちゃうから、日付として使いたいときは型を指定しよう。

array_agg()でデータを配列に集約する

さて、ここからがちょっと高度で面白いところ。もし既にテーブルにデータがあって、それを配列にまとめたいときは？そんなときはarray_agg()関数の出番！

一番強力な使い方の一つが、複数行をarray_agg()で配列に変換すること。

基本の使い方：

-- テスト用テーブルを作成
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, 'アンナ'), (1, 'オットー'), (1, 'マリア'),
(2, 'アレックス'), (2, 'キラ'),
(3, 'エレナ');

-- グループごとに学生をまとめる
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

配列内の要素をソートする：

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS students_sorted
FROM students
GROUP BY group_id;

集約時にフィルタリング：

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE 'ア%') AS students_a
FROM students
GROUP BY group_id;

実践的な使い方の例

配列は日常のいろんな場面で役立つよ：タグやアクセス権限の保存、ユーザーの1日分のアクション集計など。下の例で、PostgreSQLで配列をどう使うかイメージを掴もう！

例1：ブログのタグシステム

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- いろんな構文で挿入
INSERT INTO blog_posts (title, content, tags) VALUES
    ('PostgreSQLを学ぼう', '記事の内容...',
     ARRAY['PostgreSQL', 'SQL', 'データベース']),
    ('2024年のWeb開発', '記事の内容...',
     '{"JavaScript", "React", "Node.js"}'),
    ('機械学習', '記事の内容...',
     ARRAY['ML', 'Python', 'Data Science']);

-- タグで記事を検索
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

例2：ユーザー権限システム

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
permissions TEXT[]
);

INSERT INTO users (username, permissions) VALUES
('admin', ARRAY['read', 'write', 'delete', 'manage_users']),
('editor', ARRAY['read', 'write']),
('viewer', ARRAY['read']);

-- システム内の全ユニーク権限を集約
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

例3：ユーザーアクション履歴

CREATE TABLE user_actions (
user_id INTEGER,
action TEXT,
action_date DATE
);

INSERT INTO user_actions VALUES
(1, 'ログイン', '2024-01-01'),
(1, 'プロフィール閲覧', '2024-01-01'),
(1, '設定編集', '2024-01-01'),
(2, 'ログイン', '2024-01-01'),
(2, 'ログアウト', '2024-01-01');

-- ユーザーごと・日ごとにアクションをまとめる
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS daily_actions
FROM user_actions
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. 配列を使ったクエリ：抽出とフィルタリング

配列ができたら、それを取り出したり分析したりしたいよね。普通にSELECTで配列を取得できる：

SELECT tags FROM articles WHERE id = 1;

これで返ってくるのは：

でも、例えばPostgreSQLというタグが付いてる記事を探したいときはどうする？これは次の講義で詳しくやるけど、基本の考え方はシンプル：配列があれば、中身を柔軟に検索できるってこと！