LabEx
ログイン登録

PostgreSQL 高度データ型

PostgreSQLBeginner
オンラインで実践に進む

はじめに

この実験では、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 (自動インクリメントされる整数型の主キー) と data (JSON データを格納する JSONB 列) の 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 オブジェクトをクエリすることもできます。たとえば、最初の製品の最初の機能を取得するには、次のクエリを使用できます。

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}';

このコマンドは、products テーブルから、data 列が価格 75 の price キーを持つ JSON オブジェクトを含んでいるすべての行の名前を選択します。

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

  ?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 に対して、以下のような出力が表示されるはずです。

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;

このコマンドは、tags 配列から name と個々の 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'];

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

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

  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;

このコマンドは、products テーブル (エイリアス p2) から、最初の製品 (エイリアス p1) のタグと少なくとも 1 つのタグを共有しているすべての行の名前を選択します。ただし、最初の製品自体は除外します。

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

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

完璧です！既存のテーブルに配列列を正常に追加し、配列の操作方法を学びました。これで、products テーブルには JSONB データ型と配列データ型の両方が含まれるようになり、次のステップの準備が整いました。

UUID 列の追加と UUID 生成の学習

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

既存のセッションとテーブルを継続するため、UUID の機能を示すために現在の products テーブルに UUID 列を追加します。

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

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

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

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

CREATE EXTENSION

次に、既存の products テーブルに UUID 列を追加しましょう。

ALTER TABLE products ADD COLUMN uuid_id UUID;

このコマンドは、既存の products テーブルに新しい 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 に対して、以下のような出力が表示されるはずです。

UPDATE 1

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

SELECT id, name, uuid_id FROM products;

このコマンドは、products テーブルからすべての列を選択し、他のデータと共に生成された UUID を表示します。

以下のような出力が表示されるはずです (UUID は異なる場合があります)。

                  id
------------------------------------
 a1b2c3d4-e5f6-7890-1234-567890abcdef
(1 row)

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

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

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

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

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

最後に、作成したテーブルと拡張機能をクリーンアップしましょう。

DROP TABLE products;
DROP EXTENSION "uuid-ossp";

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

DROP TABLE
DROP EXTENSION

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

この最終ステップでは、この実験全体を通して 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 に対して、以下のような出力が表示されるはずです。

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 機能を追加しました。

最後に、洗練されたクエリで 3 つの高度なデータ型 (JSONB、配列、UUID) をすべて組み合わせた包括的なデータ抽出技術を練習しました。この段階的なアプローチは、これらのデータ型が実際のデータベーススキーマでどのように連携できるかを示し、最新のアプリケーションに柔軟性と強力なクエリ機能を提供します。

関連 PostgreSQL コース
初心者のための PostgreSQL

初心者のための PostgreSQL

databasepostgresql
高度な PostgreSQL 実践ラボ

高度な PostgreSQL 実践ラボ

databasepostgresql