SQL Quiz Book

SQL JSON — 明細展開・パス検索・再構成の応用

応用JSONjsonb_to_recordsetWITH ORDINALITYJSONPathjsonb_object_agg配列の組み直しPostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 1

jsonb_to_recordset — JSON配列の明細を型付きの行へ開く

jsonb_to_recordsetLATERAL明細ゼロ
前提知識 ▼

明細をJSON配列で持つテーブルは、そのままでは集計できません。jsonb_to_recordset は列名と型を宣言して配列を行へ開く関数で、開いた後は普通の表と同じように SUM や GROUP BY をかけられます。行ごとの列を参照するため、LATERAL と組にします。

SELECT t.id_col, x.*
FROM   table_name t
LEFT JOIN LATERAL jsonb_to_recordset(t.json_col)
     AS x(key_col text, num_col int) ON TRUE;  -- 列名と型は自分で宣言する
開けない行は消える：配列が空の行では関数が0行を返すため、CROSS JOIN LATERAL（カンマ区切りも同じ）では元の行ごと結果から落ちます。残すなら LEFT JOIN LATERAL … ON TRUE にします。
問題 ▼

orders テーブルから、注文ごとの合計金額を求めてください。items は明細オブジェクトの配列で、各要素は sku（テキスト）、qty（個数）、unit_price（単価）を持ちます。合計金額は明細ごとの qty × unit_price の総和とし、明細が1件も無い注文は 0 とします。取得列は order_id, customer, total_amount、order_id 昇順で返してください。

使用テーブル
▸ orders
order_idcustomeritems（jsonb）
1佐藤[{"sku":"A-1","qty":2,"unit_price":1200},{"sku":"B-2","qty":1,"unit_price":3000}]
2鈴木[{"sku":"A-1","qty":1,"unit_price":1200}]
3田中[{"sku":"C-3","qty":3,"unit_price":800},{"sku":"A-1","qty":2,"unit_price":1200},{"sku":"B-2","qty":1,"unit_price":3000}]
4高橋[]
期待出力 ▼
order_idcustomertotal_amount
1佐藤5400
2鈴木1200
3田中7800
4高橋0
模範解答コード ▼ 
SELECT
  o.order_id,
  o.customer,
  COALESCE(SUM(i.qty * i.unit_price), 0) AS total_amount  -- 明細ゼロを 0 に寄せる
FROM     orders o
LEFT JOIN LATERAL jsonb_to_recordset(o.items)
     AS i(sku text, qty int, unit_price numeric) ON TRUE  -- 型を宣言して行へ開く
GROUP BY o.order_id, o.customer
ORDER BY o.order_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders                        → 4行読み込み
  2. LEFT JOIN LATERAL jsonb_to_recordset → 注文ごとに明細を行へ展開（7行）
  3. GROUP BY o.order_id, o.customer    → 注文単位の4グループへ戻す
  4. SELECT COALESCE(SUM(...), 0)       → 明細金額を合計、明細ゼロは 0
  5. ORDER BY o.order_id                → order_id の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT o.order_id, o.customer, COALESCE(SUM(i.qty * i.unit_price), 0) AS total_amount FROM orders o LEFT JOIN LATERAL jsonb_to_recordset(o.items) AS i(sku text, qty int, unit_price numeric) ON TRUE GROUP BY o.order_id, o.customer ORDER BY o.order_id;
LEGEND ▼
データ取得・読込対象
① FROM orders ▼
FROM ordersorders 全4行を読み込みます。明細は items の中に配列として畳まれたままで、この時点では1注文が1行です。order 4 の items は空配列で、明細を1件も持ちません。
1 / 6
order_idcustomeritems（jsonb）
1佐藤[{"sku":"A-1","qty":2,...},{"sku":"B-2","qty":1,...}]
2鈴木[{"sku":"A-1","qty":1,...}]
3田中[{"sku":"C-3","qty":3,...},{"sku":"A-1","qty":2,...},{"sku":"B-2","qty":1,...}]
4高橋[]
全 4行 読込
学習ポイント
型は取り出し側で宣言する：jsonb_to_recordset は列の型情報をJSONから推測しません。AS i(sku text, qty int, unit_price numeric) の宣言が契約で、値が入っていない要素はその列が NULL になり、数値として読めない値が入っていれば実行時エラーになります。金額を numeric で受けるのは、float の丸め誤差を持ち込まないためです。
LEFT JOIN LATERAL が行を守る：集合を返す関数は、返す行が0件のとき「その行が消える」という形で結果を変えます。LEFT JOIN … ON TRUE にしておくと、明細ゼロの注文が NULL 行として残り、あとから COALESCE で意味づけできます。
SUM の空集合は NULL：COUNT は 0 を返しますが、SUM / AVG / MAX は対象が1件も無いと NULL を返します。「0円の注文」を出したいなら、集約の外側で COALESCE をかけるのが定石です。
アンチパターン
カンマ区切りで関数を並べる：FROM orders o, jsonb_to_recordset(o.items) AS i(...) は CROSS JOIN LATERAL と同じ意味で、明細ゼロの注文が黙って消えます。結果の行数が入力より減っていることに気付けるのは、件数を突き合わせたときだけです。
アプリ側で配列を回して合計する：全行を取得してからループで足し上げると、転送量とラウンドトリップが行数に比例します。開いて集約するところまでをSQLに寄せると、返るのは注文数ぶんの行だけです。
実務コラム：明細をJSONに畳むか、テーブルに分けるか
受注APIのレスポンスをそのまま保存したい、明細の項目が取引先ごとに違う——こうした事情があるうちは、明細をJSON配列で持つ設計に利があります。反面、明細単位の集計・結合・制約はすべて展開のコストを払うことになり、外部キーや CHECK も効きません。明細を「毎日の集計対象」として扱い始めた時点が、order_items テーブルへ切り出す合図です。移行の第一歩として、この設問のような展開クエリをそのままビューにしておくと、参照側を書き換えずにテーブル化へ進めます。
1 / 5
問題を選択
QUESTION 2

WITH ORDINALITY — 配列の並び順を添字として取り出す

WITH ORDINALITYjsonb_array_elements_text順序の保証
前提知識 ▼

JSON配列は順序を持ちますが、行へ開いた時点でその情報は列になりません。WITH ORDINALITY を付けると、集合を返す関数の出力に1始まりの連番列が追加され、「何番目の要素か」を WHERE や ORDER BY で扱えるようになります。

SELECT x.elem, x.pos
FROM   table_name t
CROSS JOIN LATERAL jsonb_array_elements_text(t.json_col)
     WITH ORDINALITY AS x(elem, pos);  -- pos は 1 始まりの bigint
行が出てくる順に頼らない：展開関数は配列順に行を返しますが、上位で結合や集約を挟んだ後の並びは ORDER BY を書かない限り保証されません。位置が意味を持つなら、必ず列にしてから並べ替えます。
問題 ▼

playlists テーブルの tracks は曲名（文字列）の配列です。各プレイリストの先頭3曲を、曲順の番号を付けて取得してください。番号は配列の先頭を 1 とします。取得列は playlist_id, pos, title、playlist_id 昇順・pos 昇順で返してください。

使用テーブル
▸ playlists
playlist_idnametracks（jsonb）
1morning["Aurora","Bloom","Cinder","Drift"]
2focus["Ember","Frost"]
3night["Glow","Halo","Iris"]
期待出力 ▼
playlist_idpostitle
11Aurora
12Bloom
13Cinder
21Ember
22Frost
31Glow
32Halo
33Iris
模範解答コード ▼ 
SELECT
  p.playlist_id,
  t.pos,
  t.title
FROM   playlists p
CROSS JOIN LATERAL jsonb_array_elements_text(p.tracks)
     WITH ORDINALITY AS t(title, pos)  -- 曲名と 1 始まりの位置
WHERE  t.pos <= 3                    -- 先頭3曲だけ残す
ORDER BY p.playlist_id, t.pos;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM playlists                     → 3行読み込み
  2. CROSS JOIN LATERAL jsonb_array_elements_text → 曲を行へ展開（9行）
  3. WITH ORDINALITY                    → 展開順に 1 始まりの pos を付与
  4. WHERE t.pos <= 3                   → 8行に絞り込み
  5. SELECT playlist_id, pos, title     → 3列を選択
  6. ORDER BY playlist_id, pos          → プレイリスト順・曲順
  */
解説（テーブル変化・ポイント） ▼
SELECT p.playlist_id, t.pos, t.title FROM playlists p CROSS JOIN LATERAL jsonb_array_elements_text(p.tracks) WITH ORDINALITY AS t(title, pos) WHERE t.pos <= 3 ORDER BY p.playlist_id, t.pos;
LEGEND ▼
データ取得・読込対象
① FROM playlists ▼
FROM playlistsplaylists 全3行を読み込みます。曲は tracks の中に配列として畳まれたままで、曲数はプレイリストごとに違います。
1 / 5
playlist_idnametracks（jsonb）
1morning["Aurora","Bloom","Cinder","Drift"]
2focus["Ember","Frost"]
3night["Glow","Halo","Iris"]
全 3行 読込
学習ポイント
位置は列にしてから使う：「配列の先頭」「N番目」は、展開した行の並びではなく WITH ORDINALITY の連番で表現します。列になっていれば WHERE、ORDER BY、ウィンドウ関数の PARTITION BY まで、通常の列と同じ道具が使えます。
連番は関数呼び出しごとに振り直す：LATERAL の中で行ごとに関数が呼ばれるため、pos は各プレイリストで 1 から始まります。全体を通した通し番号が欲しいときは ROW_NUMBER() OVER (ORDER BY …) と役割が分かれます。
_text 版とそうでない版：jsonb_array_elements は要素を jsonb のまま返すので、文字列は "Aurora" と引用符付きになります。要素が文字列と分かっていて、そのままテキストとして使うなら jsonb_array_elements_text を選ぶとキャストが1つ減ります。
アンチパターン
LIMIT で「先頭3曲」を取る：LIMIT 3 は結果全体に効くので、プレイリストごとの先頭3曲にはなりません。グループごとの上位N件は、位置列や ROW_NUMBER による絞り込みで表現します。
ORDER BY を省いて展開順に頼る：単純なクエリでは配列順のまま返るように見えますが、これは保証ではなく実行計画の都合です。並列スキャンやマージ結合が選ばれた瞬間に順序が変わり、テストでは再現しない不具合になります。
実務コラム：順序を持つデータをJSON配列で持つとき
プレイリスト、ワークフローの手順、フォームの設問——順序そのものが情報であるデータは、JSON配列と相性が良い一方で更新が重くなります。「3曲目と4曲目を入れ替える」だけでも配列全体を書き戻すことになり、読んでから書き戻すまでの間に入った他の更新は、後勝ちで消えます。並べ替えが頻繁なら、明細テーブルに sort_order 列を持たせて、間に挿入できるよう間隔を空けた採番（10, 20, 30 …）にするほうが扱いやすくなります。JSON配列が向くのは、まとめて差し替えるか読むだけ、という使い方のときです。
2 / 5
問題を選択
QUESTION 3

JSONPath — ネストした配列から条件に合う要素を抜く

jsonb_path_queryJSONPathフィルタ式
前提知識 ▼

包含演算子 @> が書けるのは「この値を含むか」までで、数値の大小は表現できません。SQL/JSON path なら、パスの途中に ? ( … ) のフィルタ式を挟んで、条件に合う要素だけを取り出せます。フィルタの中の @ は「いま見ている要素」を指します。

SELECT x.elem
FROM   table_name t
CROSS JOIN LATERAL
     jsonb_path_query(t.json_col, '$.arr[*] ? (@.num_key > 0)') AS x(elem);
-- $ = 全体、.arr[*] = 配列の全要素、? ( … ) = 絞り込み
該当0件の行は結果から消える：jsonb_path_query は集合を返す関数なので、条件に合う要素が1つも無い行は CROSS JOIN LATERAL で落ちます。行を残したいなら LEFT JOIN LATERAL … ON TRUE、真偽だけ欲しいなら述語演算子 @? を使います。
問題 ▼

products テーブルの spec は、製品ごとに variants 配列を持ちます。各要素は color（色）と stock（在庫数）を持ちます。在庫が1以上のバリエーションだけを取り出してください。取得列は product_id, color, stock、product_id 昇順・color 昇順で返してください。stock は数値、color は引用符の付かないテキストにします。

使用テーブル
▸ products
product_idspec（jsonb）
1{"brand":"Nova","variants":[{"color":"red","stock":0},{"color":"blue","stock":12}]}
2{"brand":"Orbit","variants":[{"color":"black","stock":4},{"color":"white","stock":7}]}
3{"brand":"Pico","variants":[{"color":"green","stock":0}]}
4{"brand":"Quill","variants":[]}
期待出力 ▼
product_idcolorstock
1blue12
2black4
2white7
模範解答コード ▼ 
SELECT
  p.product_id,
  v.elem ->> 'color'        AS color,
  (v.elem ->> 'stock')::int AS stock  -- テキスト経由で数値へ
FROM   products p
CROSS JOIN LATERAL
     jsonb_path_query(p.spec, '$.variants[*] ? (@.stock > 0)') AS v(elem)
ORDER BY p.product_id, color;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM products                  → 4行読み込み
  2. CROSS JOIN LATERAL jsonb_path_query → $.variants[*] で全要素を走査
  3. ? (@.stock > 0)                → 在庫のある要素だけを返す（3行）
  4. SELECT color, stock            → 要素から2つのキーを取り出す
  5. ORDER BY product_id, color     → 製品順・色名順
  */
解説（テーブル変化・ポイント） ▼
SELECT p.product_id, v.elem ->> 'color' AS color, (v.elem ->> 'stock')::int AS stock FROM products p CROSS JOIN LATERAL jsonb_path_query(p.spec, '$.variants[*] ? (@.stock > 0)') AS v(elem) ORDER BY p.product_id, color;
LEGEND ▼
データ取得・読込対象
① FROM products ▼
FROM productsproducts 全4行を読み込みます。variants はオブジェクトの配列で、要素数は製品ごとに違います。Quill は variants が空配列です。
1 / 5
product_idspec（jsonb）
1{"brand":"Nova","variants":[{red,0},{blue,12}]}
2{"brand":"Orbit","variants":[{black,4},{white,7}]}
3{"brand":"Pico","variants":[{green,0}]}
4{"brand":"Quill","variants":[]}
全 4行 読込
学習ポイント
パスと述語を1つの文字列で書く：$.variants[*] ? (@.stock > 0) は「降りる」「広げる」「絞る」を1本のパスにまとめた形です。展開してから WHERE で絞る書き方と結果は同じですが、条件がJSON側に閉じているぶん、複数階層をまたぐ条件を短く書けます。
フィルタ式では型が効く：@.stock > 0 は JSON の数値どうしの比較です。値が "12" のように文字列で入っていると、比較は真でも偽でもなく未定義になり、その要素は返りません。数値として検索する項目は、書き込み側で数値として入れておくのが前提になります。
取り出しと判定を使い分ける：要素そのものが欲しいときは jsonb_path_query、条件に合う要素があるかだけ知りたいときは @? です。WHERE spec @? '$.variants[*] ? (@.stock > 0)' なら行は増えず、GINインデックスも効きます。
アンチパターン
包含演算子で範囲を書こうとする：spec @> '{"variants":[{"stock":0}]}' は「在庫0の要素を含む」という等価の判定で、「在庫が1以上」は表現できません。@> は等価の包含専用と割り切り、大小比較はパス式に寄せます。
パス文字列をアプリで組み立てる：ユーザー入力を連結してパス式を作ると、JSONPath版のインジェクションになります。jsonb_path_query(spec, '$.variants[*] ? (@.stock > $min)', jsonb_build_object('min', 0)) のように、値は変数として渡します。
実務コラム：ドライバのプレースホルダと ? の衝突
JSONPath のフィルタは ? で始まりますが、多くのドライバは ? をバインド変数の記号として横取りします。JDBC や一部のORMでは、パス文字列の中の ? がパラメータとして解釈され、意味の分からないエラーになります。回避策は3つあります。パス全体をパラメータとして渡す、jsonb_path_query(spec, CAST(:path AS jsonpath)) のようにキャストで包む、あるいはドライバのエスケープ（?? など）に従う——どれを採るかは接続層の都合で決まるので、JSONPath を使い始める前に、まず短いパスで1本通してみるのが早道です。
3 / 5
問題を選択
QUESTION 4

jsonb_object_agg — 縦持ちの属性をJSONへ畳む

jsonb_object_agg集約内 ORDER BYキーの後勝ち
前提知識 ▼

1行1属性の縦持ち（キーと値の表）は列を増やさずに項目を足せますが、参照側では1行1エンティティの形が欲しくなります。jsonb_object_agg は、グループ内の行をキーと値のペアとしてJSONオブジェクトへ畳みます。同じキーが複数回現れると後から来た値が残るため、集約の中に ORDER BY を書いて「後」を決めます。

SELECT id_col,
       jsonb_object_agg(key_col, val_col ORDER BY ts_col) AS obj
FROM     table_name
GROUP BY id_col;  -- 後勝ちなので、昇順に並べると最後＝最新が残る
キーに NULL は渡せない：値が NULL の行は JSON の null になりますが、キーが NULL の行が1つでもあると実行時エラーで落ちます。キー列に NOT NULL が無いなら、集約の前に除外します。
問題 ▼

device_attrs テーブルは、デバイスの属性を1行1属性で持ちます。デバイスごとに、attr_key をキー・attr_value を値とするJSONオブジェクトへまとめてください。同じ属性が複数回記録されている場合は、recorded_at が最も新しい値を採用します。取得列は device_id, attrs、device_id 昇順で返してください。

使用テーブル
▸ device_attrs
device_idattr_keyattr_valuerecorded_at
d-01osiOS 162026-01-10
d-01carrierNTT2026-01-10
d-01osiOS 172026-03-01
d-02osAndroid 142026-02-05
d-02storageNULL2026-02-05
d-03osiOS 172026-03-02
期待出力 ▼
device_idattrs
d-01{"os": "iOS 17", "carrier": "NTT"}
d-02{"os": "Android 14", "storage": null}
d-03{"os": "iOS 17"}
模範解答コード ▼ 
SELECT
  device_id,
  jsonb_object_agg(attr_key, attr_value ORDER BY recorded_at) AS attrs  -- 後勝ち＝最新が残る
FROM     device_attrs
GROUP BY device_id
ORDER BY device_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM device_attrs              → 6行読み込み
  2. GROUP BY device_id             → 3グループに分割
  3. ORDER BY recorded_at（集約内）  → グループ内の適用順を古い順に固定
  4. SELECT jsonb_object_agg(...)   → キー・値のペアを1つのJSONへ畳む
  5. ORDER BY device_id             → device_id の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT device_id, jsonb_object_agg(attr_key, attr_value ORDER BY recorded_at) AS attrs FROM device_attrs GROUP BY device_id ORDER BY device_id;
LEGEND ▼
データ取得・読込対象
① FROM device_attrs ▼
FROM device_attrsdevice_attrs 全6行を読み込みます。1行が1属性で、d-01 だけ os が2回記録されています。d-02 の storage は値が NULL です。
1 / 5
device_idattr_keyattr_valuerecorded_at
d-01osiOS 162026-01-10
d-01carrierNTT2026-01-10
d-01osiOS 172026-03-01
d-02osAndroid 142026-02-05
d-02storageNULL2026-02-05
d-03osiOS 172026-03-02
全 6行 読込
学習ポイント
集約の中の ORDER BY が勝敗を決める：重複キーが後勝ちである以上、どの行が最後に来るかを決めない限り結果は不定です。jsonb_object_agg(k, v ORDER BY ts) の1語で「最新を採る」が表現でき、副問い合わせで最新行を先に絞る必要がなくなります。
キーの並び順は保存されない：jsonb はキーを「長さ順 → バイト順」に正規化して格納します。積んだ順序も、元テーブルの並びも残りません。キーの順序に意味を持たせたいなら、配列（jsonb_agg）で持つか json 型を選びます。
値の NULL と行の欠損は別物：{"storage": null} は「storage は記録されていて、値が空」を表します。キーごと無いのは「まだ記録されていない」です。取り出し側では attrs ? 'storage'（キーの有無）と attrs ->> 'storage' IS NULL（値が空）を意識して使い分けます。
アンチパターン
集約内の ORDER BY を省く：並べ替えを書かなくても、小さなテーブルでは挿入順のまま最新が残ることがあります。しかし読み取り順は保証されないので、行数が増えたりインデックススキャンに変わった瞬間に古い値が採用されます。テストでは気付けない種類の劣化です。
属性をすべて縦持ちにする：必ず存在して必ず検索される項目まで縦持ちにすると、1エンティティの取得に自己結合が並びます。可変なのは末端の属性だけ、という切り分けをしないと、この設問のような復元クエリが常時走ることになります。
実務コラム：縦持ちとJSON列、どちらで持つか
縦持ち（EAV）は、属性の追加をデータ投入だけで済ませられ、いつ・誰が入れたかの履歴も行として残せます。代わりに、1エンティティを組み立てるたびに集約が必要です。JSON列は取得が1行で済む反面、部分更新が全体の書き戻しになり、値ごとの履歴は持てません。実務では「履歴と監査が要る属性は縦持ち、表示のためにまとめて読むだけの属性はJSON列」と役割で分け、この設問のような集約をマテリアライズドビューに固定して両取りする形がよく使われます。更新頻度が低く参照頻度が高いほど、固定化の効果が大きくなります。
4 / 5
問題を選択
QUESTION 5

配列の組み直し — 要素を絞って並び順ごと復元する

jsonb_aggFILTER空配列
前提知識 ▼

JSON配列から条件に合う要素だけを取り除く演算子はありません。展開して、絞って、組み直すのが定石です。組み直しでは2つの注意点があります。並び順は集約内の ORDER BY で指定すること、そして要素が1つも残らないグループでは jsonb_agg が NULL を返すことです。

SELECT id_col,
       COALESCE(jsonb_agg(x.elem ORDER BY x.pos)
                FILTER (WHERE 条件式), '[]'::jsonb) AS arr
FROM   table_name t
LEFT JOIN LATERAL jsonb_array_elements(t.json_col)
     WITH ORDINALITY AS x(elem, pos) ON TRUE
GROUP BY id_col;
WHERE で絞ると行ごと消える：条件を WHERE に書くと、残る要素が0個のグループはグループそのものが消えます。行を残して空配列を返したいなら、絞り込みは集約に付ける FILTER に置きます。
問題 ▼

carts テーブルの items は明細オブジェクトの配列で、各要素は sku と qty を持ちます。qty が 0 の明細を取り除いたカートを作ってください。元の並び順は保ち、明細が1件も残らないカートは空配列 [] を返します。取得列は cart_id, items、cart_id 昇順で返してください。

使用テーブル
▸ carts
cart_iditems（jsonb）
1[{"sku":"A","qty":2},{"sku":"B","qty":0},{"sku":"C","qty":1}]
2[{"sku":"D","qty":0}]
3[]
4[{"sku":"E","qty":5}]
期待出力 ▼
cart_iditems
1[{"qty": 2, "sku": "A"}, {"qty": 1, "sku": "C"}]
2[]
3[]
4[{"qty": 5, "sku": "E"}]
模範解答コード ▼ 
SELECT
  c.cart_id,
  COALESCE(
    jsonb_agg(e.elem ORDER BY e.pos) FILTER (WHERE (e.elem ->> 'qty')::int > 0),
    '[]'::jsonb                                -- 全滅したカートは空配列へ
  ) AS items
FROM   carts c
LEFT JOIN LATERAL jsonb_array_elements(c.items)
     WITH ORDINALITY AS e(elem, pos) ON TRUE  -- 元の位置を pos に残す
GROUP BY c.cart_id
ORDER BY c.cart_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM carts                     → 4行読み込み
  2. LEFT JOIN LATERAL … WITH ORDINALITY → 明細を行へ展開し位置を付与（6行）
  3. GROUP BY c.cart_id             → カート単位の4グループへ戻す
  4. FILTER (WHERE qty > 0)         → 集約対象から qty 0 の明細を外す
  5. SELECT jsonb_agg(... ORDER BY pos) → 元の順で配列へ組み直す
  6. COALESCE(..., '[]')            → 対象ゼロのカートを空配列へ
  7. ORDER BY c.cart_id             → cart_id の昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT c.cart_id, COALESCE( jsonb_agg(e.elem ORDER BY e.pos) FILTER (WHERE (e.elem ->> 'qty')::int > 0), '[]'::jsonb ) AS items FROM carts c LEFT JOIN LATERAL jsonb_array_elements(c.items) WITH ORDINALITY AS e(elem, pos) ON TRUE GROUP BY c.cart_id ORDER BY c.cart_id;
LEGEND ▼
データ取得・読込対象
① FROM carts ▼
FROM cartscarts 全4行を読み込みます。cart 1 は3明細、cart 2 と cart 4 は1明細、cart 3 は空配列です。qty が 0 の明細は cart 1 と cart 2 に入っています。
1 / 6
cart_iditems（jsonb）
1[{"sku":"A","qty":2},{"sku":"B","qty":0},{"sku":"C","qty":1}]
2[{"sku":"D","qty":0}]
3[]
4[{"sku":"E","qty":5}]
全 4行 読込
学習ポイント
FILTER は行を残したまま集約対象を選ぶ：WHERE はグループ分けの前に行を捨てるので、全滅したグループは結果から消えます。FILTER は集約関数ごとに対象を選ぶ仕組みなので、グループは残り「対象が0件」という状態を表現できます。1つのクエリで条件別の集計を並べたいときにも同じ形が使えます。
順序は pos で明示する：展開時に WITH ORDINALITY で位置を残しておくと、組み直しの ORDER BY e.pos がそのまま「元の並び」を意味します。位置を残さずに組み直すと、要素の並びが実行計画任せになります。
空配列と NULL を混ぜない：受け取り側にとって、[] は「要素ゼロの配列」、NULL は「配列そのものが無い」です。アプリのコードが配列を前提に length を読むなら、SQL側で COALESCE(…, '[]'::jsonb) まで済ませておくほうが、分岐が1つ減ります。
アンチパターン
条件を WHERE に書く：WHERE (e.elem ->> 'qty')::int > 0 にすると、明細が全滅した cart 2 と、もともと空だった cart 3 が結果から消えます。入力4行に対して出力2行になり、「消えたカートは存在しないのか、空なのか」が呼び出し側から区別できません。
文字列操作で要素を削る：replace(items::text, …) のようなテキスト処理は、値に区切り文字が含まれた瞬間に壊れます。JSONの構造を保証したまま編集できるのは、展開と再集約か jsonb_set 系の関数だけです。
実務コラム：配列の「一部だけ更新」が重い理由
JSON配列の1要素を書き換えるには、行全体を読み、配列を作り直し、行全体を書き戻します。UPDATE の対象が1バイトでも、Postgres は新しい行バージョンを1つ作るため、更新コストは配列の大きさに比例します。明細が数百件を超え、1件ずつ更新が走る使い方になってきたら、それは配列で持つ限界のサインです。加えて、変更の消失に注意が要ります。UPDATE … SET items = jsonb_set(items, …) のように列を参照する1文なら、後続の更新は行ロックを待ってから更新後の行へ適用されるので、別要素への同時更新は両方残ります。消えるのは、アプリが古い文書を読み、手元で組み直して全体を書き戻す形です——この場合、先に入った変更が後勝ちで上書きされます。読みと書きを分けるなら SELECT … FOR UPDATE か版数の照合を挟みます。要素単位の更新が要件に入った時点で、明細テーブルへの切り出しを検討します。
5 / 5
問題を選択
次のセット →