SQL JSON — 抽出・配列展開・包含検索の基礎

QUESTION 1

json と jsonb — 意味が同じJSONを突き合わせる

jsonb型キャスト正規化

前提知識 ▼

PostgreSQLのJSON型は2種類あります。json は投入されたテキストをそのまま保持し、jsonb は解析済みのバイナリとして保持します。jsonb になった時点で、空白は詰められ、キーは内部順序へ並べ替えられ、重複キーは最後の値だけが残ります。

SELECT * FROM table_name
WHERE  json_col::jsonb = '{"key": "value"}'::jsonb;  -- 正規化してから突き合わせる

json 型には等価演算子がない：json_col = '...'::json は「演算子が存在しない」エラーになります。テキストとして ::text で比較すると、今度は空白やキー順の違いで一致しません。

問題 ▼

api_logs テーブルから、payload が {"status":"ok","code":200} と意味的に同じ行を取得してください。payload は json 型で、書式もキー順も揃っていません。取得列は log_id, payload、log_id 昇順で返してください。

使用テーブル

▸ api_logs

log_id	payload（json）
1	{"status": "ok", "code": 200}
2	{"code":200,"status":"ok"}
3	{"status":"ng","code":200,"status":"ok"}
4	{"status": "error", "code": 500}
5	{"status": "ok"}

期待出力 ▼

log_id	payload
1	{"status": "ok", "code": 200}
2	{"code":200,"status":"ok"}
3	{"status":"ng","code":200,"status":"ok"}

模範解答コード ▼

SELECT
  log_id, payload
FROM   api_logs
WHERE  payload::jsonb = '{"code": 200, "status": "ok"}'::jsonb  -- 両辺を正規化して比較
ORDER BY log_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM api_logs                → 5行読み込み
  2. WHERE payload::jsonb = ...   → 正規化して突き合わせ、3行に絞り込み
  3. SELECT log_id, payload       → 2列を選択（payload は元のテキストのまま）
  4. ORDER BY log_id              → log_id の昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  log_id, payload
FROM   api_logs
WHERE  payload::jsonb = '{"code": 200, "status": "ok"}'::jsonb
ORDER BY log_id;

LEGEND ▼

データ取得・読込対象

① FROM api_logs ▼

FROM api_logsapi_logs 全5行を読み込みます。payload は json 型なので、投入されたテキストがそのまま保持されています。log 3 には status キーが2回現れている点に注目してください。

1 / 4

log_id	payload（json）
1	{"status": "ok", "code": 200}
2	{"code":200,"status":"ok"}
3	{"status":"ng","code":200,"status":"ok"}
4	{"status": "error", "code": 500}
5	{"status": "ok"}

全 5行読込

学習ポイント

比較・検索するなら jsonb：等価比較、包含演算子 @>、GINインデックスはすべて jsonb 側の機能です。json にあるのは「入力テキストを1文字も変えずに保存する」ことだけで、監査ログのように原文保持が要件のときに選びます。

正規化の3つの効果：jsonb への変換で、空白の差、キー順の差、重複キーが同時に吸収されます。逆に言えば、この3つを区別したいなら jsonb にしてはいけません。

重複キーは後勝ち：log 3 の status は ng ではなく ok になります。JSONの仕様上は重複キーの扱いが未定義なので、json 型のまま関数で取り出したときも最後の値が返ります。

アンチパターン

テキストとして比較する：payload::text = '{"status":"ok","code":200}' は log 2 のような書式違いを取りこぼします。アプリ側が生成した空白の有無に結果が左右される、再現しにくい不具合になります。

毎回キャストして絞り込む：列が json 型のままでは、payload::jsonb に索引が効きません。検索キーとして使う列は最初から jsonb で持つか、式インデックスを張ります。

実務コラム：JSONで持つか、列に開くか

スキーマが決まらない属性や、外部APIのレスポンス原文はJSONで持つ価値があります。一方、必ず存在して必ず検索される項目（ステータス、テナントID、金額）は通常の列に開くほうが、制約・型・索引・統計情報がすべて効きます。「全部JSONに入れておけば後で困らない」は、数か月後に集計クエリが遅くなってから気付く選択です。判断に迷ったら、WHERE句に出てくるかを基準にします。

QUESTION 2

矢印演算子 — JSON値として取るかテキストとして取るか

jsonb型キャストテキスト抽出

前提知識 ▼

キーの取り出しには2つの矢印演算子を使います。-> はJSON値（jsonb）を返し、->> はテキストを返します。文字列の値では、前者は引用符付き、後者は引用符なしになります。

SELECT jsonb_col ->  'key'            AS as_json,  -- "value" （JSON値）
       jsonb_col ->> 'key'            AS as_text,  -- value   （テキスト）
       (jsonb_col ->> 'num_key')::int AS as_num -- 数値として扱う
FROM   table_name;

数値比較には ->> とキャスト：jsonb_col -> 'num_key' >= 30 は jsonb と整数の比較になり、そのままでは意図した数値比較になりません。->> でテキストにしてから ::int でキャストします。

問題 ▼

users テーブルから、年齢が30歳以上の会員を取得してください。profile は jsonb 型です。取得列は user_id, name, age、user_id 昇順で返してください。name は引用符の付かないテキスト、age は数値にします。

使用テーブル

▸ users

user_id	profile（jsonb）
101	{"name": "佐藤", "age": 34, "city": "東京"}
102	{"name": "鈴木", "age": 28, "city": "大阪"}
103	{"name": "高橋", "age": 41, "city": "東京"}
104	{"name": "田中", "age": 30, "city": "福岡"}

期待出力 ▼

user_id	name	age
101	佐藤	34
103	高橋	41
104	田中	30

模範解答コード ▼

SELECT
  user_id,
  profile ->> 'name'       AS name,  -- 引用符なしのテキスト
  (profile ->> 'age')::int AS age   -- テキスト経由で数値へ
FROM   users
WHERE  (profile ->> 'age')::int >= 30
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users                     → 4行読み込み
  2. WHERE (profile->>'age')::int   → 数値へ揃えて 30 以上に絞り込み
  3. SELECT ->> でテキスト・数値へ    → 3列を選択
  4. ORDER BY user_id               → user_id の昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  user_id,
  profile ->> 'name'       AS name,
  (profile ->> 'age')::int AS age
FROM   users
WHERE  (profile ->> 'age')::int >= 30
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM users ▼

FROM usersusers 全4行を読み込みます。profile は jsonb 型で、name・age・city の3キーを持ちます。

1 / 4

user_id	profile（jsonb）
101	{"name": "佐藤", "age": 34, "city": "東京"}
102	{"name": "鈴木", "age": 28, "city": "大阪"}
103	{"name": "高橋", "age": 41, "city": "東京"}
104	{"name": "田中", "age": 30, "city": "福岡"}

全 4行読込

学習ポイント

矢の数＝返る型：矢が1本の -> は jsonb、2本の ->> は text を返します。さらに掘るなら ->、値として使うなら ->>、と使い分けます。

キャストは ->> の後：(profile -> 'age')::int でも動きますが、jsonbからの直接キャストは値が数値でないときにエラーになります。->> を挟む形を既定にしておくと、後述の失敗のしかたが揃います。

存在しないキーは NULL：どちらの演算子も、キーが無ければエラーではなく NULL を返します。WHERE で使うと、その行は静かに落ちます。

アンチパターン

テキストのまま大小比較する：profile ->> 'age' >= '30' は文字列比較になり、'9' > '30' のような順序になります。件数は返るので、値を確かめるまで気付けません。

-> の結果を表示に使う：profile -> 'name' をそのまま画面へ出すと、引用符付きの "佐藤" が表示されます。表示・連結・比較はすべて ->> 側です。

実務コラム：JSON内の値は型が保証されない

通常の列なら int に入らない値は投入時に弾かれますが、JSONの中身は「数値のはずの文字列」でも保存できてしまいます。(profile ->> 'age')::int は、そういう1行が混ざった瞬間にクエリ全体がエラーで落ちます。安全に倒すなら、PostgreSQL 16 以降の ::int の代わりに jsonb_typeof(profile -> 'age') = 'number' で選別するか、投入側に CHECK 制約を置きます。

QUESTION 3

パス取り出し — ネストしたオブジェクトと配列の要素を指す

ネストパス指定NULL安全

前提知識 ▼

矢印演算子はつなげられます。連結すると1階層ずつ、パス演算子 #>> なら経路を配列でまとめて指定できます。配列の添字は0始まりです。

SELECT jsonb_col -> 'parent' ->> 'child'     AS chained,     -- 1階層ずつたどる
       jsonb_col #>> '{parent, child}'      AS by_path,     -- 経路をまとめて指定
       jsonb_col #>> '{array_key, 0, name}' AS first_name   -- 配列の先頭要素
FROM   table_name;

途中で切れても NULL：経路の途中のキーが存在しない場合、エラーにはならず NULL が返ります。#> はJSON値、#>> はテキストを返す点は矢印演算子と同じです。

問題 ▼

orders テーブルから、配送先の都市と明細の先頭商品名を取り出してください。都市は customer.address.city、先頭商品名は items 配列の0番目の name です。取得列は order_id, city, first_item、order_id 昇順で返してください。

使用テーブル

▸ orders

order_id	detail（jsonb）
1	{"customer": {"id": 101, "address": {"city": "東京"}}, "items": [{"name": "マウス", "qty": 2}, {"name": "キーボード", "qty": 1}]}
2	{"customer": {"id": 102, "address": {"city": "大阪"}}, "items": [{"name": "モニター", "qty": 4}]}
3	{"customer": {"id": 103}, "items": [{"name": "マウス", "qty": 3}]}

期待出力 ▼

order_id	city	first_item
1	東京	マウス
2	大阪	モニター
3	NULL	マウス

模範解答コード ▼

SELECT
  order_id,
  detail #>> '{customer, address, city}' AS city,       -- 経路が切れたら NULL
  detail #>> '{items, 0, name}'           AS first_item  -- 配列は0始まり
FROM   orders
ORDER BY order_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders                  → 3行読み込み
  2. SELECT #>> でパスをたどる      → 3列を選択（経路が無い行は NULL）
  3. ORDER BY order_id            → order_id の昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  order_id,
  detail #>> '{customer, address, city}' AS city,
  detail #>> '{items, 0, name}'          AS first_item
FROM   orders
ORDER BY order_id;

LEGEND ▼

データ取得・読込対象

① FROM orders ▼

FROM ordersorders 全3行を読み込みます。detail はオブジェクトの中にオブジェクト、さらに配列を持つ入れ子構造です。order 3 だけ customer に address がありません。

1 / 3

order_id	detail（jsonb）
1	{"customer": {"id": 101, "address": {"city": "東京"}}, "items": [ … ]}
2	{"customer": {"id": 102, "address": {"city": "大阪"}}, "items": [ … ]}
3	{"customer": {"id": 103}, "items": [ … ]}

全 3行読込

学習ポイント

連結とパスは等価：detail -> 'customer' -> 'address' ->> 'city' と detail #>> '{customer, address, city}' は同じ結果です。階層が3つ以上ならパス演算子のほうが読みやすくなります。

配列の添字も経路の一部：パスの中の 0 は配列の添字として解釈されます。負の添字も使え、-1 は末尾の要素を指します。

NULLで返るのは安全側：経路が途中で切れてもクエリは止まりません。ただし「値が無い」と「経路が無い」を区別できないので、必須項目の欠落を検知したいなら jsonb_path_exists や ? で明示的に確かめます。

アンチパターン

最後だけ -> でつなぐ：detail -> 'customer' -> 'address' -> 'city' は "東京" という引用符付きのJSON値を返します。最終段だけ ->>／#>> にするのが定型です。

配列を1始まりで数える：SQLの配列は1始まりですが、JSON配列の添字は0始まりです。'{items, 1, name}' は先頭ではなく2番目を指し、要素が1つしかない order 3 では NULL になります。

実務コラム：先頭要素に意味を持たせない

「配列の0番目が代表値」という設計は、書き込み順に依存します。並び順を変える改修や、要素の追加・削除が入った瞬間に、既存クエリの意味が静かに変わります。代表を表したいなら {"primary": true} のようなフラグを要素側に持たせ、添字ではなく条件で選ぶほうが壊れません。添字指定は「配列がJSONのまま渡ってくる外部データを、とりあえず覗く」用途に留めます。

QUESTION 4

jsonb_array_elements — JSON配列を行に展開して集計する

配列展開GROUP BYLATERAL

前提知識 ▼

jsonb_array_elements はJSON配列の各要素を1行ずつ返す集合返し関数です。FROM 句に置き、元の行と横に結合することで、配列の中身を通常の行として集計できます。

SELECT elem ->> 'key'
FROM   table_name t
CROSS JOIN LATERAL jsonb_array_elements(t.jsonb_col -> 'array_key') AS elem;
-- 配列が3要素なら、元の1行が3行に増える

LATERAL は左の行を参照する宣言：関数の引数に左側のテーブルの列を使うため LATERAL が必要です。FROM t, jsonb_array_elements(...) というカンマ結合でも同じ意味になりますが、意図が読み取りやすい前者を既定にします。要素をテキストの配列として扱うなら jsonb_array_elements_text を使います。

問題 ▼

orders テーブルの detail にある items 配列を展開し、商品ごとの合計数量を求めてください。取得列は item_name, total_qty、total_qty の降順で返してください。

使用テーブル

▸ orders

order_id	detail（jsonb）
1	{"items": [{"name": "マウス", "qty": 2}, {"name": "キーボード", "qty": 1}]}
2	{"items": [{"name": "モニター", "qty": 4}]}
3	{"items": [{"name": "マウス", "qty": 3}]}

期待出力 ▼

item_name	total_qty
マウス	5
モニター	4
キーボード	1

模範解答コード ▼

SELECT
  item ->> 'name'              AS item_name,
  SUM((item ->> 'qty')::int) AS total_qty
FROM   orders o
CROSS JOIN LATERAL jsonb_array_elements(o.detail -> 'items') AS item  -- 配列を行へ
GROUP BY item ->> 'name'
ORDER BY total_qty DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders o                      → 3行読み込み
  2. CROSS JOIN LATERAL jsonb_array_...  → items を展開して4行へ
  3. GROUP BY item->>'name'             → 商品名で3グループ
  4. SELECT SUM((item->>'qty')::int)    → グループごとに数量を合計
  5. ORDER BY total_qty DESC            → 合計数量の降順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  item ->> 'name'              AS item_name,
  SUM((item ->> 'qty')::int) AS total_qty
FROM   orders o
CROSS JOIN LATERAL jsonb_array_elements(o.detail -> 'items') AS item
GROUP BY item ->> 'name'
ORDER BY total_qty DESC;

LEGEND ▼

データ取得・読込対象

① FROM orders ▼

FROM orders oorders 全3行を読み込みます。items 配列の長さは順に 2・1・1 で、合計4要素あります。

1 / 4

order_id	detail -> 'items'
1	[{"name": "マウス", "qty": 2}, {"name": "キーボード", "qty": 1}]
2	[{"name": "モニター", "qty": 4}]
3	[{"name": "マウス", "qty": 3}]

全 3行読込

学習ポイント

展開＝行数が増える結合：集合返し関数は「1行を配列の長さぶんの行に増やす結合」です。展開後は通常の行なので、WHERE・GROUP BY・JOIN がそのまま使えます。

GROUP BY にも同じ式を書く：集約していない item ->> 'name' は GROUP BY に必要です。別名 item_name でグループ化することもできますが、式をそのまま書くほうが移植性があります。

空配列と欠損の違い：items が空配列 [] の行、あるいは items キー自体が無い行は、CROSS JOIN なので結果から消えます。元の行を残したいなら LEFT JOIN LATERAL … ON true にします。

アンチパターン

SELECT句で展開する：SELECT jsonb_array_elements(detail -> 'items') … は書けてしまいますが、他の集合返し関数や集約と混ぜたときの行数が直感と合いません。展開は FROM 句で行い、行が増えることを構文上も見えるようにします。

展開後に元の行を数える：展開すると order 1 が2行になるため、COUNT(DISTINCT o.order_id) ではなく COUNT(*) を使うと注文件数が水増しされます。行数の意味が展開の前後で変わる点を意識します。

実務コラム：展開が毎回走るコスト

配列展開は行を増やしてから集計するため、対象行が多いほど重くなります。索引で絞れるのは展開前の条件だけなので、WHERE は展開前のテーブル側へ寄せるのが基本です。明細の集計を毎日のように実行するなら、そもそも明細を子テーブルとして正規化するか、集計結果をマテリアライズドビューへ落とすほうが素直です。JSON配列は「めったに集計しない付随データ」に向いています。

QUESTION 5

存在と包含 — キーの有無と部分構造でJSONを絞り込む

存在演算子包含演算子GINインデックス

前提知識 ▼

JSONの絞り込みには専用の演算子があります。? はトップレベルにキーが存在するか、@> は右辺の構造を左辺が含むかを判定します。どちらも jsonb 専用です。

SELECT * FROM table_name
WHERE  jsonb_col ? 'key'                          -- キーが存在する
  AND  jsonb_col @> '{"array_key": ["value"]}';  -- 配列が value を含む

@> は部分集合の判定：右辺に書いた分だけが満たされていれば真です。配列は順序も要素数も問わず、含まれてさえいれば一致します。オブジェクトも同じで、右辺に書かなかったキーは無視されます。

問題 ▼

products テーブルから、color キーを持ち、かつ tags に "sale" を含む商品を取得してください。attrs は jsonb 型です。取得列は product_id, color、product_id 昇順で返してください。

使用テーブル

▸ products

product_id	attrs（jsonb）
1	{"color": "red", "size": "M", "tags": ["sale", "new"]}
2	{"color": "blue", "size": "L"}
3	{"size": "M", "tags": ["sale"]}
4	{"color": "red", "size": "S", "tags": ["new"]}
5	{"color": "green", "size": "M", "tags": ["limited", "sale"]}

期待出力 ▼

product_id	color
1	red
5	green

模範解答コード ▼

SELECT
  product_id,
  attrs ->> 'color' AS color
FROM   products
WHERE  attrs ?  'color'                -- トップレベルに color キーがある
  AND  attrs @> '{"tags": ["sale"]}'  -- tags が sale を含む（順序不問）
ORDER BY product_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM products                → 5行読み込み
  2. WHERE ? と @> の AND          → 2行に絞り込み
  3. SELECT attrs->>'color'       → 2列を選択
  4. ORDER BY product_id          → product_id の昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  product_id,
  attrs ->> 'color' AS color
FROM   products
WHERE  attrs ?  'color'
  AND  attrs @> '{"tags": ["sale"]}'
ORDER BY product_id;

LEGEND ▼

データ取得・読込対象

① FROM products ▼

FROM productsproducts 全5行を読み込みます。product 2 は tags キーを持たず、product 3 は color キーを持ちません。product 5 の tags は sale が2番目に入っています。

1 / 3

product_id	attrs（jsonb）
1	{"color": "red", "size": "M", "tags": ["sale", "new"]}
2	{"color": "blue", "size": "L"}
3	{"size": "M", "tags": ["sale"]}
4	{"color": "red", "size": "S", "tags": ["new"]}
5	{"color": "green", "size": "M", "tags": ["limited", "sale"]}

全 5行読込

学習ポイント

? はトップレベル限定：attrs ? 'city' はネストの中までは見ません。深い位置のキーを確かめるなら attrs -> 'customer' ? 'city' のように、対象のオブジェクトまで降りてから使います。

@> は構造ごと書ける：ネストした条件も attrs @> '{"customer": {"address": {"city": "東京"}}}' のように、探したい形をそのまま書けます。条件が増えても演算子は1つのままです。

GINインデックスが効く：CREATE INDEX ON products USING GIN (attrs); を張ると @>・?・?|・?& が索引で処理されます。->> による絞り込みはこの索引では加速しないため、検索条件に合わせて選びます。

アンチパターン

配列をテキストで部分一致させる：attrs ->> 'tags' LIKE '%sale%' は動きますが、"sale_end" のような別の値にも当たり、索引も効きません。配列の要素判定は @> です。

キーの有無を IS NOT NULL で判定する：attrs ->> 'color' IS NOT NULL は、値が JSON の null（{"color": null}）のときも偽になります。「キーはあるが値が null」を区別したいなら ? を使います。

実務コラム：? 演算子とプレースホルダの衝突

JDBCをはじめ、多くのドライバは ? をバインド変数の記号として解釈します。そのため attrs ? 'color' がそのまま送れず、?? とエスケープする、あるいは関数形の jsonb_exists(attrs, 'color') へ書き換える、という対処が必要になります。演算子と関数が1対1で用意されているのはこのためで、@> にも jsonb_contains があります。アプリから発行するクエリでは、最初から関数形で書いておくと移植で困りません。