トップ > web開発 > PostgreSQLのjsonb型を活用したテーブル設計

PostgreSQLのjsonb型を活用したテーブル設計

web開発 システム設計 リレーショナルデータベース

エクストーンの豊田です。 最近、PostgreSQLのテーブル設計を行う機会があり、jsonb型を利用することでシンプルな構造を実現できたという体験がありました。

今回は、jsonb型の有用性やデータの作成・取得・検索方法、インデックスの作成方法について、こちらの記事で紹介させていただきます。

jsonb型を利用すると有用なケース

jsonb型は、JSONデータをバイナリ形式で格納するPostgreSQLのデータ型です。以下のようなケースで特に有用です。

  • ユーザー設定や属性を柔軟に管理する場合：例えば、ユーザーごとに異なる設定や好みを保存する際、jsonb型を使うとフィールドを追加せずにデータを格納できます。

  • 商品カタログサイトの情報：商品ごとに異なる属性（色、サイズ、ブランド、仕様など）を持つ場合、jsonb型で動的にデータを保存できます。新しい商品属性が追加されても、スキーマの変更が不要です。

  • ログデータやイベントデータの保存：アプリケーションから生成されるログやイベント情報をjsonb型で保存することで、後から必要な情報を柔軟に解析できます。

  • マイクロサービス間のデータ統合：異なるサービスからの多様なデータを統一的に管理する際、スキーマを固定せずにjsonb型でデータを保存できます。

  • 多言語対応 : 一つのカラムで、複数言語に対応した文字列情報を保存することができます。

上記のように、関連を気にする必要がなく、スキーマが一意に定まらないケースにおいて、jsonb型を有用に活用することができます。

jsonb型のデータ作成方法

jsonb型のデータを作成・挿入する方法を紹介します。

テーブルの作成

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);
  • detailsカラムにjsonb型を指定します。

データの挿入

INSERT INTO products (name, details) VALUES (
    'スマートフォン',
    '{"color": "ブラック", "storage": "128GB", "features": {"camera": "12MP", "battery": "3000mAh"}}'
);
  • JSON形式の文字列をそのまま挿入します。

データの更新

UPDATE products
SET details = details || '{"price": 80000}'
WHERE id = 1;
  • ||演算子で既存のJSONデータに新しいフィールドを追加します。
  • 同じ属性がすでに存在している場合、その値を更新します。

jsonb型のデータ取得方法

jsonb型のデータから特定の値を取得する方法をいくつか紹介します。

基本的な取得方法

SELECT name, details -> 'color' AS color FROM products;
  • 結果例：
name color
スマートフォン "ブラック"
  • ->演算子は、指定したキーの値をJSON形式で取得します。

ネストされたJSONデータの取得

SELECT name, details -> 'features' -> 'camera' AS camera_spec FROM products;
  • 結果例：
name camera_spec
スマートフォン "12MP"
  • ネストされたキーには、->を連続して使用します。

テキストとして値を取得

SELECT name, details ->> 'storage' AS storage_capacity FROM products;
  • 結果例：
name storage_capacity
スマートフォン 128GB
  • ->>演算子は、値をテキスト形式で取得します。

値の型変換

SELECT name, (details ->> 'price')::INT AS price FROM products;
  • 結果例：
name price
スマートフォン 80000
  • テキスト形式で取得した値を数値型に変換します。

jsonb型のデータ検索方法

jsonb型のデータを検索する際のクエリの書き方を紹介します。

特定のキーや値を持つデータの検索

SELECT * FROM products WHERE details @> '{"color": "ブラック"}';
  • @>演算子は、指定したJSONが包含されているかを確認します。

キーの存在確認

SELECT * FROM products WHERE details ? 'price';
  • ?演算子は、指定したキーが存在するかを確認します。

複雑な条件での検索

SELECT * FROM products WHERE details -> 'features' ->> 'battery' = '3000mAh';
  • ネストされた値に対して条件を指定します。

jsonbデータのインデックス作成方法

jsonb型ので保存されたデータを検索する場合、他の型のカラムと同様にインデックスを作成することができます。

GINインデックスの作成

CREATE INDEX idx_products_details ON products USING GIN (details);
  • GIN（Generalized Inverted Index）インデックスは、jsonb型の全体に対して有効です。包含演算子（@>など）を使用した検索を高速化します。

この方式ですと、任意の属性名についてインデックスが有効になるため、jsonb型のユースケースにマッチしていると思います。

特定のキーに対するインデックス

CREATE INDEX idx_products_color ON products ((details ->> 'color'));
  • 特定のキーの値に対してインデックスを作成します。これにより、details ->> 'color'を使用した検索が高速化されます。

任意の属性が設定可能なjsonb型ですが、このインデックスには属性名を指定する必要があるので、 できればクエリ側で包含演算子を利用するように工夫する方が望ましいです。

インデックス作成時の注意点

一般的に、インデックスを過剰に作成する場合、以下のデメリットがあることに気をつける必要があります。

  • ディスク容量の消費：インデックスは追加のディスクスペースを使用します。
  • データ更新時のオーバーヘッド：データの挿入や更新時にインデックスも更新されるため、パフォーマンスに影響を与える可能性があります。

jsonb型のカラムの値に対して、あらゆる考えられるクエリに対してインデックスを作成すると過剰になり、デメリットの方が大きくなることもあります。 頻繁に使用する検索条件に合わせてインデックスを設計するなど、必要に応じてインデックスの最適化を行うようにしてください。

おわりに

jsonb型を活用することで、PostgreSQLでのデータベース設計がより柔軟になり、複雑なデータ構造にも対応できるようになります。 特に、データ構造が頻繁に変化するサービスや、多様な情報を扱うサイト、ログや監査等の仕組みなどで大きな効果を発揮します。

実際にシンプルなツリー構造でしかないのに、リレーションとして表現しようとしてテーブルが増えてしまうことなどがよくあるかと思いますが、 jsonb型によって、よりシンプルなデータ構造を実現することが可能です。

この記事が、皆様のよりよいDBテーブル設計につながれば幸いです。