PostgreSQLとJSON：パフォーマンス最適化とインデックス戦略

PostgreSQLにおけるJSON型

PostgreSQLには主に2つのJSONデータ型があります。

• jsonb: JSONデータをバイナリ形式で保存します。これにより、クエリ時のパースが不要になり、処理速度が向上します。また、インデックスをサポートします。ただし、空白やキーの順序は保持されず、重複するキーがある場合は最後の値のみが保持されます。通常、jsonbの方が推奨されます。
• json: 入力されたJSONテキストを正確に保存します。空白やキーの順序も保持されます。クエリ時には毎回パースし直すため、処理速度はjsonbに劣ります。

JSON関数と演算子

PostgreSQLのJSON関数と演算子は、主に以下のカテゴリに分けられます。

1. データ作成関数 (Creation Functions)
2. データ抽出演算子 (Extraction Operators)
3. データ処理関数 (Processing Functions)
4. データ集約関数 (Aggregate Functions)
5. データ操作演算子 (Manipulation Operators)
6. 包含・存在演算子 (Containment and Existence Operators)
7. JSONパス演算子 (SQL/JSON Path Operators)

データ作成関数 (Creation Functions)

SQLの値をJSONに変換したり、JSONオブジェクトや配列を構築したりするために使用します。

• json_object(text[]) / jsonb_object(text[]): テキスト配列からJSONオブジェクトを構築します。配列は{key, value, key, value, ...}の形式である必要があります。
  • 例: SELECT json_object('{a, 1, b, 2}'); → {"a" : "1", "b" : "2"}
• json_build_object(VARIADIC "any") / jsonb_build_object(VARIADIC "any"): 可変個のキーと値のペアからJSONオブジェクトを構築します。
  • 例: SELECT json_build_object('name', 'Alice', 'age', 30); → {"name" : "Alice", "age" : 30}
• json_build_array(VARIADIC "any") / jsonb_build_array(VARIADIC "any"): 可変個の引数からJSON配列を構築します。
  • 例: SELECT json_build_array('a', 1, true); → ["a", 1, true]
• row_to_json(record [, boolean]): SQLの複合型 (行) をJSONオブジェクトに変換します。
• array_to_json(anyarray [, boolean]): SQLの配列をJSON配列に変換します。
• to_json(anyelement) / to_jsonb(anyelement): 任意のSQL値をJSON型またはJSONB型に変換します。
  • 例: SELECT to_json('Hello, world!'); → "Hello, world!"
  • 例: SELECT to_jsonb(ARRAY[1, 2, 3]); → [1, 2, 3]

データ抽出演算子 (Extraction Operators)

JSONデータから特定の要素を抽出するために使用します。

• #>> (JSON/JSONBサブオブジェクトをパスでテキストとして抽出)
  • 構文: json #>> text[]
  • 例: SELECT '{"user": {"id": 1, "data": {"city": "Tokyo"}}}'::json #>> '{user,data,city}'; → Tokyo (text型)
• #> (JSON/JSONBサブオブジェクトをパスで抽出)
  • 構文: json #> text[]
  • 例: SELECT '{"user": {"id": 1, "data": {"city": "Tokyo"}}}'::json #> '{user,data,city}'; → "Tokyo" (JSON型)
• ->> (JSON/JSONBオブジェクトフィールドまたは配列要素をテキストとして抽出)
  • 構文: json ->> text または json ->> integer
  • 例: SELECT '{"name": "Alice", "age": 30}'::json ->> 'name'; → Alice (text型)
  • 例: SELECT '[10, 20, 30]'::json ->> 1; → 20 (text型)
• -> (JSON/JSONBオブジェクトフィールドまたは配列要素をJSONとして抽出)
  • 構文: json -> text (オブジェクトの場合) または json -> integer (配列の場合)
  • 例: SELECT '{"name": "Alice", "age": 30}'::json -> 'name'; → "Alice" (JSON型)
  • 例: SELECT '[10, 20, 30]'::json -> 1; → 20 (JSON型)

データ処理関数 (Processing Functions)

JSONデータの構造を操作したり、情報を取得したりします。

• jsonb_pretty(jsonb): JSONB値を整形された（読みやすい）テキストとして返します。
  • 例: SELECT jsonb_pretty('{"a":1,"b":[2,3]}');

    {
        "a": 1,
        "b": [
            2,
            3
        ]
    }
    

• jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]): JSONBドキュメントの指定されたパスに新しい値を挿入します。配列の場合、insert_afterがtrueなら指定インデックスの後ろに挿入、falseなら前に挿入されます。
  • 例: SELECT jsonb_insert('[1,2,3]'::jsonb, '{1}', '99'::jsonb); → [1, 99, 2, 3]
• jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]): JSONBドキュメントの指定されたパスの値を設定します。create_missingがtrueの場合、パスが存在しない場合は作成されます。
  • 例: SELECT jsonb_set('{"a":1, "b":{"c":2}}'::jsonb, '{b,c}', '3'::jsonb); → {"a": 1, "b": {"c": 3}}
• json_strip_nulls(json) / jsonb_strip_nulls(jsonb): JSONオブジェクトからnull値を持つフィールドを削除したJSON値を返します。他のnull値（配列内のnullなど）はそのままです。
  • 例: SELECT json_strip_nulls('{"a":1, "b":null, "c":"foo"}'); → {"a":1, "c":"foo"}
• json_typeof(json) / jsonb_typeof(jsonb): 最も外側のJSON値の型をテキスト文字列で返します（object, array, string, number, boolean, null）。
  • 例: SELECT json_typeof('123.4'); → number
• json_object_keys(json) / jsonb_object_keys(jsonb): 最も外側のJSONオブジェクトのキーの集合を返します。
  • 例: SELECT json_object_keys('{"a":1, "b":"foo"}');

    json_object_keys
    ------------------
    a
    b
    

• json_each_text(json) / jsonb_each_text(jsonb): json_eachと同様ですが、値がtext型で返されます。
• json_each(json) / jsonb_each(jsonb): 最も外側のJSONオブジェクトをキーと値のペアの集合として展開します。値はjsonまたはjsonb型で返されます。
  • 例: SELECT * FROM json_each('{"a":1, "b":"foo"}');

    key | value
    -----+-------
    a   | 1
    b   | "foo"
    

• json_array_length(json) / jsonb_array_length(jsonb): 最も外側のJSON配列の要素数を返します。
  • 例: SELECT json_array_length('[1, 2, {"a":1}]'); → 3

データ集約関数 (Aggregate Functions)

複数の行からJSON値を集約します。

• json_object_agg(key "any", value "any") / jsonb_object_agg(key "any", value "any"): キーと値のペアをJSONオブジェクトに集約します。
  • 例: SELECT json_object_agg(id, name) FROM users; (usersテーブルにidとname列がある場合)
• json_agg(anyelement) / jsonb_agg(anyelement): 入力値をJSON配列に集約します。
  • 例: SELECT json_agg(col) FROM (VALUES (1), (2), (3)) AS t(col); → [1, 2, 3]

データ操作演算子 (Manipulation Operators)

jsonb型にのみ利用可能な、JSON構造を変更する演算子です。

• #- text[] (パスで指定された要素の削除): 指定されたパスにあるJSON要素を削除します。
  • 例: SELECT '{"a":{"b":1,"c":2}}'::jsonb #- '{a,b}'; → {"a": {"c": 2}}
• - integer (配列要素の削除): JSON配列から指定されたインデックスの要素を削除します。
  • 例: SELECT '[1,2,3]'::jsonb - 1; → [1, 3]
• - text (キーの削除): JSONオブジェクトから指定されたキーとその値を削除します。
  • 例: SELECT '{"a":1, "b":2}'::jsonb - 'b'; → {"a": 1}
• || (連結): 2つのjsonb値を連結します。配列を連結すると要素が結合され、オブジェクトを連結するとキーが結合されます（重複キーは右側の値が優先）。
  • 例: SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb; → {"a": 1, "b": 2}
  • 例: SELECT '[1,2]'::jsonb || '[3,4]'::jsonb; → [1, 2, 3, 4]

包含・存在演算子 (Containment and Existence Operators)

jsonb型にのみ利用可能で、JSONデータ内の特定のキーの存在や、部分的なJSON構造の包含をチェックします。これらの演算子はGINインデックスによって高速化できます。

• ?& (全てのキーの存在): JSONB文字列が指定されたテキスト配列の全てのキーを含んでいるかをチェックします。
  • 例: SELECT '{"a":1, "b":2}'::jsonb ?& ARRAY['a', 'b']; → t (true)
• ?| (いずれかのキーの存在): JSONB文字列が指定されたテキスト配列のいずれかのキーを含んでいるかをチェックします。
  • 例: SELECT '{"a":1, "b":2}'::jsonb ?| ARRAY['a', 'c']; → t (true)
• ? (キーの存在): JSONB文字列が指定されたテキストのキーを含んでいるかをチェックします。
  • 例: SELECT '{"a":1, "b":2}'::jsonb ? 'a'; → t (true)
• <@ (逆包含): @>の逆。右側のJSONB値が左側のJSONB値を包含しているかをチェックします。
• @> (包含): 左側のJSONB値が右側のJSONB値を包含しているか（部分集合として含んでいるか）をチェックします。
  • 例: SELECT '{"a":1, "b":{"c":2}}'::jsonb @> '{"b":{"c":2}}'::jsonb; → t (true)
  • 例: SELECT '[1,2,3]'::jsonb @> '[1,3]'::jsonb; → t (true)

SQL/JSONパス演算子 (SQL/JSON Path Operators)

PostgreSQL 12以降で導入されたSQL/JSON Path言語をサポートする演算子です。より強力で柔軟なJSONデータのクエリが可能です。

• @@ (パスがJSONB値を返すか): JSONB値が指定されたSQL/JSONパスの仕様を満たすかどうかをチェックし、結果としてブール値またはJSONB値のシーケンスを返します。通常、jsonb_path_existsやjsonb_path_queryなどの関数と組み合わせて使用されます。
  • 例: SELECT '{"a":1}'::jsonb @@ '$.a = 1'; → t (true)
• @? (パスに値が存在するか): JSONB値が指定されたSQL/JSONパスの仕様を満たすかどうかをチェックします。結果はブール値です。
  • 例: SELECT '{"a":1, "b":2}'::jsonb @? '$.a'; → t (true)
  • 例: SELECT '[{"id":1},{"id":2}]'::jsonb @? '$[*] ? (@.id == 1)'; → t (true)

• SQL/JSON Path: より複雑なJSONデータに対するクエリや変換を行う場合、SQL/JSON Path言語は非常に強力なツールとなります。
• エラーハンドリング: JSONパスの抽出演算子は、パスに一致するデータがない場合、エラーではなくNULLを返します。これは、構造が変化する可能性のあるJSONデータを扱う際に便利です。
• インデックス: jsonb型にはGINインデックスを適用することで、@>, ?, ?|, ?&などの演算子のパフォーマンスを大幅に向上させることができます。
• json vs jsonb: データの書き込み頻度と読み込み/クエリ頻度を考慮して適切な型を選択してください。通常、クエリ性能が重要な場合はjsonbが推奨されます。

---

データ型の不一致または不正なJSON形式

エラーの例

• malformed json (不正な形式のJSON)
• invalid input syntax for type json (JSON型への不正な入力構文)

原因

• 末尾のコンマ、欠落したブラケット（{}や[]）、不正な文字など、構文エラーがある。
• JSON文字列内のクォーテーションマーク（"）のエスケープが不足している、または過剰である。
• jsonまたはjsonb型へのキャストが正しく行われていない。
• 挿入しようとしている文字列が有効なJSON形式ではない。

トラブルシューティング

1. エスケープシーケンスの確認
   SQL文字列リテラル内でJSONを記述する場合、JSON内のダブルクォーテーションはエスケープ（\"）する必要があります。
   • 誤り
     INSERT INTO my_table (json_col) VALUES ('{"name": "Alice"}');
   • 正しい
     INSERT INTO my_table (json_col) VALUES ('{"name": "Alice"}'); (PostgreSQLでは通常、シングルクォーテーションで囲まれた文字列内のダブルクォーテーションはエスケープ不要ですが、JSON文字列を動的に生成する場合など、混同しないように注意が必要です。)
   • より安全な方法として、E''シンタックスやドルクォート（$$）を使用することも検討してください。
     • 例: INSERT INTO my_table (json_col) VALUES (E'{"name": "It\'s fine."}');
     • 例: INSERT INTO my_table (json_col) VALUES ($${"name": "It's fine."}$$);
2. 明示的なキャスト
   必要に応じて、::jsonまたは::jsonbで明示的にキャストします。
   • 例: SELECT '{"test": 1}'::jsonb;

存在しないキーやインデックスへのアクセス

エラーの例

• ほとんどの場合、エラーにはならず、単にNULLが返されます。これはエラーではなく、期待される動作として理解しておく必要があります。

原因

• ネストされたJSONで、パスの途中の要素が存在しない。
• JSON配列から範囲外のインデックスを指定して要素を抽出しようとしている。
• JSONオブジェクトから存在しないキーを抽出しようとしている。

トラブルシューティング

1. パスとキーの確認
   クエリで使用しているJSONパスやキーが、実際にJSONデータ内に存在するかを確認します。
   • jsonb_object_keys()関数を使ってオブジェクトのキーを一覧表示したり、jsonb_typeof()関数で値の型を確認したりするとデバッグに役立ちます。
   • 例: SELECT jsonb_object_keys('{"a":1, "b":2}');
2. NULL値のハンドリング
   存在しないパスへのアクセスはNULLを返すため、クエリロジックでNULL値を適切に処理する必要があります。COALESCEやWHERE ... IS NOT NULLを使用します。
   • 例: SELECT data->>'non_existent_key' FROM my_table; (これはNULLを返す)
   • 例: SELECT COALESCE(data->>'name', 'Unknown') FROM my_table;
3. jsonb_path_exists()での存在チェック
   SQL/JSONパス演算子を使用している場合、jsonb_path_exists()関数で事前に存在チェックを行うことで、後続の処理の安全性を高めることができます。
   • 例: SELECT data FROM my_table WHERE data @? '$.items[*]'; (items配列が存在するか)

不適切なJSON型へのキャストまたは比較

エラーの例

• operator does not exist: jsonb = integer (jsonb型とinteger型の=演算子は存在しない)
• operator does not exist: jsonb @> text (jsonb型とtext型の@>演算子は存在しない)

原因

• 比較対象の型が異なる。
• JSON関連の演算子や関数が、期待されるJSON型（jsonまたはjsonb）ではなく、text、integer、booleanなどの異なるSQL型に適用されている。

トラブルシューティング

1. 型の確認
   使用しているJSON関数や演算子が、正しいデータ型に対して適用されているかを確認します。特に->と->>の違いを理解することが重要です。
   • ->: JSON値を返す（引き続きJSON演算子を使用できる）
   • ->>: テキスト値を返す（テキスト演算子や他のSQL型へのキャストが必要）
   • 例: SELECT my_jsonb_column -> 'key' = 'value'; (これはエラー。左辺はjsonb型で、右辺はtext型)
   • 正しい
     SELECT (my_jsonb_column -> 'key')::text = 'value'; または SELECT my_jsonb_column ->> 'key' = 'value';
2. 明示的なキャスト
   比較や演算を行う前に、値を目的の型に明示的にキャストします。
   • 例: SELECT (my_jsonb_column -> 'number_key')::int > 10;
   • 例: SELECT my_jsonb_column @> '{"status": "active"}'::jsonb; (包含演算子を使用する場合、右辺もjsonb型にする)

jsonb_setやjsonb_insertでのパスの誤り

エラーの例

• cannot set or insert into an atomic value (アトミック値には設定または挿入できない)
• path element must be a string or integer (パス要素は文字列または整数でなければならない)

原因

• スカラー値（例: 123, "hello", true）に対してオブジェクトキーや配列インデックスを指定しようとしている。
• 存在しないパスの一部に値を挿入しようとしているが、create_missing引数がfalseになっている。
• jsonb_setやjsonb_insert関数のpath引数が不正な形式（例: {key, value}ではなく{key}の形式）である。

トラブルシューティング

1. パス配列の形式確認
   path引数はテキストの配列である必要があります。キーは文字列、配列のインデックスは整数の文字列で指定します。
   • 例: '{user,address,street}' (オブジェクトのパス)
   • 例: '{items,0}' (配列の0番目の要素へのパス)
2. create_missing引数
   オブジェクトや配列の途中パスが存在しない場合に新しく作成したい場合は、create_missingをtrueに設定します。
   • 例: SELECT jsonb_set('{}'::jsonb, '{a,b}', '1'::jsonb, true); → {"a": {"b": 1}}
3. アトミック値への操作
   jsonb_setは、スカラー値（number, string, boolean, null）の内部を変更することはできません。変更したい場合は、そのスカラー値を含む上位のオブジェクトまたは配列自体を操作する必要があります。

パフォーマンスの問題

エラーの例

• 直接的なエラーメッセージではなく、クエリの実行が遅い、CPU使用率が高いなどのパフォーマンスの問題として現れます。

原因

• 全データスキャンが必要な演算子（例: LIKE演算子をJSONデータ全体に適用するなど）を使用している。
• 大量のJSONデータをテキストとして扱うjson型を使用している。
• jsonb型に適切なインデックスが貼られていない。

1. jsonbの使用
   クエリ対象となるJSONデータは、基本的にjsonb型で保存することを強く推奨します。json型は文字列として保存するため、クエリごとにパースのオーバーヘッドが発生します。
2. GINインデックスの利用
   jsonb列に対してGINインデックスを作成することで、@>, ?, ?|, ?&などの包含・存在演算子のパフォーマンスを劇的に向上させることができます。
   • オブジェクトのキーや値の存在をチェックする場合
     CREATE INDEX idx_jsonb_col_gin ON my_table USING GIN (jsonb_column);
   • 特定のパスの値をインデックス化する場合 (PostgreSQL 12+)
     CREATE INDEX idx_jsonb_path_ops ON my_table USING GIN (jsonb_column jsonb_path_ops); このインデックスはSQL/JSONパス演算子 (@?, @@) やより複雑なクエリで効果を発揮します。
   • 特定のキーの値のみをインデックス化する場合
     CREATE INDEX idx_jsonb_col_key ON my_table USING GIN ((jsonb_column->'desired_key'));
3. インデックスの確認とEXPLAIN ANALYZE
   クエリがインデックスを正しく使用しているかを確認するために、EXPLAIN ANALYZEコマンドを使用します。インデックススキャンではなくシーケンシャルスキャンになっている場合は、インデックス戦略を見直す必要があります。

---

ここでは、jsonb型を中心に説明します。jsonbはバイナリ形式でJSONを格納するため、検索性能に優れており、ほとんどのユースケースで推奨されます。

まず、サンプルデータを使用するために、テーブルを作成し、データを挿入します。

-- テーブルの作成
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSONB
);

-- サンプルデータの挿入
INSERT INTO products (name, details) VALUES
('Laptop Pro', '{"brand": "ABC", "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}, "price": 1200.00, "features": ["lightweight", "long battery life"], "available": true}'),
('Smartphone X', '{"brand": "XYZ", "specs": {"cpu": "Snapdragon", "ram": "8GB", "storage": "128GB"}, "price": 800.00, "features": ["5G", "OLED display"], "available": false}'),
('External SSD', '{"brand": "ABC", "specs": {"capacity": "1TB", "interface": "USB 3.1"}, "price": 150.00, "available": true}');

JSONデータの作成

SQLのデータからJSONbデータを構築する例です。

to_jsonb(anyelement) / to_json(anyelement): 任意のSQL値をJSONBに変換

SELECT to_jsonb('Hello World');
-- 結果: "Hello World"

SELECT to_jsonb(12345);
-- 結果: 12345

SELECT to_jsonb(TRUE);
-- 結果: true

SELECT to_jsonb(ARRAY['apple', 'banana', 'cherry']);
-- 結果: ["apple", "banana", "cherry"]

-- 行型をJSONBオブジェクトに変換
SELECT to_jsonb(ROW(1, 'Test Item', 99.99));
-- 結果: {"f1": 1, "f2": "Test Item", "f3": 99.99}

jsonb_build_object(key1, value1, ...): キーと値のペアからJSONBオブジェクトを構築

SELECT jsonb_build_object('product_name', 'Tablet Pro', 'price', 450.00, 'in_stock', TRUE);
-- 結果: {"price": 450.0, "in_stock": true, "product_name": "Tablet Pro"}

jsonb_build_array(element1, element2, ...): 複数の要素からJSONB配列を構築

SELECT jsonb_build_array('red', 'green', 'blue', 123, true);
-- 結果: ["red", "green", "blue", 123, true]

JSONデータの抽出

JSONbデータから特定のフィールドや要素を取り出す例です。

-> (JSON/JSONBオブジェクトフィールドまたは配列要素をJSONBとして抽出)

-- オブジェクトのキーから値を抽出（JSONBとして）
SELECT details->'brand' AS brand_jsonb FROM products WHERE id = 1;
-- 結果: "ABC" (jsonb型)

-- 配列のインデックスから要素を抽出（JSONBとして、0から始まる）
SELECT details->'features'->0 AS first_feature_jsonb FROM products WHERE id = 1;
-- 結果: "lightweight" (jsonb型)

-- 複数の`->`をチェーンしてネストされた値にアクセス
SELECT details->'specs'->'cpu' AS cpu_jsonb FROM products WHERE id = 1;
-- 結果: "Intel i7" (jsonb型)

->> (JSON/JSONBオブジェクトフィールドまたは配列要素をテキストとして抽出)

-> と同様ですが、結果がテキスト型になります。比較や表示によく使われます。

-- オブジェクトのキーから値を抽出（テキストとして）
SELECT details->>'brand' AS brand_text FROM products WHERE id = 1;
-- 結果: ABC (text型)

-- 配列のインデックスから要素を抽出（テキストとして）
SELECT details->'features'->>1 AS second_feature_text FROM products WHERE id = 1;
-- 結果: long battery life (text型)

-- ネストされた値をテキストとして抽出
SELECT details->'specs'->>'ram' AS ram_text FROM products WHERE id = 1;
-- 結果: 16GB (text型)

#> (JSON/JSONBサブオブジェクトをパスで抽出、JSONBとして)

-- パス配列を使ってネストされたオブジェクトを抽出
SELECT details#>>'{specs,cpu}' AS cpu_text FROM products WHERE id = 1;
-- 結果: Intel i7 (text型)

-- パス配列を使って配列の要素を抽出
SELECT details#>>'{features,0}' AS first_feature_text FROM products WHERE id = 1;
-- 結果: lightweight (text型)

JSONデータの処理

JSONデータの情報を取得したり、変換したりする例です。

jsonb_array_length(jsonb): JSONB配列の要素数を取得

SELECT name, jsonb_array_length(details->'features') AS num_features FROM products WHERE id = 1;
-- 結果: Laptop Pro | 2

jsonb_object_keys(jsonb): JSONBオブジェクトのトップレベルキーを返す

SELECT name, jsonb_object_keys(details) AS top_level_keys FROM products WHERE id = 1;
-- 結果: Laptop Pro | brand
--             Laptop Pro | specs
--             Laptop Pro | price
--             Laptop Pro | features
--             Laptop Pro | available

jsonb_each(jsonb) / jsonb_each_text(jsonb): JSONBオブジェクトをキー/値のセットとして展開

SELECT name, key, value FROM products, jsonb_each(details) WHERE id = 1;
-- 結果:
-- name       | key       | value
-- -----------+-----------+-------------------------------------
-- Laptop Pro | brand     | "ABC"
-- Laptop Pro | specs     | {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}
-- Laptop Pro | price     | 1200.0
-- Laptop Pro | features  | ["lightweight", "long battery life"]
-- Laptop Pro | available | true

SELECT name, key, value FROM products, jsonb_each_text(details->'specs') WHERE id = 1;
-- 結果:
-- name       | key     | value
-- -----------+---------+-----------
-- Laptop Pro | cpu     | Intel i7
-- Laptop Pro | ram     | 16GB
-- Laptop Pro | storage | 512GB SSD

jsonb_typeof(jsonb): JSONB値の型をテキストで返す

SELECT details->'price', jsonb_typeof(details->'price') FROM products WHERE id = 1;
-- 結果: 1200.0 | number

SELECT details->'features', jsonb_typeof(details->'features') FROM products WHERE id = 1;
-- 結果: ["lightweight", "long battery life"] | array

JSONデータの操作 (更新・削除)

jsonb型にのみ利用可能です。

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) 指定されたパスの値を設定または更新します。create_missingをtrueにすると、パスが存在しない場合に作成されます。

-- 価格を更新
UPDATE products
SET details = jsonb_set(details, '{price}', '1250.00'::jsonb)
WHERE id = 1;

SELECT details->>'price' FROM products WHERE id = 1;
-- 結果: 1250.00

-- 新しい機能を追加（既存の配列に要素を追加する場合、インデックスを指定）
UPDATE products
SET details = jsonb_set(details, '{features,2}', '"touchscreen"'::jsonb, true)
WHERE id = 1;

SELECT details->'features' FROM products WHERE id = 1;
-- 結果: ["lightweight", "long battery life", "touchscreen"]

-- 新しいトップレベルのキーを追加
UPDATE products
SET details = jsonb_set(details, '{warranty_years}', '2'::jsonb, true)
WHERE id = 1;

SELECT details FROM products WHERE id = 1;
-- 結果: {"brand": "ABC", "price": 1250.00, "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}, "available": true, "features": ["lightweight", "long battery life", "touchscreen"], "warranty_years": 2}

jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]) 指定されたパスに新しい値を挿入します。配列の場合、insert_afterがtrueなら指定インデックスの後ろに、falseなら前に挿入されます。

-- 配列の最初の要素の前に新しい要素を挿入
UPDATE products
SET details = jsonb_insert(details, '{features,0}', '"new_feature_first"'::jsonb, false)
WHERE id = 1;

SELECT details->'features' FROM products WHERE id = 1;
-- 結果: ["new_feature_first", "lightweight", "long battery life", "touchscreen"]

-- 配列の最後の要素の後に新しい要素を挿入
UPDATE products
SET details = jsonb_insert(details, '{features,99}', '"new_feature_last"'::jsonb, true) -- 99は存在しないインデックスだが、trueで最後に追加
WHERE id = 1;

SELECT details->'features' FROM products WHERE id = 1;
-- 結果: ["new_feature_first", "lightweight", "long battery life", "touchscreen", "new_feature_last"]

|| (連結演算子) 二つのjsonb値を結合します。オブジェクトはキーを結合し、配列は要素を結合します。

SELECT '{"a":1, "b":2}'::jsonb || '{"c":3}'::jsonb;
-- 結果: {"a": 1, "b": 2, "c": 3}

SELECT '[1,2]'::jsonb || '[3,4]'::jsonb;
-- 結果: [1, 2, 3, 4]

-- 重複キーがある場合、右側の値が優先
SELECT '{"a":1, "b":2}'::jsonb || '{"b":3}'::jsonb;
-- 結果: {"a": 1, "b": 3}

- (キーまたは要素の削除) JSONオブジェクトからキーを削除したり、JSON配列から指定インデックスの要素を削除します。

-- オブジェクトからキーを削除
UPDATE products
SET details = details - 'available'
WHERE id = 1;

SELECT details FROM products WHERE id = 1;
-- 結果: {"brand": "ABC", "price": 1250.00, "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}, "features": ["new_feature_first", "lightweight", "long battery life", "touchscreen", "new_feature_last"], "warranty_years": 2} (availableが削除された)

-- 配列からインデックス1の要素を削除
UPDATE products
SET details = jsonb_set(details, '{features}', details->'features' - 1)
WHERE id = 1;

SELECT details->'features' FROM products WHERE id = 1;
-- 結果: ["new_feature_first", "long battery life", "touchscreen", "new_feature_last"] (lightweightが削除された)

#- (パスで指定された要素の削除)

-- ネストされたキーを削除
UPDATE products
SET details = details #- '{specs,storage}'
WHERE id = 1;

SELECT details->'specs' FROM products WHERE id = 1;
-- 結果: {"cpu": "Intel i7", "ram": "16GB"} (storageが削除された)

JSONデータの検索・フィルタリング

jsonb型にのみ利用可能な包含・存在演算子です。GINインデックスを適用することで高速化できます。

@> (包含): 左辺が右辺を包含しているか

-- '{"brand": "ABC"}'というJSONオブジェクトを包含する製品を検索
SELECT name, details FROM products WHERE details @> '{"brand": "ABC"}'::jsonb;
-- 結果: Laptop Pro | {"brand": "ABC", ...}
--             External SSD | {"brand": "ABC", ...}

-- 特定の機能を持つ製品を検索（配列の包含）
SELECT name, details FROM products WHERE details->'features' @> '["5G"]'::jsonb;
-- 結果: Smartphone X | {"brand": "XYZ", ...}

-- ネストされた条件で検索
SELECT name, details FROM products WHERE details @> '{"specs": {"cpu": "Intel i7"}}'::jsonb;
-- 結果: Laptop Pro | {"brand": "ABC", ...}

? (キーの存在): 特定のキーが存在するか

-- 'available'キーを持つ製品を検索
SELECT name, details FROM products WHERE details ? 'available';
-- 結果: Laptop Pro | {"brand": "ABC", ...}
--             Smartphone X | {"brand": "XYZ", ...}
--             External SSD | {"brand": "ABC", ...}

-- 'warranty_years'キーを持つ製品を検索
SELECT name, details FROM products WHERE details ? 'warranty_years';
-- 結果: Laptop Pro | {"brand": "ABC", ...} (更新例で追加したため)

?| (いずれかのキーの存在): 配列内のいずれかのキーが存在するか

-- 'cpu'または'capacity'のいずれかのキーを持つ製品を検索
SELECT name, details FROM products WHERE details->'specs' ?| ARRAY['cpu', 'capacity'];
-- 結果: Laptop Pro | {"brand": "ABC", ...}
--             Smartphone X | {"brand": "XYZ", ...}
--             External SSD | {"brand": "ABC", ...}

?& (全てのキーの存在): 配列内の全てのキーが存在するか

-- 'cpu'と'ram'の両方のキーを持つ製品を検索
SELECT name, details FROM products WHERE details->'specs' ?& ARRAY['cpu', 'ram'];
-- 結果: Laptop Pro | {"brand": "ABC", ...}
--             Smartphone X | {"brand": "XYZ", ...}

集約関数

複数の行のJSONデータを集約する例です。

jsonb_agg(expression): 複数の値をJSONB配列に集約

-- 各製品のブランド名をJSONB配列として取得
SELECT jsonb_agg(details->>'brand') AS all_brands FROM products;
-- 結果: ["ABC", "XYZ", "ABC"]

-- 特定の条件で絞り込んだ製品の価格をJSONB配列として取得
SELECT jsonb_agg(details->'price') AS available_product_prices
FROM products
WHERE details->>'available' = 'true';
-- 結果: [1250.0, 150.0]

jsonb_object_agg(key_expression, value_expression): キーと値のペアをJSONBオブジェクトに集約

-- 製品IDと製品名をキー/値としたJSONBオブジェクトを構築
SELECT jsonb_object_agg(id, name) AS product_map FROM products;
-- 結果: {"1": "Laptop Pro", "2": "Smartphone X", "3": "External SSD"}

-- ブランドと製品名をキー/値としたJSONBオブジェクトを構築（同じブランドは上書きされる）
SELECT jsonb_object_agg(details->>'brand', name) AS brand_to_product_map FROM products;
-- 結果: {"ABC": "External SSD", "XYZ": "Smartphone X"}

SQL/JSON Path演算子 (PostgreSQL 12+)

より強力で柔軟なJSONデータのクエリが可能です。

@? (パスが存在するか): JSONB値が指定されたSQL/JSONパスの仕様を満たすかどうかをチェックします。

-- 'specs.cpu'パスが存在する製品を検索
SELECT name FROM products WHERE details @? '$.specs.cpu';
-- 結果: Laptop Pro
--             Smartphone X

-- features配列に'5G'が含まれる製品を検索
SELECT name FROM products WHERE details @? '$.features[*] ? (@ == "5G")';
-- 結果: Smartphone X

-- 全製品からCPUの値を抽出
SELECT name, jsonb_path_query(details, '$.specs.cpu') AS extracted_cpu FROM products;
-- 結果:
-- name          | extracted_cpu
-- --------------+---------------
-- Laptop Pro   | "Intel i7"
-- Smartphone X | "Snapdragon"
-- External SSD | NULL (cpuがないため)

-- features配列から'lightweight'と'5G'を抽出
SELECT name, jsonb_path_query(details, '$.features[*] ? (@ == "lightweight" || @ == "5G")') AS selected_features FROM products;
-- 結果:
-- name          | selected_features
-- --------------+-------------------
-- Laptop Pro   | "lightweight"
-- Smartphone X | "5G"

---

アプリケーション層でのJSON処理

最も一般的な代替手段は、データベースではなくアプリケーションのコード内でJSONデータを処理することです。

• 適しているケース:
  • JSONデータが比較的小さく、頻繁なサーバーサイドでのフィルタリングや集計を必要としない場合。
  • アプリケーションが既に高度なJSON処理ライブラリを持っており、その機能を活用したい場合。
  • 開発チームがSQLのJSON関数に不慣れな場合。
• 欠点:
  • データベースの負荷分散の欠如: フィルタリングや集計など、本来データベースで効率的に行える処理をアプリケーションに持っていくと、データベースから大量のJSONデータを転送する必要が生じ、ネットワーク帯域やアプリケーションサーバーのリソースを消費する可能性がある。
  • SQLの制約: SQLレベルでの強力なJSONクエリ機能（例: GINインデックスによる高速検索）を活用できない。
  • トランザクション管理: JSONデータの一部のみを変更する場合でも、全体を読み込み、変更し、書き戻すという処理が必要になり、競合状態やトランザクションの複雑さが増す場合がある。
• 利点:
  • 言語の熟練度: 開発者が使い慣れたプログラミング言語のJSONライブラリを使用できるため、学習コストが低い。
  • 複雑なロジック: データベースのSQLでは表現しにくい複雑なデータ変換やビジネスロジックを、アプリケーション層で柔軟に実装できる。
  • 可搬性: データベースに依存しないJSON処理ロジックを構築できる。
  • デバッグの容易さ: アプリケーションのデバッグツールやIDEを使って、JSON処理部分をステップ実行できる。

テキスト型 (TEXT) としてJSONを保存する

JSONデータをTEXT型としてデータベースに保存し、必要に応じてアプリケーションでパースする方法です。

• 適しているケース:
  • JSONデータが参照用であり、データベース内での検索や操作がほとんど不要な場合。
  • ごく単純なログや設定ファイルを保存するような、非常に限定的なユースケース。
• 欠点:
  • 検索性能の劣悪さ: JSONデータの内容に基づいた検索やフィルタリングが非常に遅くなる。部分文字列検索（LIKEなど）しかできず、インデックスがほとんど効かない。
  • ストレージ効率: jsonb型のような最適化されたバイナリ形式ではなく、テキスト形式で保存されるため、ストレージ効率が悪い場合がある。
  • データ検証の欠如: データベース側でJSON形式が有効であるかどうかの検証が行われないため、不正なJSONデータが保存されるリスクがある。
• 利点:
  • 最もシンプル: データベース側での特別な設定や型変換が不要。
  • スキーマレス: 厳密なJSON構造を意識する必要がない（ただし、これは欠点にもなり得る）。

HSTORE型を利用する (非推奨)

PostgreSQLのhstore型は、キー-値ペアのセットを保存するのに使われる古いデータ型です。JSONの登場以前は、半構造化データを扱うためによく利用されていました。

• 適しているケース:
  • 既存のシステムでhstoreが既に使われており、互換性のために維持する必要がある場合。
  • ごく単純でフラットなキー-値データのみを扱う場合。
• 欠点:
  • ネスト構造のサポートなし: JSONの主要な利点であるネストされたオブジェクトや配列を直接表現できない。
  • 値の型がすべてテキスト: すべての値がテキストとして保存されるため、数値や真偽値としての直接的な操作ができない。
  • JSONBの登場: jsonb型が導入されて以来、ほとんどの半構造化データユースケースでhstoreは推奨されなくなった。
• 利点:
  • JSONより古くから存在し、一部のシステムでは既に利用されている。
  • 単純なキー-値データには効率的。

JSONを完全にリレーショナルモデルに変換し、複数のテーブルに分解する方法です。

• 適しているケース:
  • データの構造が非常に安定しており、変更がほとんど発生しない場合。
  • 厳格なデータ整合性とリレーショナルな分析機能が最優先される場合。
  • JSONデータが実際にリレーショナルな関係性を持っている場合。
• 欠点:
  • スキーマ変更の複雑さ: JSONの構造変更（新しいフィールドの追加など）があった場合、テーブルスキーマの変更が必要になり、マイグレーション作業が発生する。
  • 結合の増加: ネストされたJSON構造は複数のテーブルに分散されるため、元のJSONデータ全体を取得するためには多くのJOINが必要になり、クエリが複雑化し、パフォーマンスに影響を与える可能性がある。
  • 柔軟性の欠如: 半構造化データが持つ柔軟性（スキーマの事前定義が不要、動的なフィールドの追加など）が失われる。
• 利点:
  • データの整合性: 厳密なスキーマと外部キー制約により、データの整合性を最大限に高めることができる。
  • 明確な構造: データベースの構造が明確で、開発者間の理解が容易。
  • リレーショナルな強み: リレーショナルデータベースの設計原則とツール（ORマッパーなど）を最大限に活用できる。

PostgreSQLのネイティブJSON関数と演算子（特にjsonb型）は、多くのユースケースで最も推奨されるアプローチです。特に、データベース層での効率的な検索、フィルタリング、部分的な更新が必要な場合には、その強力な機能とパフォーマンス上の利点が際立ちます。