PostgreSQLでJSON型配列要素を操作する方法
2024-04-18
SQL、JSON、PostgreSQLにおける配列要素に対するクエリ
このチュートリアルでは、SQL、JSON、PostgreSQLを使用して、JSON型内の配列要素に対してクエリを実行する方法を説明します。JSON型は、構造化データの保存に役立つデータ形式です。
前提条件
- PostgreSQLデータベース
- JSONデータを含むテーブル
- SQLの基本的な知識
CREATE TABLE users (
id serial PRIMARY KEY,
name varchar(255),
interests json
);
INSERT INTO users (name, interests) VALUES
('Alice', '["reading", "music", "travel"]'),
('Bob', '["coding", "gaming", "sports"]'),
('Charlie', '["cooking", "photography", "art"]');
クエリの実行
JSON配列のすべての要素を取得する
SELECT id, name, interests->'interests' AS interests_array
FROM users;
結果
id | name | interests_array |
---+------------+---------------------------------------------------------------|
1 | Alice | ["reading", "music", "travel"] |
2 | Bob | ["coding", "gaming", "sports"] |
3 | Charlie | ["cooking", "photography", "art"] |
SELECT id, name, interests->'interests'[1] AS second_interest
FROM users;
id | name | second_interest |
---+------------+----------------------------------------------------------|
1 | Alice | "music" |
2 | Bob | "gaming" |
3 | Charlie | "photography" |
JSON配列の要素を条件付きで取得する
SELECT id, name, interests->'interests' AS interests_array
FROM users
WHERE interests->'interests'[1] = 'gaming';
id | name | interests_array |
---+------------+---------------------------------------------------------------|
2 | Bob | ["coding", "gaming", "sports"] |
SELECT id, name, interest
FROM users, json_array_elements(interests->'interests') AS interest;
id | name | interest |
---+------------+-------------------------------------------------------|
1 | Alice | "reading" |
1 | Alice | "music" |
1 | Alice | "travel" |
2 | Bob | "coding" |
2 | Bob | "gaming" |
2 | Bob | "sports" |
3 | Charlie | "cooking" |
3 | Charlie | "photography" |
3 | Charlie | "art" |
JSON配列内の要素を関数と組み合わせて処理する
SELECT id, name, UPPER(interest) AS upper_interest
FROM users, json_array_elements(interests->'interests') AS interest;
id | name | upper_interest |
---+------------+-------------------------------------------------------|
1 | Alice | READING |
1 | Alice | MUSIC |
1 | Alice | TRAVEL |
2 | Bob | CODING |
2 | Bob | GAMING |
2 | Bob | SPORTS |
3 | Charlie | COOKING |
3 | Charlie | PHOTOGRAPHY |
3 | Charlie | ART |
説明
json_array_elements()関数は、JSON配列内の各要素を個別の行として返します。->演算子は、JSONオブジェクト内のプロパティにアクセスするために使用されます。WHERE句は、クエリ結果をフィルタリングするために使用されます。WITH句は、一時的な結果セットを作成するために使用されます。SELECT句は、クエリから返される列
CREATE TABLE users (
id serial PRIMARY KEY,
name varchar(255),
interests json
);
INSERT INTO users (name, interests) VALUES
('Alice', '["reading", "music", "travel"]'),
('Bob', '["coding", "gaming", "sports"]'),
('Charlie', '["cooking", "photography", "art"]');
SELECT id, name, interests->'interests' AS interests_array
FROM users;
id | name | interests_array |
---+------------+---------------------------------------------------------------|
1 | Alice | ["reading", "music", "travel"] |
2 | Bob | ["coding", "gaming", "sports"] |
3 | Charlie | ["cooking", "photography", "art"] |
このクエリは、users テーブルからすべての行を返し、interests 列をJSON型から配列型に変換します。interests_array という名前の新しい列が作成され、その列には元のJSON配列のすべての要素が含まれます。
SELECT id, name, interests->'interests'[1] AS second_interest
FROM users;
id | name | second_interest |
---+------------+----------------------------------------------------------|
1 | Alice | "music" |
2 | Bob | "gaming" |
3 | Charlie | "photography" |
SELECT id, name, interests->'interests' AS interests_array
FROM users
WHERE interests->'interests'[1] = 'gaming';
id | name | interests_array |
---+------------+---------------------------------------------------------------|
2 | Bob | ["coding", "gaming", "sports"] |
このクエリは、users テーブルから interests 列の2番目の要素が "gaming" である行のみを返します。
SELECT id, name, interest
FROM users, json_array_elements(interests->'interests') AS interest;
id | name | interest |
---+------------+-------------------------------------------------------|
1 | Alice | "reading" |
1 | Alice | "music" |
1 | Alice | "travel" |
2 | Bob | "coding" |
2 | Bob | "gaming" |
2 | Bob | "sports" |
3 | Charlie | "cooking" |
3 | Charlie | "photography" |
3 | Charlie | "art" |
このクエリは、users テーブルの各行に対して interests 列をループし、各要素を interest という名前の新しい列に返します。
SELECT id, name, UPPER(interest) AS upper_interest
FROM users, json_array_elements(interests->'interests') AS interest;
id | name | upper_interest |
---+------------+-------------------------------------------------------|
1 | Alice | READING |
1 | Alice | MUSIC |
1 | Alice | TRAVEL |
2 | Bob | CODING |
2 | Bob | GAMING |
2 | Bob | SPORTS |
3 | Charlie | COOKING |
3 | Charlie | PHOTOGRAPHY |
3
JSON型配列要素に対するクエリ：その他の方法
jsonb_array_elements() 関数は、jsonb 型の配列内の各要素を個別の行として返します。jsonb 型は、json 型よりも効率的でスケーラブルなバイナリ表現のJSONデータ形式です。
SELECT id, name, interest
FROM users, jsonb_array_elements(interests->'interests') AS interest;
LATERAL 句は、クエリ内のサブクエリを実行し、その結果を基にメインクエリを処理できるようにします。
SELECT id, name, interest
FROM users
LATERAL json_array_elements(interests->'interests') AS interest;
unnest() 関数は、配列または多次元配列を個別の行と列に変換します。
SELECT id, name, interest
FROM users
UNNEST(interests->'interests') AS interest;
WITH 句と再帰CTE（共通表式）を使用して、JSON配列内の要素を再帰的に処理することができます。
WITH RECURSIVE interests_array AS (
SELECT id, name, interests->'interests' AS interest
FROM users
UNION ALL
SELECT id, name, json_array_element(interest) AS interest
FROM interests_array
WHERE json_array_length(interest) > 1
)
SELECT id, name, interest
FROM interests_array;
最適な方法の選択
使用する方法は、データの種類、クエリの複雑さ、パフォーマンス要件など、さまざまな要因によって異なります。
その他のヒント
- JSONデータの操作に関するその他のチュートリアルを参照してください。
- パフォーマンスが重要な場合は、
jsonb型の使用を検討してください。
sql json postgresql
