PostgreSQL全文検索（Full Text Search）超入門！JSONB・CTE・ウィンドウ関数も解説

PostgreSQLのFULL TEXT SEARCHとは？全文検索の仕組みを整理

先生と生徒の会話形式で理解しよう

生徒

「先生、データベースの中から特定の『言葉』を検索したいときはどうすればいいんですか？例えば、長い文章の中からキーワードを探したいんです。」

先生

「それは『全文検索（Full Text Search）』という機能がぴったりですね。PostgreSQLには、本をパラパラめくるのではなく、索引（インデックス）を使って一瞬で目的のページを開くような、賢い検索の仕組みがあるんですよ。」

生徒

「普通の検索とは違うんですか？あと、最近よく聞くJSONBやCTE、ウィンドウ関数というのも難しそうで不安です……。」

先生

「心配いりません。一つずつ、身近なものに例えて説明しますね。まずは、大量のテキストから言葉を見つけ出す『全文検索』の魔法から見ていきましょう！」

1. PostgreSQLの全文検索（Full Text Search）とは？

データベース（データの保管箱）に保存された大量の文章の中から、特定の単語やフレーズを高速に見つけ出す機能のことを全文検索（Full Text Search）と呼びます。パソコンを触ったことがない方でも、「辞書」をイメージすると分かりやすいでしょう。

通常の検索（LIKE検索といいます）は、名簿の端から端まで一行ずつ目で追って「ここにあるかな？」と探す方法です。これを「線形探索」と言い、データが数万件、数十万件と増えると、検索が終わるまでに非常に時間がかかってしまいます。

対して、PostgreSQLの全文検索は「索引（インデックス）」という仕組みを使います。本の巻末にある索引のように、「どの単語が、どのページ（行）に書かれているか」というリストをあらかじめ作成しておきます。これにより、何百万という文章の中からでも、一瞬でキーワードを見つけ出すことができるのです。

全文検索の仕組み：tsvectorとtsquery

PostgreSQLで全文検索を行うには、主に2つの特別なデータ形式を使います。

tsvector：検索対象の文章を「単語」に分解して、検索しやすい形に加工したデータです。
tsquery：私たちが探したい「検索キーワード」を、システムが理解できる形にしたものです。

例えば、「猫が庭で遊ぶ」という文章がある場合、システムはこれを「猫」「庭」「遊ぶ」という単語にバラバラにして整理します。これが全文検索の第一歩です。

実際に全文検索を試してみよう

まずは、ブログの記事を管理する「articles」というテーブル（表）があると考えてみましょう。


id | title            | content
---+------------------+------------------------------------------
1  | 猫の飼い方       | 自宅の庭で猫と一緒に遊ぶ方法を解説します。
2  | 庭園の手入れ     | 素敵な庭を作るためのガーデニング初心者ガイド。
3  | 料理レシピ       | 美味しいカレーを作るための秘訣とは？
4  | 週末の過ごし方   | 庭で読書をしたり、猫と昼寝をしたりします。
5  | 最新家電紹介     | お掃除ロボットを使えば家事が楽になります。

この中から「庭」という言葉が含まれる記事を、全文検索の機能を使って探してみます。


SELECT id, title
FROM articles
WHERE to_tsvector('japanese', content) @@ to_tsquery('japanese', '庭');

この命令を実行すると、次のような結果が得られます。


id | title
---+------------------
1  | 猫の飼い方
2  | 庭園の手入れ
4  | 週末の過ごし方

このように、文章の中に「庭」という漢字が含まれている行だけを瞬時に抜き出すことができました。

エンジニアの必須スキル「SQL」を、図解と豊富な練習問題でゼロから体系的に学びたい人へ。 MySQLやPostgreSQLなど、各種データベースに対応した不朽の入門書です。

SQL 第2版ゼロからはじめるデータベース操作をAmazonで見る

※ Amazon広告リンク

2. JSONB：複雑なデータを柔軟に扱う魔法

次に、JSONBという機能について解説します。通常、データベースの表は「名前」「年齢」のように、入れる項目がカチッと決まっています。しかし、商品によって「色」があったり「サイズ」があったり、項目がバラバラなデータを扱いたいときがあります。

そんな時に便利なのがJSONBです。これは「付箋（ふせん）」をたくさん貼れる大きなノートのようなものです。一つの枠の中に、複数の情報を自由な形式で詰め込むことができます。

JSONBを使ったデータ操作

例えば、家電製品のスペックを保存するテーブルを考えてみましょう。


id | product_name | details (JSONB型)
---+--------------+----------------------------------------------
1  | 冷蔵庫       | {"color": "white", "capacity": "500L"}
2  | 掃除機       | {"color": "red", "weight": "2kg", "type": "cordless"}
3  | 電子レンジ   | {"color": "black", "output": "1000W"}

ここで、「色が赤色の商品」だけを探したいときは、次のようなSQLを書きます。


SELECT product_name
FROM products
WHERE details ->> 'color' = 'red';

実行結果：


product_name
--------------
掃除機

3. CTE（共通テーブル式）：複雑な命令をスッキリ整理

CTEとは「Common Table Expression」の略で、日本語では「共通テーブル式」と呼びます。これは、難しい計算や複雑な検索をするときに、「一時的なメモ用紙」を作る機能です。

プログラミング未経験の方には、「料理の下ごしらえ」に例えると分かりやすいでしょう。いきなり完成品を作るのではなく、まず「野菜を切っておく（CTE）」「肉を味付けしておく（CTE）」という準備をして、最後にそれらを組み合わせて炒め物を作るイメージです。

「WITH」という言葉を使って、一時的な表に名前をつけます。


WITH temp_sales AS (
    SELECT product_id, SUM(amount) as total_price
    FROM sales
    GROUP BY product_id
)
SELECT p.product_name, s.total_price
FROM products p
JOIN temp_sales s ON p.id = s.product_id;

このように書くことで、プログラムが非常に読みやすくなり、後で見返したときも何をしているかがすぐに分かります。

4. ウィンドウ関数：データの「流れ」を分析する

最後に紹介するのがウィンドウ関数です。これは、全体のデータを見渡しながら、各行に対して「順位」をつけたり「累計金額」を計算したりする非常に強力な機能です。

例えば、テストの点数一覧があったとき、「クラスの中での順位」を知りたいですよね？普通に合計を出すだけでは順位は分かりませんが、ウィンドウ関数を使えば、他の人の点数と比較して「あなたは何位ですよ」という情報を一瞬で追加できます。

順位をつけてみよう

テスト結果のテーブルを例に見てみましょう。


id | student_name | score
---+--------------+-------
1  | 田中         | 85
2  | 佐藤         | 92
3  | 鈴木         | 78
4  | 高橋         | 92
5  | 伊藤         | 88

このデータに、点数が高い順に順位をつけてみます。


SELECT student_name, score,
       RANK() OVER (ORDER BY score DESC) as ranking
FROM test_results;

実行後の結果：


student_name | score | ranking
-------------+-------+---------
佐藤         | 92    | 1
高橋         | 92    | 1
伊藤         | 88    | 3
田中         | 85    | 4
鈴木         | 78    | 5

「OVER」という言葉を使うのがウィンドウ関数の特徴です。これにより、データをバラバラにすることなく、全体の流れを把握しながら計算を行うことができます。

5. 全文検索と高度な機能を組み合わせるメリット

これまで紹介した「全文検索」「JSONB」「CTE」「ウィンドウ関数」は、組み合わせて使うことでさらにパワーを発揮します。

例えば、巨大なニュースサイトのデータベースを想像してください。

全文検索を使って、膨大な記事の中から「最新技術」という言葉が含まれる記事を抽出します。
JSONBで保存されていた「読者の反応（いいね数やコメント）」を取り出します。
CTEを使って、それらのデータを一時的なリストにまとめます。
ウィンドウ関数を使って、そのリストの中から「いいね数が多い順」にランキングを作成します。

これらすべての操作を、PostgreSQLという一つのシステムだけで完結させることができるのです。これは、情報の整理整頓が非常に得意な「スーパー事務員さん」を雇っているようなものです。

なぜPostgreSQLが選ばれるのか？

データベースには色々な種類がありますが、PostgreSQL（ポストグレスキューエル）は特に「多機能」で「正確」なことで知られています。初心者の方にとっては、最初は覚えることが多いと感じるかもしれませんが、一度身につけてしまえば、どんなに大量のデータが目の前にあっても、自由自在に操ることができるようになります。

パソコンの操作に慣れていない方でも、まずは「表（テーブル）」があって、そこに「命令（SQL）」を出す、という基本イメージを大切にしてください。全文検索も、JSONBも、すべてはその延長線上にある便利な道具に過ぎません。