PostgreSQLでのJSON型とJSONB型の違い(実際に動かしてみる)

皆さんはデータベース、何を使用していますか？

私は業務以外も含めて、PostgreSQLとSQLite, MongoDB, DynamoDBくらいしか触った事がありません。業務ではPostgreSQLを触っています。

そんなPostgreSQLはMySQLを抜いて、現在人気No.1になっているようです。
その原因について、こちらの記事が考察していて面白いです。

要約すると、生成AI時代に求められる色々な機能が柔軟に取り込まれていて使いやすいって話です。

その中でも私が気になったのはJSONB型です。JSONB型自体は、PostgreSQL 9.4（2014年12月リリース）から対応しているそうですが、私は使用した事がありませんでした。

JSONB型は簡単に言えば、PostgreSQL内でNoSQL的運用ができますよって機能です。

これを使う際に気をつけなければいけないので、JSON型もあるという事です。

そこで今回は、なんだか間違えそうなJSON型とJSONB型の違いをまとめました。

JSON型とJSONB型の違い

JSON型はデータを文字列として保存し、JSONB型はデータをバイナリ形式で保存します。
これが明確な違いですが、具体的にどのように異なってくるかは下記のようになります。

JSON型の特徴

保存データはtext型に近い
基本的にはJSONデータの文字列を検証して保存しますが、実際にはtext型に近い挙動をします。クエリ実行時にデータをパース（データを解析して、その構造を理解し、プログラムが扱える形式に変換すること）する必要があるため、処理が遅くなりがちです。

自由度が高い
データはそのまま文字列として保存されるため、構造に特化した操作が必要ない場合や、単純にJSONデータを保存しておきたい場合に向いています。

特定のデータのみを変更するのが難しい
JSON型では、部分的な更新（例: JSON内の特定のキーだけを更新する）ができません。一度データ全体を取り出して編集し、再度保存する必要があります。

JSONB型の特徴

データ制約を加えることができる
JSONB型では、インデックスを作成することで効率的な検索が可能になります。
また、CHECK制約やトリガーを使用してJSONB型データの構造や値に対する制約を加えることも可能です（例: 特定のキーが存在するか確認する）。

インデックスが設定できる
GINインデックスやGiSTインデックスを使うことで、データ構造全体や特定のキーに効率的な検索を適用できます。これにより、クエリの速度が向上します。

クエリ操作が早い
保存時にバイナリ形式に変換されるため、データを効率的に扱えます。
クエリ時にパースが不要なため、特に頻繁にアクセスするデータではJSONB型が高速です。

使い分け

JSON型を使う場合

• データを保存するだけで特別な操作が必要ない場合
• クエリの頻度が少なく、主に読み書きの記録用途で使う場合
• 元のJSONデータの形式を完全に保ちたい場合（順序が重要な場合など）

JSONB型を使う場合

• データに対して頻繁に検索やフィルタを行う場合
• 部分更新が必要な場合
• インデックスを使用して高速化したい場合

実際にローカルのデータベースでテストしてみる

ここからは実際にどのような違いがあるのかを、DockerでPostgreSQLのテーブルを作成してテストしてみましょう。

テスト環境
MacBook Pro
チップ：Apple M3 Max
メモリ：36GB

Dockerコンテナの作成

まずはDockerでPostgreSQLのコンテナを作成します。

version: '3.8'  # 必要に応じてバージョンを指定
services:
  db:
    image: postgres:17.2
    container_name: postgres_container
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: test_db
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
volumes:
  postgres_data:

Dockerの起動コマンドでDockerを起動します。

docker-compose up -d

テーブルの作成

先ほど作成した、Dockerコンテナが起動した状態でテーブルを作成します。
今回は企業のデータベースをイメージして作成しました。

import psycopg2

# PostgreSQL接続情報
DB_NAME = "test_db"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_HOST = "localhost"
DB_PORT = "5432"

# テーブル作成クエリ
CREATE_JSON_TABLE = """
-- 企業情報テーブル
CREATE TABLE company_json (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    prefecture VARCHAR(100) NOT NULL,
    industry VARCHAR(50) NOT NULL,
    details JSON
);
"""

CREATE_JSONB_TABLE = """
-- 企業情報テーブル
CREATE TABLE company_jsonb (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    prefecture VARCHAR(100) NOT NULL,
    industry VARCHAR(50) NOT NULL,
    details JSONB
);
"""

def create_tables():
    try:
        # PostgreSQLに接続
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT,
        )
        cursor = conn.cursor()

        # テーブル作成
        cursor.execute(CREATE_JSON_TABLE)
        cursor.execute(CREATE_JSONB_TABLE)

        # コミットして接続を閉じる
        conn.commit()
        print("テーブルが作成されました。")
    except Exception as e:
        print(f"エラーが発生しました: {e}")
    finally:
        cursor.close()
        conn.close()


# 関数を実行
create_tables()

ダミーデータの作成

テーブルが作成できたらダミーデータを作成します。
業種ごとに入力される情報が異なるような場面を想定しています。
例）飲食業の場合、料理のジャンルや席数、営業時間など

import json
import random

import psycopg2

# 47都道府県リスト
PREFECTURES = [
    "Hokkaido",
    "Aomori",
    "Iwate",
    "Miyagi",
    "Akita",
    "Yamagata",
    "Fukushima",
    "Ibaraki",
    "Tochigi",
    "Gunma",
    "Saitama",
    "Chiba",
    "Tokyo",
    "Kanagawa",
    "Niigata",
    "Toyama",
    "Ishikawa",
    "Fukui",
    "Yamanashi",
    "Nagano",
    "Gifu",
    "Shizuoka",
    "Aichi",
    "Mie",
    "Shiga",
    "Kyoto",
    "Osaka",
    "Hyogo",
    "Nara",
    "Wakayama",
    "Tottori",
    "Shimane",
    "Okayama",
    "Hiroshima",
    "Yamaguchi",
    "Tokushima",
    "Kagawa",
    "Ehime",
    "Kochi",
    "Fukuoka",
    "Saga",
    "Nagasaki",
    "Kumamoto",
    "Oita",
    "Miyazaki",
    "Kagoshima",
    "Okinawa",
]

# 業種リスト
INDUSTRIES = ["飲食業", "製造業", "物流業", "医療"]


# 業種に応じたJSONデータ生成
def generate_details(industry):
    if industry == "飲食業":
        return {
            "ジャンル": random.choice(["和食", "洋食", "中華", "ファストフード"]),
            "店舗数": random.randint(1, 100),
            "営業時間": random.choice(["9:00-18:00", "11:00-23:00", "24時間"]),
        }
    elif industry == "製造業":
        return {
            "ジャンル": random.choice(["食品", "機械", "化学", "電子"]),
            "工場情報": {
                "所在地": random.choice(PREFECTURES),
                "従業員数": random.randint(10, 1000),
            },
        }
    elif industry == "物流業":
        return {"所有トラック台数": random.randint(1, 500)}
    elif industry == "医療":
        return {"対応科": random.choice(["内科", "外科", "小児科", "皮膚科", "眼科"])}


# ダミーデータ生成
def generate_data():
    data = []
    for i in range(100000):
        industry = random.choice(INDUSTRIES)
        data.append(
            {
                "company_name": f"Company_{i}",
                "prefecture": random.choice(PREFECTURES),
                "industry": industry,
                "details": generate_details(industry),
            }
        )
    return data


# PostgreSQLに接続してデータを挿入
def insert_data():
    conn = psycopg2.connect(
        dbname="test_db",
        user="postgres",
        password="password",
        host="localhost",
        port="5432",
    )
    cursor = conn.cursor()

    data = generate_data()

    for record in data:
        # company_json (JSON型のテーブル) への挿入
        cursor.execute(
            "INSERT INTO company_json (company_name, prefecture, industry, details) VALUES (%s, %s, %s, %s)",
            (
                record["company_name"],
                record["prefecture"],
                record["industry"],
                json.dumps(record["details"]),
            ),
        )

        # company_jsonb (JSONB型のテーブル) への挿入
        cursor.execute(
            "INSERT INTO company_jsonb (company_name, prefecture, industry, details) VALUES (%s, %s, %s, %s)",
            (
                record["company_name"],
                record["prefecture"],
                record["industry"],
                json.dumps(record["details"]),
            ),
        )

    conn.commit()
    cursor.close()
    conn.close()


insert_data()

これで10万件のランダムなダミーデータを作成できました。
ここからは実際にダミーデータを用いて操作の違いを体感してみましょう。

データに制約を加える

JSONB型では、データに制約を加えることができます。

例えば、特定のキーが存在することを制約にすることができます。
例）キーに「ジャンル」と「店舗数」を必須にする

ALTER TABLE company_jsonb
ADD CONSTRAINT check_details_keys
CHECK (
    details ? 'ジャンル' AND
    details ? '店舗数'
);

また値の範囲を指定することもできます。
例）店舗数の値は数値型で1以上500以下

ALTER TABLE company_jsonb
ADD CONSTRAINT check_store_count
CHECK (
    (details ->> '店舗数')::INT >= 1 AND
    (details ->> '店舗数')::INT <= 500
);

今回のように他のカラムの値によってJSONB型のデータの制約が変わることへも対応できます。
(実際にやってみます)

CREATE OR REPLACE FUNCTION validate_jsonb_data()
RETURNS TRIGGER AS $$
BEGIN
    -- 飲食業の場合、'ジャンル', '店舗数', '営業時間' が必要
    IF NEW.industry = '飲食業' THEN
        IF NOT (NEW.details ? 'ジャンル' AND NEW.details ? '店舗数' AND NEW.details ? '営業時間') THEN
            RAISE EXCEPTION 'Invalid JSONB data for 飲食業. 必要なキーが不足しています。';
        END IF;
    END IF;

    -- 製造業の場合、'ジャンル', '工場情報' が必要
    IF NEW.industry = '製造業' THEN
        IF NOT (NEW.details ? 'ジャンル' AND NEW.details ? '工場情報') THEN
            RAISE EXCEPTION 'Invalid JSONB data for 製造業. 必要なキーが不足しています。';
        END IF;
    END IF;

    -- 物流業の場合、'所有トラック台数' が必要
    IF NEW.industry = '物流業' THEN
        IF NOT (NEW.details ? '所有トラック台数') THEN
            RAISE EXCEPTION 'Invalid JSONB data for 物流業. 必要なキーが不足しています。';
        END IF;
    END IF;

    -- 医療の場合、'対応科' が必要
    IF NEW.industry = '医療' THEN
        IF NOT (NEW.details ? '対応科') THEN
            RAISE EXCEPTION 'Invalid JSONB data for 医療. 必要なキーが不足しています。';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

このルールが作成できたら、テーブルに紐づけます。

CREATE TRIGGER trigger_validate_jsonb
BEFORE INSERT OR UPDATE ON company_jsonb
FOR EACH ROW
EXECUTE FUNCTION validate_jsonb_data();

そして、ルールに違反するクエリをテストしてみます。

test_db=# CREATE TRIGGER trigger_validate_jsonb
BEFORE INSERT OR UPDATE ON company_jsonb
FOR EACH ROW
EXECUTE FUNCTION validate_jsonb_data();
CREATE TRIGGER
test_db=# INSERT INTO company_jsonb (company_name, prefecture, industry, details)
VALUES (
    'Test Company',
    'Tokyo',
    '飲食業',
    '{"ジャンル": "和食", "店舗数": 10}'::jsonb -- '営業時間' が不足
);
ERROR:  Invalid JSONB data for 飲食業. 必要なキーが不足しています。
CONTEXT:  PL/pgSQL function validate_jsonb_data() line 6 at RAISE

無事エラーが出ました。
このように制約を作成することで、データが荒れることを防ぐ事ができます。

クエリの速度

JSON型とJSONB型のクエリ速度の違いを実際に計測してみます。
今回は飲食業の”和食”ジャンルを取得するクエリでテストします。

import time

import psycopg2

# PostgreSQL接続情報
DB_NAME = "test_db"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_HOST = "localhost"
DB_PORT = "5432"


# クエリの検索速度を測定する関数
def measure_query_speed(table_name, condition):
    conn = psycopg2.connect(
        dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST, port=DB_PORT
    )
    cursor = conn.cursor()

    query = f"SELECT COUNT(*) FROM {table_name} WHERE details ->> 'ジャンル' = %s;"
    start_time = time.time()
    cursor.execute(query, (condition,))
    result = cursor.fetchone()
    end_time = time.time()

    cursor.close()
    conn.close()

    return result[0], end_time - start_time


# 検索条件とテーブル
condition = "和食"  # 飲食業のジャンルを条件とする
tables = ["company_json", "company_jsonb"]


def main():
    for table in tables:
        count, duration = measure_query_speed(table, condition)
        print(f"テーブル: {table}, 該当件数: {count}, 実行時間: {duration:.4f}秒")


if __name__ == "__main__":
    main()

私のローカル環境でのテスト結果はこのようになりました。

テーブル	該当件数	テスト1回目	テスト2回目	テスト3回目	テスト4回目	テスト5回目	平均時間
company_json	6243	0.0703秒	0.0754秒	0.0722秒	0.0397秒	0.0738秒	0.0663秒
company_jsonb	6243	0.0102秒	0.0105秒	0.0099秒	0.0087秒	0.0100秒	0.0099秒

JSONB型のテーブル(company_jsonb)は JSON型のテーブル(company_json) の約 6.7 倍高速に処理できました。
今回は１０万件のデータでしたが、データ量が増えたり複雑な検索をするともうもっと処理速度の差が出てくるかなと思います。

また、JSONB型ではインデックスの設定を行うこともできます。
pg_trgm拡張機能の有効化とインデックスの作成をします。

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_jsonb_genre ON company_jsonb USING gin ((details->>'ジャンル') gin_trgm_ops);

インデックスを作成後の検索のパフォーマンスは下記のようになりました。

テーブル	該当件数	テスト1回目	テスト2回目	テスト3回目	テスト4回目	テスト5回目	平均時間
company_json	6243	0.0703秒	0.0754秒	0.0722秒	0.0397秒	0.0738秒	0.0663秒
company_jsonb	6243	0.0102秒	0.0105秒	0.0099秒	0.0087秒	0.0100秒	0.0099秒
company_jsonb(インデックス済み)	6243	0.0059秒	0.0055秒	0.0052秒	0.0047秒	0.0051秒	0.0053秒

インデックス作成前と比べて、さらに倍ほど早くなりました。
インデックスの作成・更新コストがありますが、検索速度のパフォーマンスという意味ではかなり優位に立てると思います。

インデックス作成・更新コストと検索パフォーマンスについては、また別でテストをしてみたいですね。

値の更新・追加

JSON型はテキストのような形なので、データの追加や特定の値を更新する場合、現在のすべての取得して更新・追加をする必要があります。

そのため、処理が煩雑になったり、遅くなったりします。

24時間営業のファミレスへの補助金があるとして、そのフラグを追加します。

import psycopg2
import time

# PostgreSQL接続情報
DB_NAME = "test_db"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_HOST = "localhost"
DB_PORT = "5432"

# JSON型の更新クエリ
JSON_UPDATE_QUERY = """
UPDATE company_json
SET details = (details::jsonb || '{"補助金": "東京都補助金"}'::jsonb)::json
WHERE details->>'営業時間' = '24時間'
  AND prefecture = 'Tokyo'
  AND industry = '飲食業';
"""

# JSONB型の更新クエリ
JSONB_UPDATE_QUERY = """
UPDATE company_jsonb
SET details = jsonb_set(details, '{補助金}', '"東京都補助金"'::jsonb)
WHERE details->>'営業時間' = '24時間'
  AND prefecture = 'Tokyo'
  AND industry = '飲食業';
"""

# データ更新と速度測定
def update_and_measure(query, table_name):
    try:
        # データベース接続
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT
        )
        cursor = conn.cursor()

        # 実行前の時間を記録
        start_time = time.time()

        # クエリを実行
        cursor.execute(query)
        conn.commit()

        # 実行後の時間を記録
        end_time = time.time()

        # 実行時間の計測
        elapsed_time = end_time - start_time
        print(f"テーブル: {table_name}, 実行時間: {elapsed_time:.4f}秒")

    except Exception as e:
        print(f"エラー: {e}")
    finally:
        cursor.close()
        conn.close()

# 実行
if __name__ == "__main__":
    print("JSON型の更新処理:")
    update_and_measure(JSON_UPDATE_QUERY, "company_json")

    print("JSONB型の更新処理:")
    update_and_measure(JSONB_UPDATE_QUERY, "company_jsonb")

実行結果は下記のようになりました。

テーブル	実行時間
company_json	0.0598秒
company_jsonb	0.0352秒

今回の更新対象は184件でした。jsonbの方がやはり早いです。
データの更新頻度が高い場合などは、JSONB型が優位なようです。

最後に

今回はPostgreSQLのJSON型とJSONB型の違いについてテストをしながら見てきました。

以前、運用する中でデータ構造の変更が見込まれる案件があり、その際にNoSQLを使うかどうかなどの調査をしたり、PostgreSQLとMongoDBを組み合わせて使う方法などを検討した事がありました。
その際に、JSONB型のことを知っていればその選択肢を取ることもできたのにと後悔しています。

JSONB型は便利な反面、運用を考慮して制約などを入れないとデータがぐちゃぐちゃになってしまうので、実際の運用での知見などが求められると思います。

バックエンドエンジニアでも、データベースに関してはなんとなくという人もいるのではないかと思います(私がそう)。Pythonの処理速度の遅さが指摘される事もありますが、Pythonの処理速度よりもI/O（Input/Output）の処理を最適化する、データベースを最適化することの方が効果的な場面もあるかと思います。

そんな人の参考になれば幸いです。

(PostgreSQLの処理速度やパフォーマンスの計測に問題がありましたら、指摘していただければと思います。)