SQL Quiz Book

SQL JSON — 部分更新・JSON組み立て・型判定の基礎

基礎JSONjsonb_setjsonb_aggjsonb_eachjsonb_typeofPostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 6

jsonb_set と連結 — JSONの一部だけを書き換える

jsonb_set連結演算子部分更新
前提知識 ▼

JSONの一部だけを差し替えるには jsonb_set(対象, パス, 新しい値) を使います。トップレベルへのキー追加・上書きは連結演算子 || が簡潔です。どちらも新しい jsonb を返すだけで、元の値は書き換えません。

SELECT jsonb_set(jsonb_col, '{parent, child}', 'false'::jsonb)  AS replaced,  -- 指定パスを差し替え
       jsonb_col || '{"key": "value"}'                       AS merged     -- 同名キーは右が優先
FROM   table_name;
親のパスが無いと何も起きない：jsonb_set が作れるのは最後の1段だけです。'{notify, email}' の notify 自体が存在しない行では、エラーにも警告にもならず、対象がそのまま返ります。
問題 ▼

user_settings テーブルの全行について、notify.email を false に変更し、さらにトップレベルへ "theme": "dark" を設定した結果を求めてください。取得列は user_id, settings、user_id 昇順で返してください。

使用テーブル
▸ user_settings
user_idsettings（jsonb）
1{"theme": "light", "notify": {"push": false, "email": true}}
2{"notify": {"push": true, "email": true}}
3{"theme": "light"}
期待出力 ▼
user_idsettings
1{"theme": "dark", "notify": {"push": false, "email": false}}
2{"theme": "dark", "notify": {"push": true, "email": false}}
3{"theme": "dark"}
模範解答コード ▼ 
SELECT
  user_id,
  jsonb_set(settings, '{notify, email}', 'false'::jsonb)  -- notify がある行だけ差し替わる
    || '{"theme": "dark"}' AS settings                    -- トップレベルは上書き or 追加
FROM   user_settings
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM user_settings           → 3行読み込み
  2. SELECT jsonb_set(...)        → notify.email を false へ差し替え
  3. SELECT ... || '{"theme"...}' → theme を上書き or 追加
  4. ORDER BY user_id             → user_id の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT user_id, jsonb_set(settings, '{notify, email}', 'false'::jsonb) || '{"theme": "dark"}' AS settings FROM user_settings ORDER BY user_id;
LEGEND ▼
データ取得・読込対象
① FROM user_settings ▼
FROM user_settingsuser_settings 全3行を読み込みます。user 2 には theme が無く、user 3 には notify がありません。jsonb 列なので、キーは長さ順・バイト順に並び替えられた状態で保持されています。
1 / 4
user_idsettings（jsonb）
1{"theme": "light", "notify": {"push": false, "email": true}}
2{"notify": {"push": true, "email": true}}
3{"theme": "light"}
全 3行 読込
学習ポイント
使い分けの目安：ネストの奥を1点だけ変えるなら jsonb_set、トップレベルのキーを足す・置くなら ||。|| は浅いマージで、同名キーは中身を混ぜずに丸ごと置き換わります。
キーを消すのは演算子：削除は settings - 'theme'（トップレベル）や settings #- '{notify, push}'（パス指定）で書きます。関数ではなく演算子である点が、追加・更新と非対称です。
表示順はキー長→バイト順：jsonb の出力は入力順ではありません。theme が notify より先に出るのはキー長が短いためで、値の意味には関係しません。
アンチパターン
親の存在を確かめずに書き換える：user 3 のように途中のキーが無い行は、静かに更新されません。UPDATE の更新件数は3件と報告されるため、集計するまで気付けない欠落になります。必要なら WHERE settings ? 'notify' で対象を明示します。
読んで、直して、書き戻す：アプリ側でJSON全体を取得して組み立て直し UPDATE する実装は、同時実行で片方の更新が消えます。jsonb_set はサーバ側の1文で完結するので、この競合が起きません。
実務コラム：JSONの部分更新は行全体の書き換え
SQL上は1キーの更新に見えても、PostgreSQLは行のバージョンを丸ごと作り直します。数百KBのJSONを1日に何度も部分更新するテーブルは、更新量とTOASTの読み書きが積み上がり、VACUUMも追いつかなくなります。更新頻度の高い項目は通常の列へ切り出し、JSONには「めったに変わらない付随情報」を残すのが、あとから効いてくる設計です。
1 / 5
問題を選択
QUESTION 7

jsonb_build_object と jsonb_agg — 行からJSONを組み立てる

jsonb_build_objectjsonb_aggAPI向け整形
前提知識 ▼

行をJSONへ変換するには jsonb_build_object(キー, 値, キー, 値, …) を使います。引数はキーと値の交互で、奇数個だとエラーになります。グループ内の値を1つのJSON配列にまとめるのは集約関数 jsonb_agg です。

SELECT jsonb_agg(
         jsonb_build_object('key1', col1, 'key2', col2)
         ORDER BY sort_col                              -- 配列の並びを固定する
       ) AS items
FROM     table_name
GROUP BY group_col;
行全体をそのまま入れるなら to_jsonb：列を選び直さず1行を丸ごとJSONにするなら to_jsonb(t) が使えます。列の追加が自動で反映される反面、内部列まで公開されるため、外部へ返す形は jsonb_build_object で明示するほうが安全です。
問題 ▼

sales テーブルを地域ごとにまとめ、商品と金額の組をJSON配列にしてください。配列の要素は {"product": …, "amount": …} の形で、sale_id の昇順に並べます。取得列は region, items、region 昇順で返してください。

使用テーブル
▸ sales
sale_idregionproductamount
1Eastマウス1500
2Eastキーボード3000
3Westモニター20000
期待出力 ▼
regionitems
East[{"amount": 1500, "product": "マウス"}, {"amount": 3000, "product": "キーボード"}]
West[{"amount": 20000, "product": "モニター"}]
模範解答コード ▼ 
SELECT
  region,
  jsonb_agg(
    jsonb_build_object('product', product, 'amount', amount)
    ORDER BY sale_id                                    -- 配列の並びを一意に決める
  ) AS items
FROM     sales
GROUP BY region
ORDER BY region;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM sales                    → 3行読み込み
  2. GROUP BY region               → 2グループに分割
  3. SELECT jsonb_build_object     → 行ごとにJSONオブジェクトを作成
  4. SELECT jsonb_agg(... ORDER BY sale_id) → グループごとに配列へ集約
  5. ORDER BY region               → region の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT region, jsonb_agg( jsonb_build_object('product', product, 'amount', amount) ORDER BY sale_id ) AS items FROM sales GROUP BY region ORDER BY region;
LEGEND ▼
データ取得・読込対象
① FROM sales ▼
FROM salessales 全3行を読み込みます。East に2行、West に1行が属します。
1 / 4
sale_idregionproductamount
1Eastマウス1500
2Eastキーボード3000
3Westモニター20000
全 3行 読込
学習ポイント
集約の中の ORDER BY：jsonb_agg(expr ORDER BY sort_col) は集約関数専用の構文で、配列内の並びを決めます。書かなければ順序は保証されず、実行計画が変わった日に配列の並びが変わります。
キーは書いた順に並ばない：出力の amount が先に来るのは、jsonb がキー長→バイト順で並べるためです。順序を保ちたいときだけ json_build_object（jsonb ではなく json）を使います。
ネストも同じ関数で作る：値の位置にさらに jsonb_build_object や jsonb_agg を置けば、入れ子のレスポンスを1クエリで組み上げられます。アプリ側でループして組み立てる処理をSQLへ寄せられます。
アンチパターン
文字列連結でJSONを作る：'{"product": "' || product || '"}' は、値に引用符や改行が入った瞬間に壊れたJSONを生成します。エスケープは組み立て関数に任せます。
空グループで NULL を返す：該当行が無いグループでは jsonb_agg は NULL を返します。受け取り側で空配列を期待しているなら COALESCE(jsonb_agg(…), '[]'::jsonb) を既定形にします。
実務コラム：JSONの組み立てをどこでやるか
1回のクエリでレスポンス全体を組み上げると、N+1クエリも、アプリ側の詰め替えコードも消えます。一方でSQLは長くなり、レスポンス仕様の変更がSQLの変更になります。目安は「取得の形がそのまま出力の形になる読み取り専用API」ならSQL側、「複数ソースを混ぜる・条件で形が変わる」ならアプリ側。どちらでもJSONの生成そのものは同じコストなので、変更のしやすさで選びます。
2 / 5
問題を選択
QUESTION 8

jsonb_each — オブジェクトのキーと値を行に開く

jsonb_eachLATERAL縦持ち変換
前提知識 ▼

キー名が事前に分からないオブジェクトは、jsonb_each でキーと値の組を行として取り出します。key と value の2列を返す集合返し関数で、FROM 句で使います。

SELECT kv.key, kv.value
FROM   table_name t
CROSS JOIN LATERAL jsonb_each_text(t.jsonb_col) AS kv;
-- jsonb_each      : value は jsonb（文字列は引用符付き）
-- jsonb_each_text : value は text（引用符なし）
-- jsonb_object_keys : キー名だけが欲しいとき
対象はトップレベルだけ：ネストしたオブジェクトは展開されず、値としてそのまま返ります。奥の階層も開きたいときは、取り出した値へもう一度 jsonb_each を適用します。
問題 ▼

configs テーブルの params を、1キー1行の縦持ちに展開してください。取得列は config_id, key, value、config_id 昇順・key 昇順で返してください。value は引用符の付かないテキストにします。

使用テーブル
▸ configs
config_idparams（jsonb）
1{"debug": "true", "limit": "100"}
2{"limit": "50"}
期待出力 ▼
config_idkeyvalue
1debugtrue
1limit100
2limit50
模範解答コード ▼ 
SELECT
  c.config_id,
  kv.key,
  kv.value
FROM   configs c
CROSS JOIN LATERAL jsonb_each_text(c.params) AS kv  -- value をテキストで受け取る
ORDER BY c.config_id, kv.key;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM configs c                     → 2行読み込み
  2. CROSS JOIN LATERAL jsonb_each_text → キーごとに展開して3行へ
  3. SELECT config_id, key, value       → 3列を選択
  4. ORDER BY c.config_id, kv.key       → config_id 昇順・key 昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT c.config_id, kv.key, kv.value FROM configs c CROSS JOIN LATERAL jsonb_each_text(c.params) AS kv ORDER BY c.config_id, kv.key;
LEGEND ▼
データ取得・読込対象
① FROM configs ▼
FROM configs cconfigs 全2行を読み込みます。config 1 は2キー、config 2 は1キーを持ちます。キー名はテーブル定義には現れません。
1 / 3
config_idparams（jsonb）
1{"debug": "true", "limit": "100"}
2{"limit": "50"}
全 2行 読込
学習ポイント
キー名を知らなくても扱える：->> はキー名を書く必要がありますが、jsonb_each は書かずに全キーを取り出せます。設定値のように項目が増減するデータの棚卸しに向きます。
_text 版で引用符を外す：jsonb_each の value は jsonb なので、文字列は "true" と引用符付きです。そのまま表示・比較するなら jsonb_each_text を選びます。
並び順は明示する：展開直後の行順は保証されません。出力の順序が要件なら、ORDER BY でキーと親の識別子を必ず指定します。
アンチパターン
展開してから特定キーを探す：jsonb_each で全キーを開いてから WHERE key = 'limit' で絞るのは、params ->> 'limit' と同じ結果を高いコストで得ているだけです。キー名が分かっているなら直接取り出します。
ネストが開くと期待する：値がオブジェクトのキーは、展開されずオブジェクトのまま value に入ります。jsonb_each_text ではそれがJSONテキストとして返るため、さらに解析が必要になります。
実務コラム：EAVの再発明に注意する
キーと値の縦持ちは、行を増やせば何でも表現できる代わりに、型も制約も外部キーも失います。JSONで持つ動機が「項目が事前に決まらない」ことなら妥当ですが、「テーブル定義を変えたくない」だけなら、数か月後に型の効かない巨大な設定表が残ります。jsonb_each は、そういうデータを調べるための道具として使うのが健全で、日常の参照経路にするなら列へ切り出す合図と読みます。
3 / 5
問題を選択
QUESTION 9

jsonb_typeof — JSON null・キー欠損・列のNULLを見分ける

jsonb_typeofNULLデータ品質
前提知識 ▼

JSONを扱うと「値が無い」状態が3種類に分かれます。値が JSON の null、キー自体が無い、列そのものが SQL NULL。->> はどれも SQL NULL を返すため、区別には jsonb_typeof を使います。

SELECT jsonb_typeof(jsonb_col -> 'key') AS json_type
FROM   table_name;
-- 'string' / 'number' / 'boolean' / 'object' / 'array' : 値がある
-- 'null'  : 値が JSON の null
-- SQL NULL: キーが無い、または列が NULL
? との違い：jsonb_col ? 'key' は「キーがあるか」だけを見るので、値が JSON null でも真になります。「キーはあるが値が null」を通したいのか弾きたいのかで、使う判定が変わります。
問題 ▼

profiles テーブルの各行について、nickname の値とJSON上の型を並べてください。取得列は profile_id, nickname, json_type、profile_id 昇順で返してください。絞り込みは行いません。

使用テーブル
▸ profiles
profile_iddata（jsonb）
1{"nickname": "たろう"}
2{"nickname": null}
3{"age": 20}
4NULL
5{"nickname": "はなこ"}
期待出力 ▼
profile_idnicknamejson_type
1たろうstring
2NULLnull
3NULLNULL
4NULLNULL
5はなこstring
模範解答コード ▼ 
SELECT
  profile_id,
  data ->> 'nickname'                  AS nickname,   -- 3状態とも SQL NULL
  jsonb_typeof(data -> 'nickname')     AS json_type   -- JSON null だけ 'null' 文字列
FROM   profiles
ORDER BY profile_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM profiles                → 5行読み込み
  2. SELECT ->> で値を取り出し     → 3状態とも SQL NULL になる
  3. SELECT jsonb_typeof で型判定  → JSON null と欠損を区別
  4. ORDER BY profile_id          → profile_id の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT profile_id, data ->> 'nickname' AS nickname, jsonb_typeof(data -> 'nickname') AS json_type FROM profiles ORDER BY profile_id;
LEGEND ▼
データ取得・読込対象
① FROM profiles ▼
FROM profilesprofiles 全5行を読み込みます。profile 2 は値が JSON の null、profile 3 は nickname キーそのものが無く、profile 4 は data 列が SQL NULL です。
1 / 3
profile_iddata（jsonb）
1{"nickname": "たろう"}
2{"nickname": null}
3{"age": 20}
4NULL
5{"nickname": "はなこ"}
全 5行 読込
学習ポイント
NULLは3種類ある：JSON の null、キーの欠損、列の SQL NULL。->> の結果だけを見ると全部同じに見えますが、意味は「未設定と明示した」「項目が無い」「レコードごと無い」でそれぞれ違います。
'null' は文字列：jsonb_typeof が返すのは型名のテキストです。jsonb_typeof(…) = 'null' という文字列比較で判定し、IS NULL と混同しないようにします。
値の有無を確かめる定型：実際に文字列が入っている行だけを取るなら WHERE jsonb_typeof(data -> 'nickname') = 'string' と書きます。この例では profile 1 と profile 5 が残ります。
アンチパターン
JSON null を IS NULL で探す：data -> 'nickname' IS NULL は profile 3・4 だけに当たり、値が JSON null の profile 2 は当たりません。data -> 'nickname' は jsonb の null という値であって、SQL NULL ではないためです。
未設定を空文字で表す：アプリ側で {"nickname": ""} と入れると、型は string のまま4つ目の状態が増えます。未設定はキーを置かないか JSON null にする、と最初に決めて揃えます。
実務コラム：JSONは制約が効かない領域
通常の列なら NOT NULL ひとつで「値が無い行」を排除できますが、JSONの内側にはそれが届きません。必須項目が欠けた行は、投入時ではなく数か月後の集計で見つかります。対策は2通りで、必須項目は列へ出すか、CHECK (data ? 'nickname') のような制約をテーブルに足すこと。どちらも取らないなら、少なくとも jsonb_typeof による棚卸しクエリを定期的に回して、欠損の割合を見えるようにしておきます。
4 / 5
問題を選択
QUESTION 10

包含演算子とGIN — ネストした条件を索引の効く形で書く

包含演算子GINインデックス検索性能
前提知識 ▼

JSONの絞り込みは、書き方によって索引が効くかどうかが変わります。jsonb 列へ GIN インデックスを張ると、@>・?・?|・?& が索引で処理されます。->> の等価比較はこの索引では加速しません。

CREATE INDEX ON table_name USING GIN (jsonb_col);

SELECT * FROM table_name
WHERE  jsonb_col @> '{"key": "value", "parent": {"child": "value2"}}';
-- 探したい構造をそのまま書けば、ネストしていても1つの条件で済む
複数条件は1つの @> にまとめる：@> の右辺はオブジェクト全体を部分集合として判定するため、条件が増えても演算子は1つのままです。AND で分けるより索引が使いやすくなります。
問題 ▼

events テーブルから、type が purchase で、かつ user.plan が pro のイベントを取得してください。GINインデックスが使えるよう、条件は包含演算子ひとつで書いてください。取得列は event_id, amount、event_id 昇順で返してください。amount は数値にします。

使用テーブル
▸ events
event_idpayload（jsonb）
1{"type": "purchase", "user": {"plan": "pro"}, "amount": 1200}
2{"type": "purchase", "user": {"plan": "free"}, "amount": 500}
3{"type": "login", "user": {"plan": "pro"}}
4{"type": "purchase", "user": {"plan": "pro"}, "amount": 3000}
期待出力 ▼
event_idamount
11200
43000
模範解答コード ▼ 
SELECT
  event_id,
  (payload ->> 'amount')::int AS amount
FROM   events
WHERE  payload @> '{"type": "purchase", "user": {"plan": "pro"}}'  -- 2条件を1つの包含で
ORDER BY event_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM events                  → 4行読み込み（GIN索引があれば候補行だけ）
  2. WHERE payload @> '{...}'     → 部分構造を含む2行に絞り込み
  3. SELECT (payload->>'amount')::int → 2列を選択
  4. ORDER BY event_id            → event_id の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT event_id, (payload ->> 'amount')::int AS amount FROM events WHERE payload @> '{"type": "purchase", "user": {"plan": "pro"}}' ORDER BY event_id;
LEGEND ▼
データ取得・読込対象
① FROM events ▼
FROM eventsevents 全4行を読み込みます。payload は type・user・amount を持ち、user はネストしたオブジェクトです。event 3 には amount がありません。
1 / 3
event_idpayload（jsonb）
1{"type": "purchase", "user": {"plan": "pro"}, "amount": 1200}
2{"type": "purchase", "user": {"plan": "free"}, "amount": 500}
3{"type": "login", "user": {"plan": "pro"}}
4{"type": "purchase", "user": {"plan": "pro"}, "amount": 3000}
全 4行 読込
学習ポイント
索引が効くのは演算子で決まる：GIN (jsonb_ops) が扱うのは @> と存在演算子だけです。同じ結果を返す payload ->> 'type' = 'purchase' は、この索引では全件走査になります。
2つの演算子クラス：既定の jsonb_ops はキーと値の両方を索引に載せ、? 系も使えます。USING GIN (payload jsonb_path_ops) は @> 専用の代わりに索引が小さく高速です。検索が包含だけなら後者を選びます。
特定キーだけなら式インデックス：常に同じキーで等価検索するなら CREATE INDEX ON events ((payload ->> 'type')) という B-tree の式インデックスが、GINより小さく速くなります。範囲検索もこちらでしか効きません。
アンチパターン
条件を AND でばらす：payload @> '{"type": "purchase"}' AND payload @> '{"user": {"plan": "pro"}}' は結果こそ同じですが、索引の走査が2回に分かれます。1つのオブジェクトへまとめるほうが素直です。
@> で数値の範囲を絞る：@> は完全一致の包含だけで、amount > 1000 のような比較はできません。範囲条件は (payload ->> 'amount')::int と式インデックスの組み合わせになります。
実務コラム：GINインデックスの代償
GINはJSONのキーと値を1つずつ索引語として展開するため、索引サイズがテーブルに迫ることも珍しくありません。更新も重く、既定では fastupdate により挿入時のコストを保留リストへ逃がしていますが、その掃除が走ったタイミングで応答が跳ねます。「とりあえずJSON列にGINを張る」ではなく、実際に流れている検索条件を確かめてから、jsonb_path_ops や式インデックスを含めて選び分けます。
5 / 5
問題を選択
次のセット →