PostgreSQLの高度なデータ型

はじめに

この実験では、PostgreSQLの高度なデータ型であるJSON/JSONB、配列、およびUUIDに焦点を当て、それらのデータ型を使用してデータを保存、クエリ、操作する方法を学びます。

実験の冒頭では、JSONおよびJSONBデータの保存とクエリ方法を実演します。これには、JSONBカラムを持つテーブルの作成、JSONデータの挿入、および -> や ->> といった演算子を使用して特定の値を抽出する方法が含まれます。その後、配列カラムとUUIDについて学習を進めます。

JSONおよびJSONBデータの保存とクエリ

このステップでは、PostgreSQLでJSONおよびJSONBデータを保存・クエリする方法を学びます。PostgreSQLには、JSONデータを保存するための JSON と JSONB という2つのデータ型があります。JSON データ型は入力されたJSONテキストの正確なコピーを保存するのに対し、JSONB データ型はJSONデータを分解されたバイナリ形式で保存します。JSONB はクエリやインデックス作成において優れたパフォーマンスを発揮するため、一般的に推奨されています。

まず、PostgreSQLシェルを開きましょう。最初に labex データベースに接続します。

sudo -u postgres psql -d labex

PostgreSQLのプロンプトが表示されるはずです。

labex=#

次に、JSONBデータを保存するためのテーブルを作成します。

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

このSQLコマンドは products という名前のテーブルを作成します。このテーブルには、自動インクリメントされる整数型の主キー id と、JSONデータを保存するための JSONB 型のカラム data の2つが含まれています。

以下のような出力が表示されるはずです。

CREATE TABLE

それでは、products テーブルにデータを挿入してみましょう。

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');

これらのコマンドにより、products テーブルに2行のデータが挿入されます。各行には、製品情報を含むJSONオブジェクトが格納されています。

各挿入に対して、以下のような出力が表示されるはずです。

INSERT 0 1

JSONデータをクエリするには、-> および ->> 演算子を使用します。-> 演算子はJSONオブジェクトを返し、->> 演算子はJSON値をテキストとして返します。

例えば、最初の製品の名前を取得するには、以下のクエリを使用します。

SELECT data ->> 'name' FROM products WHERE id = 1;

このコマンドは、id が1である products テーブルの data カラムから、キー name に関連付けられた値を選択します。->> 演算子を使用することで、結果がテキストとして返されることが保証されます。

以下のような出力が表示されるはずです。

  ?column?
----------
 Laptop
(1 row)

ネストされたJSONオブジェクトをクエリすることも可能です。例えば、最初の製品の最初の機能（feature）を取得するには、以下のクエリを使用します。

SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;

このコマンドは、まず data カラムから features 配列を選択し、次にその配列のインデックス0の要素を選択します。->> 演算子により、結果はテキストとして返されます。

以下のような出力が表示されるはずです。

  ?column?
----------
 16GB RAM
(1 row)

また、@> 演算子を使用して、JSONオブジェクトが特定のキーと値のペアを含んでいるかを確認することもできます。例えば、価格が75の製品をすべて見つけるには、以下のクエリを使用します。

SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';

このコマンドは、data カラムに price キーと値75を持つJSONオブジェクトが含まれている products テーブルのすべての行から、名前を選択します。

以下のような出力が表示されるはずです。

  ?column?
----------
 Keyboard
(1 row)

素晴らしい！JSONBデータを持つ最初のテーブルを作成し、そのクエリ方法を学びました。次のステップでは、このテーブルを引き続き使用して、より高度な機能を追加していきます。

既存のテーブルへの配列カラムの追加

このステップでは、PostgreSQLで配列カラムを追加および操作する方法を学びます。既存の products テーブルを拡張し、タグを保存するための配列カラムを追加します。配列カラムを使用すると、同じデータ型の複数の値を単一のカラムに保存できるため、タグ、カテゴリ、機能などのリストを保存するのに便利です。

すでにデータベースに接続し、前のステップで作成した products テーブルがある状態ですので、既存のテーブルに配列カラムを追加しましょう。

ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];

これらのコマンドは、既存の products テーブルに name（文字列）と tags（文字列の配列）という2つの新しいカラムを追加します。TEXT[] データ型は、tags カラムがテキスト値の配列であることを指定しています。

各ALTERコマンドに対して、以下のような出力が表示されるはずです。

ALTER TABLE

次に、既存のデータを更新し、新しいカラムにデータを挿入します。

UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;

これらのコマンドは、JSONBデータから抽出した名前とタグ情報、および新しい配列値で既存の製品を更新します。

各更新に対して、以下のような出力が表示されるはずです。

UPDATE 1

配列データをクエリするには、配列インデックスを使用できます。PostgreSQLの配列インデックスは1から始まります。

例えば、最初の製品の最初のタグを取得するには、以下のクエリを使用します。

SELECT tags[1] FROM products WHERE id = 1;

このコマンドは、id が1である products テーブルの tags 配列から、インデックス1の要素を選択します。

以下のような出力が表示されるはずです。

   tags
-----------
 electronics
(1 row)

また、UNNEST 関数を使用して、配列を行のセットに展開することもできます。

例えば、すべての製品からすべてのタグを取得するには、以下のクエリを使用します。

SELECT name, UNNEST(tags) AS tag FROM products;

このコマンドは、name と tags 配列の各個別の tag を選択し、タグごとに新しい行を作成します。

以下のような出力が表示されるはずです。

  name   |     tag
---------+-------------
 Laptop  | electronics
 Laptop  | computers
 Laptop  | portable
 Keyboard| electronics
 Keyboard| accessories
 Keyboard| input
(6 rows)

@> 演算子を使用して、配列が特定の値を含んでいるかを確認することもできます。

例えば、'electronics' というタグを持つすべての製品を見つけるには、以下のクエリを使用します。

SELECT name FROM products WHERE tags @> ARRAY['electronics'];

このコマンドは、tags 配列に 'electronics' という値が含まれている products テーブルのすべての行から名前を選択します。

以下のような出力が表示されるはずです。

  name
----------
 Laptop
 Keyboard
(2 rows)

また、&& 演算子を使用して、2つの配列が共通の要素を持っているかを確認することもできます。

例えば、最初の製品とタグを共有しているすべての製品を見つけるには、以下のクエリを使用します。

SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;

このコマンドは、最初の製品（エイリアス p1）と少なくとも1つの共通タグを持つ、products テーブル（エイリアス p2）のすべての行の名前を選択します（最初の製品自体は除外されます）。

以下のような出力が表示されるはずです。

  name
----------
 Keyboard
(1 row)

完璧です！既存のテーブルに配列カラムを追加し、配列の扱い方を学びました。products テーブルには現在JSONBと配列の両方のデータ型が含まれており、次のステップに進む準備が整いました。

UUIDカラムの追加とUUID生成の学習

このステップでは、PostgreSQLでUUID（Universally Unique Identifiers：汎用一意識別子）を生成および使用する方法を学びます。UUIDは128ビットの数値で、空間と時間を超えて一意になるように設計されています。データベーステーブルにおいて、異なるソースからのデータをマージする際の競合を避けるためのユニークな識別子としてよく使用されます。

引き続き現在のセッションとテーブルを使用し、products テーブルにUUIDカラムを追加してUUIDの機能を実演します。

まず、UUID生成関数を提供するUUID拡張機能を有効にします。

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

このコマンドは、まだ存在しない場合に uuid-ossp 拡張機能を作成します。

以下のような出力が表示されるはずです。

CREATE EXTENSION

次に、既存の products テーブルにUUIDカラムを追加します。

ALTER TABLE products ADD COLUMN uuid_id UUID;

このコマンドにより、既存のテーブルに新しいUUIDカラムが追加されます。

以下のような出力が表示されるはずです。

ALTER TABLE

次に、uuid_generate_v4() 関数を使用して、既存の行をUUID値で更新します。

UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;

これらのコマンドは、既存の製品をユニークなUUID値で更新します。uuid_generate_v4() 関数は、各行に対して新しいUUIDを生成します。

各更新に対して、以下のような出力が表示されるはずです。

UPDATE 1

UUIDを使用してデータをクエリするには、WHERE 句でUUID値を使用します。UUIDはランダムに生成されるため、まずは現在のデータを確認しましょう。

SELECT id, name, uuid_id FROM products;

このコマンドは、products テーブルのすべてのカラムを選択し、生成されたUUIDを他のデータと一緒に表示します。

以下のような出力が表示されるはずです（UUIDは異なります）。

 id |   name   |               uuid_id
----+----------+--------------------------------------
  1 | Laptop   | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
  2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)

次に、以下のコマンドでそのUUIDを使用します。<YOUR_UUID_HERE> を取得した実際のUUIDに置き換えてください。

SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';

このコマンドは、uuid_id が指定したUUIDと一致する products テーブルから name を選択します。

以下のような出力が表示されるはずです（選択したUUIDによって異なります）。

  name
----------
 Laptop
(1 row)

次のステップでも引き続き使用するため、products テーブルと uuid-ossp 拡張機能はそのままにしておいてください。

高度なデータ型からのデータ抽出

この最後のステップでは、この実験を通じて products テーブルに追加したすべての高度なデータ型からデータを抽出する練習をします。テーブルには現在JSONB、配列、UUIDカラムが含まれており、PostgreSQLの高度なデータ型の包括的な例となっています。

引き続き現在のセッションとテーブルを使用するため、これまでのステップで構築してきたデータを使ってすぐに作業を開始できます。

まず、抽出の例をより包括的なものにするために、JSONBデータをいくつか追加します。

UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;

これらのコマンドは、ネストされたオブジェクトや追加フィールドを含む詳細な情報で、既存のJSONBデータを更新します。

各更新に対して、以下のような出力が表示されるはずです。

UPDATE 1

それでは、すべての高度なデータ型からデータを抽出する練習をしましょう。JSONBの data カラムからデータを抽出するには、-> および ->> 演算子を使用します。例えば、更新されたデータからブランドを抽出するには：

SELECT data ->> 'brand' FROM products WHERE id = 1;

このコマンドは、data カラムからキー brand に関連付けられた値を取得します。

以下のような出力が表示されるはずです。

 ?column?
----------
 Dell
(1 row)

JSONBカラムからネストされたデータを抽出するには、-> と ->> 演算子を連結します。例えば、RAMの仕様を抽出するには：

SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;

このコマンドは、specs オブジェクト内のキー ram に関連付けられた値を取得します。

以下のような出力が表示されるはずです。

 ?column?
----------
 16GB
(1 row)

tags（配列）カラムからデータを抽出するには、ステップ2で学んだように配列インデックスを使用します。例えば、最初の製品の最初のタグを抽出するには：

SELECT tags[1] FROM products WHERE id = 1;

このコマンドは、id が1である products テーブルの tags 配列から、インデックス1の要素を取得します。

以下のような出力が表示されるはずです。

   tags
-----------
 electronics
(1 row)

最後に、JSONB、配列、UUIDというすべての高度なデータ型からデータを抽出する包括的なクエリを作成してみましょう。

SELECT
    id,
    name,
    data ->> 'brand' AS brand,
    data -> 'specs' ->> 'ram' AS ram,
    tags[1] AS first_tag,
    uuid_id
FROM products;

このコマンドは、取り組んできたすべての高度なデータ型からデータを取得します。整数型の id、name、JSONB data カラムからの brand、ネストされた ram 仕様、tags 配列の最初の要素、そして uuid_id です。

以下のような出力が表示されるはずです。

   name   |  ?column?  |   tags
----------+------------+-----------
 Laptop   | Dell       | electronics
 Keyboard | Logitech   | electronics
(2 rows)

素晴らしい！単一のテーブルで3つの高度なPostgreSQLデータ型すべてを扱うことに成功しました。この包括的な例は、JSONB、配列、UUIDを組み合わせて、柔軟で強力なデータベーススキーマを作成する方法を示しています。

すべてのステップが完了したら、以下のコマンドでPostgreSQLシェルを終了できます。

\q

PostgreSQLの高度なデータ型をさらに実験するために、テーブルをそのまま残しておくことも可能です。

まとめ

この実験では、PostgreSQLの高度なデータ型を実際に活用する包括的な products テーブルを段階的に構築し、操作しました。JSONBカラムを持つテーブルの作成から始め、-> や ->> といった演算子を使用してJSONデータを保存およびクエリする方法を学びました。

次に、配列カラムを追加してテーブルを拡張し、単一のカラムに複数の値を保存する方法や、配列インデックスおよび UNNEST などの関数を使用してクエリする方法を学びました。続いて、uuid-ossp 拡張機能を有効にし、UUIDカラムを追加してユニークな識別子を生成することで、UUID機能を追加しました。

最後に、JSONB、配列、UUIDの3つの高度なデータ型すべてを組み合わせた洗練されたクエリを使用して、包括的なデータ抽出テクニックを練習しました。このステップバイステップのアプローチを通じて、これらのデータ型が実際のデータベーススキーマでどのように連携し、現代のアプリケーションに対して柔軟性と強力なクエリ機能を提供できるかを実証しました。