PostgreSQL JSONB実践ガイド — 検索・更新・インデックス

PostgreSQL JSONB実践ガイド — 検索・更新・インデックス
目次

PostgreSQLのJSONB型は、スキーマレスなデータをリレーショナルDBで扱える強力な機能です。本記事ではJSONBの実践的な操作パターンをまとめます。

JSON vs JSONB

項目JSONJSONB
格納形式テキストバイナリ
書込み速度速いやや遅い
読込み速度遅い速い
インデックス不可GIN対応
重複キー保持最後の値を保持
空白・順序保持正規化

基本的に JSONB を推奨 します。読み取りが高速でインデックスも使えます。

テーブル設計

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO products (name, attributes) VALUES
('MacBook Pro', '{"brand": "Apple", "cpu": "M5 Pro", "ram": 36, "storage": "1TB", "tags": ["laptop", "apple", "pro"]}'),
('ThinkPad X1', '{"brand": "Lenovo", "cpu": "Ultra 9", "ram": 32, "storage": "512GB", "tags": ["laptop", "business"]}'),
('Mac mini', '{"brand": "Apple", "cpu": "M5", "ram": 16, "storage": "256GB", "tags": ["desktop", "apple", "compact"]}');

基本操作

値の取得

-- テキストとして取得 (->>)
SELECT name, attributes->>'brand' AS brand FROM products;

-- JSONとして取得 (->)
SELECT name, attributes->'tags' AS tags FROM products;

-- ネストした値の取得 (#>>)
SELECT attributes#>>'{tags,0}' AS first_tag FROM products;

存在チェック

-- キーの存在確認 (?)
SELECT name FROM products WHERE attributes ? 'cpu';

-- 値を含むか (@>)
SELECT name FROM products WHERE attributes @> '{"brand": "Apple"}';

-- 配列に値が含まれるか
SELECT name FROM products WHERE attributes->'tags' ? 'pro';

検索パターン

数値条件での検索

-- RAM 32GB以上の製品
SELECT name, attributes->>'ram' AS ram
FROM products
WHERE (attributes->>'ram')::int >= 32;

部分一致検索

-- brandに"App"を含む（LIKE相当）
SELECT name FROM products
WHERE attributes->>'brand' ILIKE '%app%';

配列要素での検索

-- tagsに"apple"を含む製品
SELECT name FROM products
WHERE attributes @> '{"tags": ["apple"]}';

-- 配列の長さで絞り込み
SELECT name FROM products
WHERE jsonb_array_length(attributes->'tags') >= 3;

更新操作

値の追加・更新

-- キーの追加/更新 (||)
UPDATE products
SET attributes = attributes || '{"color": "silver"}'
WHERE name = 'MacBook Pro';

-- ネストした値の更新 (jsonb_set)
UPDATE products
SET attributes = jsonb_set(attributes, '{ram}', '64')
WHERE name = 'MacBook Pro';

値の削除

-- キーの削除 (-)
UPDATE products
SET attributes = attributes - 'color'
WHERE name = 'MacBook Pro';

-- 配列から要素を削除
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{tags}',
    (attributes->'tags') - 'compact'
)
WHERE name = 'Mac mini';

集計

JSONBの値でグループ化

-- ブランド別の製品数
SELECT attributes->>'brand' AS brand, COUNT(*)
FROM products
GROUP BY attributes->>'brand';

配列要素の展開と集計

-- タグの出現回数
SELECT tag, COUNT(*) as count
FROM products, jsonb_array_elements_text(attributes->'tags') AS tag
GROUP BY tag
ORDER BY count DESC;

インデックス設計

GINインデックス

-- JSONB全体にGINインデックス（@>, ?, ?| オペレータに有効）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 特定パスにインデックス（等値検索に有効）
CREATE INDEX idx_products_brand ON products USING BTREE ((attributes->>'brand'));

-- 数値条件のインデックス
CREATE INDEX idx_products_ram ON products USING BTREE (((attributes->>'ram')::int));

インデックスの使い分け

用途インデックス種別対応オペレータ
含有チェックGIN@>, ?, ?&, ?|
等値検索BTREE (式)=, <, >
パス検索GIN (jsonb_path_ops)@> のみ（軽量）
-- jsonb_path_opsは@>専用だがサイズが小さい
CREATE INDEX idx_products_attrs_path ON products
USING GIN (attributes jsonb_path_ops);

パフォーマンスのコツ

  1. 頻繁に検索する値はカラムに切り出す — JSONB内よりカラムの方が高速
  2. GINインデックスはWRITE負荷が高い — 更新頻度が高いテーブルでは注意
  3. jsonb_path_opsを優先 — @>しか使わないなら軽量
  4. 大きなJSONBは分割 — 1行あたり数MBを超えるJSONBは避ける

関連記事