本記事は HRBrain Advent Calendar 2024 の 21 日目の記事です。
はじめに
業務で PostgreSQL を使用していて、DB テーブルのカラムに保存された JSON データを SQL で絞り込みたい場面がありました。本記事では、その際に学んだ内容を共有します。
対象読者
- PostgreSQL を使用している方
- JSON や JSONB の違いを知りたい方
- JSON や JSONB のデータを絞り込みする方法を知りたい方
基礎知識
JSON 絞り込みに使う主要な演算子
PostgreSQL では、JSON や JSONB 型のデータを操作するための演算子が提供されています。
※JSON と JSONB の違いは下記。
- JSON: 可読性が高いテキスト形式で保存
- JSONB: バイナリ形式で保存され、高速な検索や操作が可能
JSONB の方が演算子が豊富で、より高度な操作が可能です。また、インデックスを作成できるため、検索パフォーマンスも優れています。
共通で使用可能な演算子
-
->: JSON および JSONB データから指定したキーの値を JSON 形式で取得 -
->>: JSON および JSONB データから指定したキーの値を文字列として取得 -
#>: ネストされた JSON および JSONB データから特定のキーを指定して値を JSON 形式で取得 -
#>>: ネストされた JSON および JSONB データから特定のキーを指定して値を文字列形式で取得
JSONB のみで使用可能な演算子
-
?: JSONB オブジェクトや配列に指定したキーや値が存在するかを判定 -
@>: JSONB データが特定の構造を含むかどうかを判定 -
?|: JSONB 配列内のいずれかの要素が指定した条件を満たすかを判定 -
&>: JSONB 配列が指定したすべての要素を含むかを判定
これらの機能を活用することで、JSON 形式で保存されたデータに対して柔軟なクエリを実行することができます。
JSONB データのキャスト
JSONB から取得したデータは、デフォルトで文字列型として扱われます。そのため、数値や日付などとして扱う場合は、適切なデータ型にキャストする必要があります。
主なキャストの例を以下に示します。
- 数値型へのキャスト:
-
::integer: 整数型へのキャスト。小数点以下は切り捨て。 -
::numeric: 任意精度の数値型へのキャスト。小数点以下の桁数を保持。 -
::int: integer の別名。同じ動作。
-
- 日付型へのキャスト:
-
::date: 日付のみを扱う型へのキャスト。例: '2024-01-21' -
::timestamp: 日付と時刻を扱う型へのキャスト。例: '2024-01-21 13:45:30' -
::timestamptz: タイムゾーン付きの日付と時刻を扱う型へのキャスト。例: '2024-01-21 13:45:30+09:00'
-
- 真偽値へのキャスト:
::boolean
具体例
ここでは、実際に JSONB で保存されたデータを絞り込む方法を具体例を交えて解説します。
テーブル構造
以下のようなテーブル sample_table があるとします。
CREATE TABLE sample_table (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO sample_table (data) VALUES
('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}}'),
('{"name": "Bob", "age": 30, "skills": ["Java", "JavaScript"], "location": {"city": "Osaka"}}'),
('{"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}');
このテーブルの data カラムには、ユーザー情報が JSON 形式で保存されています。
絞り込みの例
1. 特定のキーの値を条件に絞り込む
例えば、name が "Alice" のレコードを取得したい場合、以下のクエリを使用します。
SELECT *
FROM sample_table
WHERE data ->> 'name' = 'Alice';
-
->>: JSONB から指定したキーの値を文字列として取得する演算子
実行結果
| id | data |
|---|---|
| 1 | {"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}} |
2. 数値の条件で絞り込む
age が 30 より大きいレコードを取得するには、以下のクエリを使用します。
SELECT *
FROM sample_table
WHERE (data ->> 'age')::int > 30;
-
(data ->> 'age')::int: JSONB から取得した値を数値型にキャストしています。
実行結果
| id | data |
|---|---|
| 3 | {"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}} |
3. 配列の要素を条件に絞り込む
skills 配列に "Python" が含まれるレコードを取得するには、以下のクエリを使用します。
SELECT *
FROM sample_table
WHERE data -> 'skills' ? 'Python';
-
?: JSONB 配列に特定の値が含まれているかを判定する演算子
実行結果
| id | data |
|---|---|
| 1 | {"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}} |
| 3 | {"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}} |
4. ネストされたキーを条件に絞り込む
ネストされた JSON データにアクセスする場合は、#> を使用します。
SELECT *
FROM sample_table
WHERE data #> '{location, city}' = '"Tokyo"';
-
#>: JSONB からネストされたキーを指定して値を取得する演算子
実行結果
| id | data |
|---|---|
| 1 | {"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}} |
パフォーマンスの注意点
JSONB カラムを頻繁に絞り込む場合、インデックスを作成することでパフォーマンスの向上を期待できます。
1. GIN インデックスの作成
data カラム全体にインデックスを作成する例
CREATE INDEX idx_sample_table_data ON sample_table USING gin (data);
2. 特定のキーにインデックスを作成する
特定のキーを対象としたインデックスを作成する例
CREATE INDEX idx_sample_table_data_name ON sample_table ((data ->> 'name'));
このインデックスを作成すると、name を条件にした絞り込みが高速になります。
まとめ
PostgreSQL では、JSON や JSONB カラムを柔軟に扱うことができます。これにより、ネストされたデータ構造や配列をそのまま保持しつつ、柔軟なクエリを実行できます。
一方で、リレーショナルデータベースの正規化手法と比較すると、JSON を使用することでスキーマの制約が緩和される反面、データ検証や一貫性の維持がやや難しくなることがあります。またデータ量が増えるとパフォーマンスに影響が出ることがあります。そのため、DB カラムに JSON を採用するかは検討が必要です。
今回紹介したような演算子を活用することで、複雑な絞り込み条件にも対応可能です。
業務での使用例や知見があれば、ぜひコメントで共有してください！
参考文献
PR
HRBrain では一緒に働いてくれる仲間を募集しています。興味がありましたら、ぜひご応募ください。