Day12 前半のゴール
「“JSONの世界”をSQLのテーブルっぽく扱う感覚を持つ」
Day11 では「JSONをカラムに入れる」「中身を取り出す」「中身で検索する」ところまで来ました。
Day12 はその一歩先、「JSONの中身を“行・列”っぽく展開してクエリする」イメージを作っていきます。
前半のゴールはこうです。
JSON配列を「1要素＝1行」に展開するイメージを持てる。jsonb_array_elements を使って「JSONの中身をFROM句側に持ってくる」感覚をつかめる。
「ユーザー1人＝1行」＋「JSONの中の要素＝複数行」という構造を、SQLで表現できるイメージを持つ。
今日の前提となるテーブルイメージ
「ユーザーが“複数のデバイス”をJSONで持っている」
具体的なイメージを固定するために、こんなテーブルを想像してください。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
devices JSONB NOT NULL DEFAULT '[]'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
devices には、「そのユーザーがログインしたことのあるデバイス情報」が配列で入っているとします。
あるユーザーの devices の例：
[
{
"type": "pc",
"os": "Windows",
"last_login": "2024-06-01T10:00:00Z"
},
{
"type": "mobile",
"os": "iOS",
"last_login": "2024-06-10T12:00:00Z"
}
]
ここでやりたいのは、例えばこういうことです。
ユーザーごとに「何台のデバイスを持っているか」を数えたい。
「iOSデバイスでログインしたことがあるユーザー」を探したい。
「デバイスごとに行を1行ずつ出して、集計したい」。
これを、devices を“テーブルっぽく展開”することで実現していきます。
JSON配列を“行に展開する”とは何か
「1ユーザー1行 → 1デバイス1行」に変換するイメージ
今のままだと、users テーブルは「1ユーザー＝1行」です。
でも devices の中には「複数のデバイス」が入っています。
視覚的に書くと、こういう感じです。
ユーザー行：
id: 1
email: taro@example.com
devices: [
{...}, -- デバイスA
{...} -- デバイスB
]
これを、「デバイスごとに1行」に変換したい。
user_id: 1, device_type: pc, os: Windows, last_login: ...
user_id: 1, device_type: mobile, os: iOS, last_login: ...
この「JSON配列を“行にバラす”」のが、jsonb_array_elements です。
ここが Day12 の一番大事なポイントです。
jsonb_array_elements の基本形
「FROM句の中でJSONを“テーブル化”する」
jsonb_array_elements は、「JSON配列を受け取って、1要素ずつ返す関数」です。
PostgreSQLでは、これを FROM 句の中で使うことで、「JSONをテーブルのように扱う」ことができます。
まずは最小の例から。
SELECT jsonb_array_elements(
'[
{"type": "pc", "os": "Windows"},
{"type": "mobile", "os": "iOS"}
]'::jsonb
);
これを実行すると、結果は2行になります。
1行目：{"type": "pc", "os": "Windows"}
2行目：{"type": "mobile", "os": "iOS"}
つまり、「配列の中身を1要素＝1行」にしてくれています。
これを users テーブルと組み合わせると、こうなります。
SELECT
u.id AS user_id,
u.email,
d
FROM users u
CROSS JOIN LATERAL jsonb_array_elements(u.devices) AS d;
ここでのポイントを丁寧に分解します。
jsonb_array_elements(u.devices)
ユーザーごとの devices 配列を、「1要素＝1行」に展開する。
CROSS JOIN LATERAL
「左側の行（usersの1行）を使って、右側の関数を呼び出す」ための書き方。
「ユーザー1行 → デバイス複数行」に増やすイメージ。
AS d
展開された1要素（JSONオブジェクト）に d という名前を付けている。
結果はこういうイメージになります。
user_id | email | d
--------+-------------------+-----------------------------------------
1 | taro@example.com | {"type": "pc", "os": "Windows", ...}
1 | taro@example.com | {"type": "mobile", "os": "iOS", ...}
2 | hanako@example... | {"type": "mobile", "os": "Android", ...}
...
「1ユーザー1行」だったものが、「ユーザー×デバイスの組み合わせで複数行」に増えています。
これができると、一気に“JSONの中身をSQLで集計する”世界に入れます。
展開したJSONから、さらに中身を取り出す
「d->>’type’ でデバイス種別を列にする」
さっきのクエリで、d は「1デバイス分のJSONオブジェクト」です。
ここから type, os, last_login を取り出して、普通の列として扱ってみます。
SELECT
u.id AS user_id,
u.email,
d->>'type' AS device_type,
d->>'os' AS device_os,
d->>'last_login' AS device_last_login
FROM users u
CROSS JOIN LATERAL jsonb_array_elements(u.devices) AS d;
ここでやっていることは、
jsonb_array_elements で「デバイス1つ分のJSON」を d として受け取る。d->>'type' で "pc" や "mobile" をテキストとして取り出す。d->>'os' で "Windows" や "iOS" を取り出す。
結果は、完全に「普通のテーブル」のように見えます。
user_id | email | device_type | device_os | device_last_login
--------+-------------------+-------------+-----------+-------------------
1 | taro@example.com | pc | Windows | 2024-06-01T...
1 | taro@example.com | mobile | iOS | 2024-06-10T...
...
ここまで来ると、「JSONだけど、もうほぼ普通のテーブルと同じように扱える」感覚になってきます。
Day12 前半では、この「JSON配列 → 行に展開 → 中身を列として取り出す」という流れを、しっかりイメージできればOKです。
JSON展開＋集計の最初の例
「ユーザーごとのデバイス数を数える」
展開できたら、次は「集計」です。
例えば、「ユーザーごとに何台のデバイスを持っているか」を数えてみます。
WITH user_devices AS (
SELECT
u.id AS user_id,
jsonb_array_elements(u.devices) AS d
FROM users u
)
SELECT
user_id,
COUNT(*) AS device_count
FROM user_devices
GROUP BY user_id
ORDER BY user_id;
ここでの流れは、
user_devices CTEで、「ユーザー×デバイス」の行を作る。
その上で、user_id ごとに COUNT(*) する。
結果はこうなります。
user_id | device_count
--------+-------------
1 | 2
2 | 1
3 | 3
...
「JSONの中身を展開してから集計する」というパターンは、
ログ・設定・属性など、いろんな場面で使える基本形です。
JSONクエリの“考え方の順番”
「1. 展開する → 2. 列にする → 3. 集計・フィルタする」
ここまでの内容を、「頭の中の手順」として整理しておきます。
まず、「JSON配列をそのままWHEREでいじろう」としない。
代わりに、こう考えます。
最初に、「配列を行に展開する」（jsonb_array_elements）。
次に、「展開された1要素から必要な値を列として取り出す」（->>）。
最後に、「普通のテーブルと同じように、WHERE / GROUP BY / ORDER BY で扱う」。
この3ステップを踏むことで、「JSONの世界」を「SQLの世界」に引き上げられます。
Day12 前半では、特にこの「順番の感覚」が大事です。
いきなり複雑なJSONクエリを書こうとせず、「まず展開して、テーブルっぽくしてから考える」と覚えておいてください。
Day12 前半のまとめ
JSON配列を「1要素＝1行」にバラすには、jsonb_array_elements(jsonb) を FROM 句（CROSS JOIN LATERAL）で使い、users テーブルの devices のような「1ユーザーが複数要素を持つJSON」を「ユーザー×要素」の複数行に展開する。
展開された1要素はJSONオブジェクトなので、それを d->>'type' や d->>'os' のように ->> で列に変換すれば、もはや普通のテーブルと同じように SELECT・WHERE・GROUP BY で扱えるようになり、「ユーザーごとのデバイス数を数える」といった集計も素直に書ける。
JSONクエリの基本は、「1. 配列を行に展開する → 2. 展開された要素から列を取り出す → 3. いつものSQLで集計・フィルタする」という3ステップで考えること――これが Day12 前半の着地点になる。