PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day12 JSON操作②

Day12 後半のゴール

「“JSONを条件にした絞り込み・集計”を自信を持って書けるようにする」

前半で「JSON配列を行に展開して、列として扱う」感覚までは来ました。
後半では、それを一歩進めて「条件で絞る」「集計する」「CTEと組み合わせて読みやすくする」ところまで持っていきます。

ゴールは、「JSONに入っている情報を前提に、SQLだけで“ちゃんとした分析クエリ”を書ける」状態です。

例題1：iOSデバイスでログインしたことがあるユーザー一覧

展開＋フィルタの基本パターンを固める

前半の users.devices をそのまま使います。
やりたいことは、「iOSデバイスでログインしたことがあるユーザーを一覧したい」です。

まずは「ユーザー×デバイス」に展開します。

WITH user_devices AS (
  SELECT
    u.id    AS user_id,
    u.email AS email,
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT
  DISTINCT user_id,
  email
FROM user_devices
WHERE d->>'os' = 'iOS'
ORDER BY user_id;

WITH user_devices AS (
  SELECT
    u.id    AS user_id,
    u.email AS email,
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT
  DISTINCT user_id,
  email
FROM user_devices
WHERE d->>'os' = 'iOS'
ORDER BY user_id;

ここでの流れを言葉で整理します。

user_devices で、「ユーザー1人が複数デバイス行を持つ」形に展開する。
WHERE d->>'os' = 'iOS' で、「そのデバイスがiOSである行だけ」に絞る。
同じユーザーが複数iOSデバイスを持っていても、DISTINCT で1ユーザー1行にする。

この「展開 → WHEREでJSONの中身を条件にする → DISTINCTでユーザー単位に戻す」という流れは、かなり頻出です。
「JSONの中身でユーザーを絞りたい」ときの基本パターンとして、体に入れておいてほしいところです。

例題2：OS別のデバイス数を集計する

展開＋グループ化で“JSONの中身を集計対象にする”

次は、「OS別に、全体で何台のデバイスがあるか」を数えてみます。
これは「ユーザー単位」ではなく、「デバイス単位」の集計です。

WITH user_devices AS (
  SELECT
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT
  d->>'os' AS os,
  COUNT(*) AS device_count
FROM user_devices
GROUP BY d->>'os'
ORDER BY device_count DESC;

WITH user_devices AS (
  SELECT
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT
  d->>'os' AS os,
  COUNT(*) AS device_count
FROM user_devices
GROUP BY d->>'os'
ORDER BY device_count DESC;

ここでのポイントはシンプルです。

展開された1デバイス行から、d->>'os' を「グループキー」として使う。
COUNT(*) で「そのOSのデバイス数」を数える。

結果は例えばこうなります。

os       | device_count
---------+-------------
iOS      | 120
Android  | 80
Windows  | 40
macOS    | 15
...

「JSONの中にある属性（os）を、普通のカラムと同じように GROUP BY できる」という感覚が、ここでしっかり定着してくるはずです。

例題3：“危険なOS”を使っているユーザーを検出する

セキュリティ視点でのJSONクエリの使いどころ

少しセキュリティ寄りの例も入れておきます。
例えば、「サポート終了した古いOS（例: Windows 7）からログインしているユーザーを検出したい」とします。

devices の中に、こういうデータが混ざっているイメージです。

{
  "type": "pc",
  "os": "Windows 7",
  "last_login": "2024-06-01T10:00:00Z"
}

このときのクエリはこう書けます。

WITH user_devices AS (
  SELECT
    u.id    AS user_id,
    u.email AS email,
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT DISTINCT
  user_id,
  email
FROM user_devices
WHERE d->>'os' = 'Windows 7'
ORDER BY user_id;

WITH user_devices AS (
  SELECT
    u.id    AS user_id,
    u.email AS email,
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT DISTINCT
  user_id,
  email
FROM user_devices
WHERE d->>'os' = 'Windows 7'
ORDER BY user_id;

やっていることは例題1と同じ構造ですが、意味合いが変わります。

「JSONの中に潜んでいる“リスク要因”を、SQLであぶり出す」

という使い方です。

さらに、「最後のログイン日時も一緒に見たい」なら、こう拡張できます。

WITH user_devices AS (
  SELECT
    u.id    AS user_id,
    u.email AS email,
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT
  user_id,
  email,
  d->>'os'         AS os,
  d->>'last_login' AS last_login
FROM user_devices
WHERE d->>'os' = 'Windows 7'
ORDER BY user_id, last_login DESC;

WITH user_devices AS (
  SELECT
    u.id    AS user_id,
    u.email AS email,
    jsonb_array_elements(u.devices) AS d
  FROM users u
)
SELECT
  user_id,
  email,
  d->>'os'         AS os,
  d->>'last_login' AS last_login
FROM user_devices
WHERE d->>'os' = 'Windows 7'
ORDER BY user_id, last_login DESC;

これで、「どのユーザーが、いつWindows 7からログインしたか」が一覧できます。
JSONをちゃんと構造として扱っているからこそ、こういう“セキュリティチェックSQL”も書けるわけです。

例題4：JSONの中身を“部分一致”ではなく“構造として”検索する

@> を使って「条件のJSONを含むか」を見る

Day11 で少し触れた @> を、配列展開なしで使うパターンも押さえておきます。
例えば、「devices の中に、type=mobile かつ os=iOS のデバイスがあるユーザー」を探したいとします。

devices の中の1要素はこうです。

{
  "type": "mobile",
  "os": "iOS",
  "last_login": "2024-06-10T12:00:00Z"
}

このとき、jsonb_array_elements で展開しても書けますが、
@> を使うと、配列ごと一気に条件を書けます。

SELECT
  id,
  email
FROM users
WHERE devices @> '[
  {"type": "mobile", "os": "iOS"}
]'::jsonb;

SELECT
  id,
  email
FROM users
WHERE devices @> '[
  {"type": "mobile", "os": "iOS"}
]'::jsonb;

devices @> '[{...}]'::jsonb は、「devices 配列の中に、少なくとも1つは {"type":"mobile","os":"iOS"} を含む要素があるか？」をチェックします。

ここでの重要ポイントは、「文字列のLIKEではなく、“JSONとしての構造”で条件を書いている」ことです。

LIKE '%"type":"mobile","os":"iOS"%' のような書き方だと、

順番が違うとマッチしない。
余計な場所に同じ文字列があってもマッチしてしまう。

といった問題が出ますが、@> なら「JSONオブジェクトとして含んでいるか」を見てくれるので、はるかに安全で正確です。

例題5：JSONクエリ＋インデックスの入り口

「jsonb_ops / jsonb_path_ops を意識するときの感覚」

Day12の段階では深掘りしすぎなくていいですが、「JSONクエリはインデックスとも相性がいい」という話だけ触れておきます。

例えば、さっきのようなクエリを頻繁に打つ場合、

SELECT id, email
FROM users
WHERE devices @> '[{"type": "mobile", "os": "iOS"}]'::jsonb;

SELECT id, email
FROM users
WHERE devices @> '[{"type": "mobile", "os": "iOS"}]'::jsonb;

この devices に対して、GINインデックスを張ることができます。

CREATE INDEX idx_users_devices_gin
ON users
USING GIN (devices);

CREATE INDEX idx_users_devices_gin
ON users
USING GIN (devices);

これで、@> を使った検索がかなり速くなります。

ここで覚えておいてほしいのは、「jsonbを使っていると、こういう“JSON専用インデックス”が使える」という事実です。
TEXTにJSONを突っ込んでLIKE検索しているだけだと、この世界には来られません。

パフォーマンスチューニングの細かい話はもっと後のDayに回すとして、
「JSONクエリは、ちゃんと設計すれば速くできる」という安心感だけ持っておいてください。

JSONクエリを書くときの“思考テンプレ”

「展開するか、@>で一気に見るかをまず決める」

ここまでの例を踏まえて、JSONクエリを書くときの頭の流れを整理します。

「配列の中身を1要素ずつ見て、集計したい・条件を細かく付けたい」
→ jsonb_array_elements で展開して、d->>'key' で列にしてから、普通のSQLで扱う。

「配列の中に“この条件のオブジェクトが1つでもあるか”だけを見たい」
→ @> で「条件のJSONを含むか」を直接見る。

どちらも、「JSONを文字列として扱う」のではなく、「構造として扱う」ことを前提にしています。
ここが、PostgreSQLのJSON機能を“ちゃんと使っているかどうか”の分かれ目です。

Day12 後半のまとめ

JSONクエリの実戦パターンは、「まず jsonb_array_elements で配列を行に展開し、展開された1要素から d->>'os' や d->>'type' のように列を取り出して、あとは普通のSQLと同じように WHERE・GROUP BY・ORDER BY を使う」流れと、「配列全体に対して devices @> '[{...}]'::jsonb のように“このJSONオブジェクトを含むか”を一気に判定する」流れの2本柱で考える。
前者は「iOSデバイスでログインしたユーザー一覧」「OS別デバイス数の集計」「古いOSを使っているユーザーの検出」のように“中身を細かく見て集計・フィルタする”場面で使い、後者は「type=mobile かつ os=iOS のデバイスを持つユーザー」のように“条件に合う要素が1つでもあるか”を調べる場面で使う。
jsonb＋これらのクエリパターンに、必要に応じてGINインデックスを組み合わせることで、「柔軟にJSONを持ちながら、構造を前提に安全かつ高速に検索・集計できる」状態に近づく――これが Day12 後半の着地点になる。