【PostgreSQL】JSONB型とGINインデックスで半構造化データを扱う

PostgreSQL
2022.12.23

こんにちは、かつコーチです。

前回は配列型・範囲型といったPostgreSQL固有のデータ型を紹介しました。

今回はPostgreSQLの中でもとりわけ活用の幅が広いJSONB型と、それを高速に検索するためのGINインデックスを解説します。

「柔軟にJSONを保存しつつ、リレーショナルデータベースの堅牢さも欲しい」というニーズに応えられるのが、JSONB型の強みです。

JSONB型とは

JSON型との違い

PostgreSQLにはJSONを格納する型として、JSON型とJSONB型の2種類があります。

項目JSON型JSONB型
保存形式テキストのまま保存バイナリ形式に変換して保存
書き込み速度やや速い（変換不要）やや遅い（変換コストがかかる）
読み込み・検索速度遅い（毎回パースが必要）速い（パース済みの形で保存）
インデックス使えないGINインデックスが使える
キーの順序保持される保持されない（重複キーは後勝ち）

結論から言うと、特別な理由がない限りJSONB型を使うのが基本です。

書き込み時に元のJSON文字列をそのまま保存したいという稀なケースを除いて、検索性能とインデックスが使える点でJSONB型が圧倒的に有利だからです。

テーブル定義とデータの登録

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
('ワイヤレスイヤホン', '{"color": "black", "battery_hours": 8, "waterproof": true}'),
('スマートウォッチ', '{"color": "silver", "battery_hours": 24, "waterproof": false}');

商品ごとに属性の項目数が異なるようなケースでも、attributesカラム1つで柔軟に対応できます。

すべての商品属性を個別のカラムとして事前定義する必要がないのが、JSONB型を使う大きなメリットです。

JSONBを検索する

基本の演算子

JSONB型には専用の演算子が用意されています。

-- ->  : JSONBのまま値を取得（結果もJSONB）
SELECT name, attributes -> 'color' AS color FROM products;

-- ->> : テキスト型に変換して値を取得
SELECT name, attributes ->> 'color' AS color FROM products;

-- 条件検索：colorがblackの商品を取得
SELECT name FROM products WHERE attributes ->> 'color' = 'black';

-- 数値として比較する場合はキャストが必要
SELECT name FROM products WHERE (attributes ->> 'battery_hours')::INT >= 10;

->と->>の違いをよく混同しがちですが、「結果をJSONBのまま使うか、テキストとして使うか」の違いだと覚えておくと迷いません。

WHERE句で値を比較する場合は、基本的に->>でテキスト変換してから比較します。

包含演算子で複雑な条件を検索する

-- @>演算子：JSONBが指定した構造を含んでいるかを判定
SELECT name FROM products
WHERE attributes @> '{"color": "black", "waterproof": true}';

@>演算子を使うと、複数の条件を1つのJSONBオブジェクトとしてまとめて渡せます。

条件が増えてもANDを積み重ねずに書けるため、動的にフィルタ条件を組み立てるアプリケーションのバックエンドと相性が良い書き方です。

GINインデックスでJSONB検索を高速化する

インデックスがない場合の問題

JSONB型のカラムに対する検索は、インデックスがない状態だと全行をスキャンして中身を確認する必要があり、データ件数が増えるほど遅くなります。

私が実際に、属性データを10万件のJSONBで検証したところ、インデックスなしでの@>検索には数百ミリ秒かかっていました。

GINインデックスの作成

❌ Before：インデックスなしでJSONB検索を行う

-- インデックスがない状態でattributesを検索すると全行スキャンになる
SELECT name FROM products WHERE attributes @> '{"waterproof": true}';

✅ After：GINインデックスを作成してから検索する

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 同じクエリでもGINインデックスが使われ、高速に絞り込める
SELECT name FROM products WHERE attributes @> '{"waterproof": true}';

GIN（Generalized Inverted Index：汎用転置インデックス）は、JSONBや配列型のように「1つの値の中に複数の要素が含まれるデータ」の検索に特化したインデックスです。

先ほどの検証環境でGINインデックスを作成した後は、同じ検索が数ミリ秒まで短縮されました。

@>演算子や、後述する存在演算子（?）を使った検索は、GINインデックスが有効に働くパターンです。

特定のキーだけを高速化したい場合

商品属性の中でも「colorだけをよく検索する」というように、特定のキーに絞って高速化したい場合は、式インデックスを使う方法もあります。

-- colorキーだけを対象にしたB-treeインデックス
CREATE INDEX idx_products_color ON products ((attributes ->> 'color'));

SELECT name FROM products WHERE attributes ->> 'color' = 'black';

検索パターンが特定のキーに偏っている場合は、GINインデックスよりもこちらの方が軽量かつ高速になることがあります。

つまずきやすいポイント：キーの存在チェック

NULLとキー未存在を混同する

❌ Before：->>で取得してNULLかどうかを判定しようとする

-- waterproofキー自体が存在しない場合も、値がJSON null（'null'）の場合も
-- どちらもSQL上のNULLとして扱われ、区別がつかない
SELECT name FROM products WHERE attributes ->> 'waterproof' IS NULL;

私がこの書き方をしたとき、「キーが存在しない商品」と「waterproofの値をJSONのnullとして明示的に登録した商品」の両方がヒットしてしまい、意図した結果と違う件数が返ってきて原因調査に時間を使ったことがあります。

✅ After：?演算子でキーの存在自体を判定する

-- ?演算子：指定したキーがトップレベルに存在するかどうかを判定
SELECT name FROM products WHERE NOT (attributes ? 'waterproof');

?演算子はキーの「存在有無」だけを厳密に判定するため、値がJSON nullかどうかとは切り離して確認できます。

「値がNULLかどうか」と「キーが存在するかどうか」は別の概念だと意識しておくと、この手のバグを未然に防げます。

まとめ

この記事のポイント

  • 特別な理由がなければJSON型ではなくJSONB型を使う（インデックスが使え、検索が速い）
  • ->はJSONBのまま、->>はテキストとして値を取得する
  • @>演算子で複数条件をまとめて包含検索できる
  • GINインデックスを作成することで、JSONB検索を大幅に高速化できる
  • キーの存在チェックには?演算子を使い、NULL判定と混同しないようにする

次に読むべき記事

JSONB型の基本を押さえたところで、次はさらに発展的な、AI活用と親和性の高いデータ型を扱います。

次回はpgvectorでベクトル検索・類似検索を試す方法を解説します。

→ 次の記事：pgvectorでベクトル検索・類似検索を試す

タグ: PostgreSQL, 中級者向け, データ型

タイトルとURLをコピーしました