PostgreSQL 基礎講座 #7 JSONB — スキーマの柔軟さをリレーショナルの中で

インフラ PostgreSQL データベース
読了 4分

「この部分はスキーマが変わり続けるから、そのために NoSQL を別に持つべきか」という悩みに対する PostgreSQL の答えが JSONB です。リレーショナルテーブルの 1 カラムに JSON ドキュメントを入れ、その中身をインデックスで検索できます。第 1 回で「別システムが必要と言われていたものが内蔵されている」と言った代表例で、DynamoDB vs RDS で扱った選択問題のかなりの部分を PostgreSQL だけで解決できる機能です。

json ではなく jsonb です #

型は 2 つあります。json は入力テキストをそのまま保管し、jsonb はパース済みのバイナリ形式で保存します。実務の既定値は jsonb です。 参照演算がずっと速く、インデックスを作れるからです。json を選ぶ理由は「入力の原文(キーの順序、重複キー、空白)をそのまま保存しなければならない」という特殊な要件だけです。

eventsテーブル作成 
CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    bigint NOT NULL REFERENCES users (id),
    event_type text NOT NULL,
    payload    jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (user_id, event_type, payload) VALUES
    (1, 'purchase', '{"item": "keyboard", "amount_jpy": 12800, "coupon": {"code": "AUG10", "rate": 0.1}}');

イベントログ、外部 API レスポンスの保管、商品ごとにバラバラの属性のように、「行ごとに形が違うデータ」が JSONB の居場所です。

演算子: 取り出すことと尋ねること #

JSONB演算子 
-- 取り出す: -> は jsonb として、->> は text として
SELECT payload -> 'coupon' -> 'code'   FROM events;  -- "AUG10" (jsonb)
SELECT payload ->> 'item'              FROM events;  -- keyboard (text)
SELECT payload #>> '{coupon,code}'     FROM events;  -- AUG10 (パス指定で text)

-- 尋ねる: ? はキーの存在、@> は包含
SELECT * FROM events WHERE payload ? 'coupon';                      -- coupon キーがある行
SELECT * FROM events WHERE payload @> '{"item": "keyboard"}';       -- この構造を含む行

-> と ->> の区別が最初の関門です。-> は結果がまだ jsonb なのでチェーンを続けられ、->> は text として取り出すので、比較・出力の最終段階で使います。payload ->> 'amount_jpy' を数値と比較するには (payload ->> 'amount_jpy')::numeric のようにキャストが必要です。JSON の中には型が緩く入っているので、第 2 回で固めた型の規律が、JSONB の境界ではキャストとして再登場するわけです。

GIN インデックス: JSONB 検索の相棒 #

B-tree は「カラムの値全体」をソートするインデックスなので、ドキュメントの中身は見えません。JSONB 検索の相棒は GIN インデックスです。ドキュメント内のキーと値を転置して索引化し、@>(包含)や ?(存在)の検索を支えてくれます。

GINインデックス作成 
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- これでこの検索がインデックスを使います
SELECT * FROM events WHERE payload @> '{"coupon": {"code": "AUG10"}}';

効果は第 5 回の EXPLAIN ANALYZE で確かめれば十分です(Bitmap Heap Scan の形が正常です)。ただし GIN はドキュメント全体を索引化するので、書き込みコストとサイズがかなりのものです。特定のキー 1 つだけを検索するなら、その式にだけ B-tree を張る式インデックス(ON events ((payload ->> 'item')))のほうがずっと軽いです。GIN 系の応用(オプション、他のインデックスタイプとの比較)は実践編で扱います。

更新はドキュメント単位が基本です #

jsonb_setで更新 
UPDATE events
SET payload = jsonb_set(payload, '{coupon,rate}', '0.15')
WHERE id = 1;

jsonb_set でパス 1 つだけを変える文法はありますが、ストレージ層では第 6 回で見た MVCC の原理どおり、行の新しいバージョンが丸ごと作られます。「フィールド 1 つだけだから安いはず」ではないという意味です。大きなドキュメントを頻繁に部分更新するワークロードなら、よく変わる値をドキュメントの外の普通のカラムに出すのが正しい設計です。

設計境界: 全部 JSONB に入れたくなったら #

JSONB の誘惑は「もう全部 payload に入れよう」です。境界は明確です。結合キー、頻繁に検索・ソートする値、NOT NULL・CHECK・FK で守りたい値は普通のカラムです。 JSONB の中にはこうした制約が届かないので(第 2 回で立てた「整合性は DB 層で」の原則はドキュメントの中では無力です)、構造が安定した中核フィールドはカラムに昇格させ、形が変わり続ける周辺部だけを JSONB に残すのがバランスの取れた答えです。上の events テーブルがその手本です。user_id と event_type はカラム、残りが payload です。

まとめ #

  • 型は jsonb が既定値です。json は入力原文の保存という特殊要件専用です。
  • 取り出しは ->(jsonb)と -»(text)の区別が核心で、比較にはキャストが付いてきます。検索は ?(存在)と @>(包含)です。
  • JSONB 検索の相棒は GIN インデックスです。特定のキー 1 つだけ探すなら式インデックスのほうが軽いです。
  • 部分更新も MVCC 上は行全体の新バージョンです。よく変わる値はドキュメントの外のカラムに出します。
  • 結合キー・検索対象・制約が必要な値はカラム、形が変わる周辺部だけが JSONB です。次回はビュー、CTE、ウィンドウ関数でクエリの表現力を上げます。
X