【PostgreSQL】JSON データを SQL クエリで絞り込みする方法

本記事は HRBrain Advent Calendar 2024 の 21 日目の記事です。

https://qiita.com/advent-calendar/2024/hrbrain

# はじめに

業務で 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` があるとします。

```sql
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" のレコードを取得したい場合、以下のクエリを使用します。

```sql
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 より大きいレコードを取得するには、以下のクエリを使用します。

```sql
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" が含まれるレコードを取得するには、以下のクエリを使用します。

```sql
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 データにアクセスする場合は、`#>` を使用します。

```sql
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` カラム全体にインデックスを作成する例

```sql
CREATE INDEX idx_sample_table_data ON sample_table USING gin (data);
```

### **2. 特定のキーにインデックスを作成する**

特定のキーを対象としたインデックスを作成する例

```sql
CREATE INDEX idx_sample_table_data_name ON sample_table ((data ->> 'name'));
```

このインデックスを作成すると、`name` を条件にした絞り込みが高速になります。

# まとめ

PostgreSQL では、JSON や JSONB カラムを柔軟に扱うことができます。これにより、ネストされたデータ構造や配列をそのまま保持しつつ、柔軟なクエリを実行できます。
一方で、リレーショナルデータベースの正規化手法と比較すると、JSON を使用することでスキーマの制約が緩和される反面、データ検証や一貫性の維持がやや難しくなることがあります。またデータ量が増えるとパフォーマンスに影響が出ることがあります。そのため、DB カラムに JSON を採用するかは検討が必要です。

今回紹介したような演算子を活用することで、複雑な絞り込み条件にも対応可能です。
業務での使用例や知見があれば、ぜひコメントで共有してください！

# 参考文献

1. [PostgreSQL Documentation - JSON Types](https://www.postgresql.org/docs/current/datatype-json.html)
2. [PostgreSQL Documentation - Index Types](https://www.postgresql.org/docs/current/indexes-types.html)

# PR

HRBrain では一緒に働いてくれる仲間を募集しています。興味がありましたら、ぜひご応募ください。

https://www.hrbrain.co.jp/recruit