PostgreSQL JSON/JSONB 高度な操作

はじめに

この実験（Lab）では、PostgreSQL の JSON/JSONB データ型に対する高度な操作について探求します。JSONB カラム内でのクエリパフォーマンスの向上とデータ操作に焦点を当てます。

JSONB フィールドに GIN インデックスを作成して検索を最適化する方法、ネストされた JSON 構造をクエリする方法、JSONB カラム内の特定の要素を更新する方法、およびレポートと分析のために JSON データを集計する方法を学びます。

Skills Graph

GIN による JSONB フィールドのインデックス作成

このステップでは、PostgreSQL で JSONB フィールドに GIN (Generalized Inverted Index) インデックスを作成する方法を探求します。GIN インデックスは、JSON ドキュメント内のキーと値を効率的に検索できるため、JSONB データのインデックス作成に特に役立ちます。

まず、postgres ユーザーとして psql コマンドを使用して PostgreSQL データベースに接続します。

sudo -u postgres psql

次に、products という名前のテーブルを作成し、JSONB 型の data カラムを追加します。

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

次に、サンプルデータを products テーブルに挿入します。

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}'),
('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB"]}'),
('{"name": "Mouse", "price": 30, "features": ["Wireless", "Ergonomic"]}'),
('{"name": "Monitor", "price": 300, "features": ["27 inch", "144Hz"]}');

JSONB フィールドに対するクエリを高速化するために、GIN インデックスを作成できます。たとえば、JSONB ドキュメント全体にインデックスを付けるには、次のコマンドを実行します。

CREATE INDEX idx_products_data ON products USING GIN (data);

インデックスが作成されたことを確認するには、psql で \di コマンドを使用します。

\di idx_products_data

出力には、インデックス idx_products_data とその詳細が表示されるはずです。

最後に、psql シェルを終了します。

\q

ネストされた JSON 構造のクエリ

このステップでは、PostgreSQL で JSONB データ型を使用して、ネストされた JSON 構造をクエリする方法を学びます。

まず、postgres ユーザーとして psql コマンドを使用して PostgreSQL データベースに接続します。

sudo -u postgres psql

より複雑なネストされたデータを products テーブルに追加しましょう。

INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD", "gpu": "Nvidia RTX 3070"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD", "gpu": "Integrated"}}');

ネストされた JSON 構造内のキーにアクセスするには、-> 演算子をチェーンできます。たとえば、「Gaming PC」の CPU を取得するには、次のように使用します。

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE name = 'Gaming PC';

->> 演算子は、値をテキストとして取得するために使用されます。-> を使用すると、結果は依然として JSONB オブジェクトになります。

ネストされた JSON 構造内の値に基づいて行をフィルタリングすることもできます。たとえば、Intel i5 CPU を搭載したすべての製品を見つけるには、次のようにします。

SELECT * FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

Nvidia RTX 3070 GPU を搭載した製品の名前を見つけましょう。

SELECT name FROM products WHERE data -> 'specs' ->> 'gpu' = 'Nvidia RTX 3070';

このクエリは "Gaming PC" を返すはずです。

最後に、psql シェルを終了します。

\q

特定の JSONB 要素の更新

このステップでは、PostgreSQL で JSONB カラム内の特定の要素を更新する方法を学びます。

まず、postgres ユーザーとして psql コマンドを使用して PostgreSQL データベースに接続します。

sudo -u postgres psql

jsonb_set 関数を使用して、JSONB データ内の特定の要素を更新します。基本的な構文は次のとおりです。

jsonb_set(target JSONB, path TEXT[], new_value JSONB, create_missing BOOLEAN)

たとえば、「Laptop」の価格を 1250 に更新するとします。

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::JSONB)
WHERE name = 'Laptop';

更新を確認するには、次のクエリを実行します。

SELECT data FROM products WHERE name = 'Laptop';

出力には、「Laptop」の価格が 1250 に更新されたことが表示されるはずです。

「Office PC」の価格を 100 増やしましょう。

UPDATE products
SET data = jsonb_set(data, '{price}', ((data ->> 'price')::numeric + 100)::TEXT::JSONB)
WHERE name = 'Office PC';

更新を確認するには、次のクエリを実行します。

SELECT data FROM products WHERE name = 'Office PC';

出力には、「Office PC」の価格が 100 増加したことが表示されるはずです。

最後に、psql シェルを終了します。

\q

JSON データの集計

このステップでは、PostgreSQL で JSONB カラムに格納されたデータを集計する方法を説明します。

まず、postgres ユーザーとして psql コマンドを使用して PostgreSQL データベースに接続します。

sudo -u postgres psql

JSONB データの集計には、JSONB オブジェクトから値を抽出し、SUM、AVG、MIN、MAX、COUNT などの集計関数を適用することがよくあります。

すべての製品の平均価格を計算するには、次のクエリを使用します。

SELECT AVG((data ->> 'price')::numeric) FROM products;

ここでは、->> を使用して price をテキストとして抽出し、数値型にキャストしてから、AVG 関数を使用して平均を計算します。

製品に "category" フィールドを追加しましょう。

UPDATE products SET data = jsonb_set(data, '{category}', '"Electronics"'::JSONB) WHERE id IN (1,4,5);
UPDATE products SET data = jsonb_set(data, '{category}', '"Accessories"'::JSONB) WHERE id IN (2,3);
UPDATE products SET data = jsonb_set(data, '{category}', '"Computers"'::JSONB) WHERE id IN (6);

次に、各カテゴリの製品数をカウントできます。

SELECT data ->> 'category', COUNT(*) FROM products GROUP BY data ->> 'category';

このクエリは、category の値をテキストとして抽出し、この値に基づいて行をグループ化します。

「Electronics」カテゴリのすべての製品の合計価格を計算しましょう。

SELECT SUM((data ->> 'price')::numeric) FROM products WHERE data ->> 'category' = 'Electronics';

このクエリは、Laptop、Monitor、および Gaming PC の価格の合計を返すはずです。

最後に、psql シェルを終了します。

\q

まとめ

この実験（Lab）では、PostgreSQL の JSONB データに関する高度な操作について調査し、インデックス作成、クエリ、更新、および集計に焦点を当てました。クエリのパフォーマンスを最適化するために、JSONB フィールドに GIN インデックスを作成しました。また、ネストされた JSON 構造をクエリする方法と、特定の JSONB 要素を更新する方法も示しました。最後に、さまざまな関数を使用して JSON データを集計する方法を学びました。