はじめに
この実験では、PostgreSQL の JSONB データ型を使用した高度な操作を探求します。リレーショナルデータベース内で JSON データを効果的に格納、管理、およびクエリする方法を学びます。
この実験では、JSONB 列を持つテーブルの作成、データの挿入、そして検索パフォーマンスを向上させるための GIN インデックスの適用について説明します。また、ネストされた JSON 構造のクエリ、JSONB ドキュメント内の特定の要素の更新、および JSON データに対する集計計算の実行についても練習します。
テーブルの作成と JSONB フィールドのインデックス作成
このステップでは、JSONB データ型を使用して製品情報を格納するテーブルを作成し、そのデータに対するクエリを最適化するために GIN インデックスを作成します。JSONB は JSON データを分解されたバイナリ形式で格納するため、入力は若干遅くなりますが、処理ははるかに高速になります。GIN (Generalized Inverted Index) は、JSONB 列内のデータのような複合値をインデックス化するのに理想的です。
まず、ターミナルを開き、psql 対話型シェルを使用して PostgreSQL データベースに接続します。
sudo -u postgres psql
これで、postgres=# のような PostgreSQL プロンプトが表示されます。
次に、id 列と JSONB 型の data 列を持つ products という名前のテーブルを作成します。
CREATE TABLE products (
id SERIAL PRIMARY KEY,
data JSONB
);
次に、products テーブルにサンプル製品データを挿入します。
INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');
data 列内の検索を大幅に高速化するために、それに GIN インデックスを作成します。
CREATE INDEX idx_products_data ON products USING GIN (data);
\di コマンドを使用してインデックスが正常に作成されたことを確認できます。このコマンドはすべてのインデックスを一覧表示します。
\di
以下のような出力と同様に、リレーションの一覧に idx_products_data が表示されるはずです。
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------+-------+----------+-----------+------------+-------------
public | idx_products_data | index | postgres | products | 16 kB |
public | products_pkey | index | postgres | products | 16 kB |
(2 rows)
テーブルのセットアップと JSONB 列へのインデックス作成が正常に完了しました。次のステップのために psql シェルに留まります。
ネストされた JSON 構造のクエリ
このステップでは、ネストされた構造を含む JSONB 列内のデータをクエリする方法を学びます。PostgreSQL はこの目的のためにいくつかの演算子を提供しています。
まず、より複雑でネストされたデータを持つ製品を products テーブルに挿入しましょう。
INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD"}}');
トップレベルのキーにアクセスするには、値をテキストとして返す ->> 演算子を使用できます。「Laptop」の価格を見つけましょう。
SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';
出力は次のようになります。
price
-------
1200
(1 row)
ネストされた JSON オブジェクト内のキーにアクセスするには、-> 演算子と ->> 演算子を連鎖させることができます。-> 演算子は JSON オブジェクトフィールドを取得し、->> はそれをテキストとして取得します。「Gaming PC」の CPU 仕様を取得しましょう。
SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';
このコマンドは CPU タイプを返します。
?column?
--------------
Intel i7
(1 row)
これらの演算子を WHERE 句で使用して、ネストされた値に基づいて結果をフィルタリングすることもできます。たとえば、「Intel i5」CPU を持つすべての製品を見つけます。
SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';
クエリは一致する製品の名前を返します。
name
-----------
Office PC
(1 row)
これで、JSONB 列内のトップレベルデータとネストされたデータの両方をクエリできるようになりました。
特定の JSONB 要素の更新
このステップでは、jsonb_set 関数を使用して JSONB 列内の特定の要素を更新する方法を学びます。これは、JSON オブジェクト全体を取得し、アプリケーションで変更してから書き戻すよりも効率的です。
jsonb_set 関数は次の構文を持ちます：jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing)。
「Laptop」の価格を 1200 から 1250 に更新しましょう。価格へのパスは '{price}' であり、新しい値は JSONB にキャストする必要があります。
UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';
ラップトップのデータを選択して更新を確認します。
SELECT data FROM products WHERE data ->> 'name' = 'Laptop';
出力は新しい価格を示すはずです。
data
--------------------------------------------------------------------------
{"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)
ネストされた値も更新できます。「Office PC」の RAM を「32GB」にアップグレードしましょう。ネストされた ram キーへのパスは '{specs,ram}' です。
UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';
次に、このネストされた更新を確認します。
SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';
結果は ram 値の変更を確認します。
?column?
------------------------------------------------------------
{"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)
これで、JSONB データに対するターゲットを絞った更新を実行する方法を学びました。
JSON データの集計
この最終ステップでは、JSONB 列から抽出されたデータに対して集計計算を実行します。これはレポート作成や分析に役立ちます。
計算を実行するには、値を抽出し、数値型にキャストする必要があります。すべての製品の平均価格を計算しましょう。
SELECT AVG((data ->> 'price')::numeric) FROM products;
このクエリは price をテキストとして抽出し、numeric にキャストしてから平均を計算します。出力は単一の数値になります。
avg
------------------------
659.1666666666666667
(1 row)
GROUP BY 句で集計関数を使用することもできます。各タグの製品の総数を調べましょう。これを行うには、まず jsonb_array_elements_text を使用して tags 配列を個別の行に展開する必要があります。
SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;
このクエリは、一意の各タグのカウントを生成します。
tag | count
-------------+-------
accessory | 2
electronics | 4
computer | 1
display | 1
(4 rows)
最後に、「electronics」タグを持つすべての製品の総価値を調べましょう。
SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;
@> 演算子は、左側の JSONB 値が右側の JSONB 値を含むかどうかをチェックします。「electronics」要素を tags 配列が含むかどうかを確認します。
結果は、4 つの電子製品の価格の合計です。
sum
--------
1655
(1 row)
これで、JSONB データに対して集計関数を使用する方法を学びました。psql シェルを終了するには、次のように入力します。
\q
まとめ
この実験では、PostgreSQL で JSONB データを扱うためのいくつかの高度な操作を学びました。JSONB 列を持つテーブルを作成することから始め、クエリパフォーマンスを最適化するために GIN インデックスを使用することの重要性を確認しました。次に、-> および ->> 演算子を使用して、トップレベルとネストされた両方の JSONB データをクエリする練習をしました。さらに、jsonb_set 関数を使用して JSONB ドキュメントに対してターゲットを絞った変更を実行する方法、および AVG、COUNT、SUM のような集計関数を使用して JSON データに対して強力なデータ分析を実行する方法を学びました。
