SQL JSON — 深いマージ・差分抽出・述語検索の応用

QUESTION 6

深いマージ — ネストしたキーだけを安全に書き換える

jsonb_set|| 演算子親キーの欠損

前提知識 ▼

ネストした1つのキーだけを差し替える方法は2つあります。jsonb_set はパスで位置を指して値を置き換え、|| は同じ階層のキーを上書きします。|| の上書きはその階層だけで、値がオブジェクトなら中身を混ぜずに丸ごと置き換わります（浅いマージ）。

SELECT jsonb_set(json_col, '{parent_key,child_key}', 'false'::jsonb, true),
       json_col || '{"top_key": 1}'::jsonb
FROM   table_name;  -- 第4引数 true = 末端のキーが無ければ追加する

親が無ければ追加もされない：第4引数が効くのはパスの末端だけです。途中の parent_key そのものが無い行では、jsonb_set は何も変えずに元の値を返します。引数のいずれかが NULL のときは、結果全体が NULL になります。

問題 ▼

settings テーブルの prefs について、notify オブジェクトの中の email を false にした結果を求めてください。notify の他のキーと、最上位の他のキーはそのまま残します。notify を持たない行や prefs が NULL の行でも、email が false の notify を持つ結果にしてください。取得列は user_id, prefs、user_id 昇順で返してください。

使用テーブル

▸ settings

user_id	prefs（jsonb）
1	{"theme":"dark","notify":{"email":true,"push":true}}
2	{"notify":{"push":true}}
3	{"theme":"light"}
4	NULL

期待出力 ▼

user_id	prefs
1	{"theme": "dark", "notify": {"push": true, "email": false}}
2	{"notify": {"push": true, "email": false}}
3	{"theme": "light", "notify": {"email": false}}
4	{"notify": {"email": false}}

模範解答コード ▼

SELECT
  user_id,
  COALESCE(prefs, '{}'::jsonb)                             -- 列の NULL を空オブジェクトへ
    || jsonb_build_object(
         'notify',
         COALESCE(prefs -> 'notify', '{}'::jsonb) || '{"email": false}'::jsonb
       ) AS prefs                                          -- 部分木を作ってから最上位へ混ぜる
FROM     settings
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM settings                          → 4行読み込み
  2. COALESCE(prefs -> 'notify', '{}')      → notify 部分木、無ければ空オブジェクト
  3. || '{"email": false}'                  → 部分木の email だけを上書き
  4. jsonb_build_object('notify', ...)      → notify キー1つのオブジェクトへ包む
  5. COALESCE(prefs, '{}') || ...           → 最上位へ浅くマージ
  6. ORDER BY user_id                       → user_id の昇順
  */

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

SELECT
  user_id,
  COALESCE(prefs, '{}'::jsonb)
    || jsonb_build_object(
         'notify',
         COALESCE(prefs -> 'notify', '{}'::jsonb) || '{"email": false}'::jsonb
       ) AS prefs
FROM     settings
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM settings ▼

FROM settingssettings 全4行を読み込みます。notify を持つのは user 1 と user 2 だけで、user 3 は最上位に theme しか持たず、user 4 は列そのものが NULL です。

1 / 6

user_id	prefs（jsonb）
1	{"theme": "dark", "notify": {"push": true, "email": true}}
2	{"notify": {"push": true}}
3	{"theme": "light"}
4	NULL

全 4行読込

学習ポイント

深いマージは「取り出す → 混ぜる → 包む → 混ぜる」：PostgreSQL に再帰的なマージ演算子はありません。書き換えたい階層の部分木を取り出し、その中で上書きし、元のキーで包み直して最上位へ混ぜる——この4手が深いマージの一般形です。階層が深くなれば、包む手数がその分だけ増えます。

NULL は2か所で潰す：列そのものの NULL と、取り出した部分木の NULL は別の問題です。|| も jsonb_set も、引数に NULL が1つ混じれば結果は NULL になります。COALESCE を両方に置いて初めて、どの行も同じ形で出力できます。

jsonb_set が向く場面：親が確実に存在すると分かっているなら jsonb_set(prefs, '{notify,email}', 'false') のほうが短く読みやすい書き方です。スキーマ相当の制約（CHECK (prefs ? 'notify') など）で親の存在を保証できるなら、そちらを選びます。

アンチパターン

jsonb_set をそのまま当てる：jsonb_set(prefs, '{notify,email}', 'false', true) は、notify を持たない user 3 を何も変えずに返し、prefs が NULL の user 4 では結果ごと NULL になります。エラーは出ないので、更新できていないことに気付けません。

|| で1階層まるごと上書きする：prefs || '{"notify": {"email": false}}' は notify を丸ごと置き換えるため、user 1 と user 2 の push が消えます。|| のマージが浅いことを忘れると、意図しないキーの消失が静かに起きます。

実務コラム：設定値をJSONで持つときの既定値の置き場所

ユーザー設定をJSON列で持つと、項目の追加がマイグレーションなしで済みます。代償として、「まだ設定していない」行が大量に生まれ、参照側は毎回 NULL とキー欠損を処理することになります。既定値の置き場所は3択です。書き込み時に全キーを埋める（読み取りが単純／既定値の変更が全行 UPDATE）、読み取り時に '{既定値}'::jsonb || prefs で重ねる（既定値の変更が即座に効く／毎回コストを払う）、アプリ層で埋める（SQLからは既定値が見えない）。既定値が仕様として動くもの——通知のオン・オフのように、後から方針が変わる項目——は、読み取り時に重ねる形が最も事故が少なくなります。

QUESTION 7

jsonb_each — 更新前後を突き合わせて変更キーを洗い出す

jsonb_eachFULL JOINIS DISTINCT FROM

前提知識 ▼

監査ログや変更履歴では「どのキーが変わったか」を行として出したくなります。前後のドキュメントを jsonb_each で縦へ開き、キーで FULL JOIN すると、変更・追加・削除が1つの表に並びます。片側にしか無いキーは反対側が NULL になるため、比較は <> ではなく IS DISTINCT FROM を使います。

SELECT COALESCE(b.key, a.key) AS key, b.value, a.value
FROM      jsonb_each(before_col) AS b(key, value)
FULL JOIN jsonb_each(after_col)  AS a(key, value) ON a.key = b.key
WHERE  b.value IS DISTINCT FROM a.value;  -- NULL 同士は「同じ」と扱う

<> では追加・削除が落ちる：キーが片側にしか無い行では比較の片側が NULL になり、b.value <> a.value は UNKNOWN になって WHERE を通りません。値の変わったキーだけが残り、追加と削除が黙って消えます。

問題 ▼

record_versions テーブルは、1行に更新前の before_doc と更新後の after_doc を持ちます。レコードごとに変化したキーを1行ずつ出してください。値が変わったキー、追加されたキー、削除されたキーがすべて対象です。追加されたキーの before_value と、削除されたキーの after_value は NULL とします。取得列は record_id, key, before_value, after_value、record_id 昇順・key 昇順で返してください。

使用テーブル

▸ record_versions

record_id	before_doc（jsonb）	after_doc（jsonb）
101	{"name":"A","price":100,"tag":"x"}	{"name":"A","price":120,"tag":"x"}
102	{"name":"B","price":50}	{"name":"B","price":50,"color":"red"}
103	{"name":"C","stock":3}	{"name":"C"}
104	{"name":"D"}	{"name":"D"}

期待出力 ▼

record_id	key	before_value	after_value
101	price	100	120
102	color	NULL	"red"
103	stock	3	NULL

模範解答コード ▼

SELECT
  r.record_id,
  d.key,
  d.before_value,
  d.after_value
FROM   record_versions r
CROSS JOIN LATERAL (
  SELECT COALESCE(b.key, a.key) AS key,          -- 片側だけのキーも拾う
         b.value AS before_value,
         a.value AS after_value
  FROM      jsonb_each(r.before_doc) AS b(key, value)
  FULL JOIN jsonb_each(r.after_doc)  AS a(key, value) ON a.key = b.key
  WHERE  b.value IS DISTINCT FROM a.value       -- 変更・追加・削除をまとめて拾う
) AS d
ORDER BY r.record_id, d.key;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM record_versions              → 4行読み込み
  2. jsonb_each(before_doc)            → 更新前を キー・値 の行へ開く
  3. jsonb_each(after_doc)             → 更新後を キー・値 の行へ開く
  4. FULL JOIN ON a.key = b.key        → キーで突き合わせ、片側だけの行も残す
  5. WHERE IS DISTINCT FROM            → 値が同じキーを落とす（3行）
  6. SELECT record_id, key, 前後の値    → 4列を選択
  7. ORDER BY record_id, key           → レコード順・キー名順
  */

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

SELECT
  r.record_id,
  d.key,
  d.before_value,
  d.after_value
FROM   record_versions r
CROSS JOIN LATERAL (
  SELECT COALESCE(b.key, a.key) AS key,
         b.value AS before_value,
         a.value AS after_value
  FROM      jsonb_each(r.before_doc) AS b(key, value)
  FULL JOIN jsonb_each(r.after_doc)  AS a(key, value) ON a.key = b.key
  WHERE  b.value IS DISTINCT FROM a.value
) AS d
ORDER BY r.record_id, d.key;

LEGEND ▼

データ取得・読込対象

① FROM record_versions ▼

FROM record_versionsrecord_versions 全4行を読み込みます。1行に更新前と更新後のドキュメントが並んでいますが、この形のままではキー単位の比較ができません。

1 / 6

record_id	before_doc（jsonb）	after_doc（jsonb）
101	{"name": "A", "price": 100, "tag": "x"}	{"name": "A", "price": 120, "tag": "x"}
102	{"name": "B", "price": 50}	{"name": "B", "price": 50, "color": "red"}
103	{"name": "C", "stock": 3}	{"name": "C"}
104	{"name": "D"}	{"name": "D"}

全 4行読込

学習ポイント

IS DISTINCT FROM は NULL を値として比べる：通常の比較演算子は片側が NULL なら UNKNOWN を返しますが、IS DISTINCT FROM は「NULL 同士なら同じ、片方だけ NULL なら違う」と真偽で答えます。追加・削除・変更を1つの条件で拾えるのはこの性質のおかげです。

キーは両側から COALESCE で拾う：FULL JOIN の結果では、追加されたキーは b.key が NULL、削除されたキーは a.key が NULL です。片側だけを SELECT すると、その半分が NULL の行として出てしまいます。

比較できるのは最上位のキーだけ：jsonb_each は1階層しか開かないので、ネストしたオブジェクトは値ごと丸ごと比較されます。「どの子キーが変わったか」まで欲しいなら、再帰CTEで階層を降りるか、比較したい階層を #> であらかじめ取り出します。

アンチパターン

ドキュメント全体をテキストで比べる：before_doc::text <> after_doc::text では「変わったかどうか」しか分からず、どのキーが変わったかは出せません。json 型のままなら、空白やキー順の違いだけで「変わった」と判定されることもあります。

LEFT JOIN で済ませる：LEFT JOIN にすると更新後にだけ存在するキーが結合結果に現れず、追加が構造的に検出できません。エラーは出ず、差分が片側だけ見えている状態になります。

実務コラム：監査ログに何を残すか

変更履歴の残し方は、スナップショット（毎回ドキュメント全体を保存）と差分（変わったキーだけを保存）に大別されます。スナップショットは書き込みが単純で、ある時点の姿をそのまま復元できる代わりに、容量が更新回数に比例します。差分は軽い代わりに、ある時点の姿を得るには初期値から積み上げる必要があります。実務でよく採られるのは、スナップショットを保存しておき、参照時にこの設問のようなクエリで差分を導出する形です。書き込み経路を単純に保ったまま、「誰が何を変えたか」の画面を後から足せます。差分を保存する設計に進むのは、更新頻度が高くスナップショットの容量が問題になってからで間に合います。

QUESTION 8

削除演算子 — 公開用に不要なキーと要素を落とす

- 演算子#- 演算子パス指定

前提知識 ▼

JSON から一部を取り除く演算子は2つあります。- は最上位のキー（テキスト指定）または配列の要素（整数の添字）を落とし、#- はパス配列で指した1点を落としてネストの奥にも届きます。どちらも、指した先が存在しなければ何もせず、元の値をそのまま返します。

SELECT json_col - 'top_key'                 AS a,  -- 最上位のキーを落とす
       json_col #- '{parent_key,child_key}' AS b,  -- ネストの奥を落とす
       json_col #- '{arr_key,0}'            AS c   -- 配列の先頭要素を落とす
FROM   table_name;

- にテキストを渡すと対象で意味が変わる：相手がオブジェクトなら「そのキーを落とす」ですが、配列なら「その文字列と等しい要素をすべて落とす」になります。位置で落としたいときは、整数の添字か #- のパスを使います。

問題 ▼

documents テーブルの body から、社外へ出せない項目を落とした公開用JSONを作ってください。落とすのは、最上位の internal_memo キー、owner オブジェクトの中の email キー、tags 配列の先頭要素（内部ラベル）の3つです。該当が無い文書は、その部分を変えずに返します。取得列は doc_id, public_body、doc_id 昇順で返してください。

使用テーブル

▸ documents

doc_id	body（jsonb）
1	{"title":"T1","internal_memo":"社内限定","owner":{"name":"A","email":"a@example.com"},"tags":["draft","sql","index"]}
2	{"title":"T2","owner":{"name":"B"}}
3	{"title":"T3","internal_memo":"m3","owner":{"name":"C","email":"c@example.com"},"tags":["draft","json"]}
4	{"title":"T4","owner":{"name":"D","email":"d@example.com"},"tags":[]}

期待出力 ▼

doc_id	public_body
1	{"tags": ["sql", "index"], "owner": {"name": "A"}, "title": "T1"}
2	{"owner": {"name": "B"}, "title": "T2"}
3	{"tags": ["json"], "owner": {"name": "C"}, "title": "T3"}
4	{"tags": [], "owner": {"name": "D"}, "title": "T4"}

模範解答コード ▼

SELECT
  doc_id,
  ((body - 'internal_memo')      -- 最上位のキーを落とす
         #- '{owner,email}')     -- ネストの奥のキーを落とす
         #- '{tags,0}' AS public_body  -- 配列の先頭要素を落とす
FROM     documents
ORDER BY doc_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM documents            → 4行読み込み
  2. body - 'internal_memo'    → 最上位のキーを1つ落とす
  3. #- '{owner,email}'        → owner の下の email を落とす
  4. #- '{tags,0}'             → tags 配列の先頭要素を落とす
  5. SELECT doc_id, public_body → 2列を選択
  6. ORDER BY doc_id           → doc_id の昇順
  */

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

SELECT
  doc_id,
  ((body - 'internal_memo')
         #- '{owner,email}')
         #- '{tags,0}' AS public_body
FROM     documents
ORDER BY doc_id;

LEGEND ▼

データ取得・読込対象

① FROM documents ▼

FROM documentsdocuments 全4行を読み込みます。落とす対象の持ち方は文書ごとに違い、doc 2 は internal_memo も email も tags も持ちません。doc 4 の tags は空配列です。

1 / 5

doc_id	最上位のキー	owner の中身	tags
1	title, internal_memo, owner, tags	{"name": "A", "email": "a@example.com"}	["draft", "sql", "index"]
2	title, owner	{"name": "B"}	（キーなし）
3	title, internal_memo, owner, tags	{"name": "C", "email": "c@example.com"}	["draft", "json"]
4	title, owner, tags	{"name": "D", "email": "d@example.com"}	[]

全 4行読込

学習ポイント

該当が無くてもエラーにならない：削除演算子は「指した先が無ければ何もしない」という設計です。文書ごとに形が違うJSONでも、行を選別せずに同じ式を当てられます。裏返せば、パスの綴りを間違えても静かに素通りするので、想定した削除が起きているかは結果で確かめます。

パスの末端が整数なら配列の添字：'{tags,0}' はテキスト配列ですが、指した先が配列なら 0 は添字として解釈されます。負の値は末尾からの位置で、'{tags,-1}' は最後の要素です。範囲外の添字は、やはり何も起きません。

複数キーをまとめて落とす：最上位のキーが複数あるなら body - ARRAY['internal_memo','draft_note'] と、テキスト配列を1回で渡せます。パス指定の #- は1回に1点だけなので、階層をまたぐ削除は演算子を重ねます。

アンチパターン

ドット記法でネストを指す：body - 'owner.email' は「owner.email という名前の最上位キー」を探すので、何も削除されません。エラーは出ず、公開してはいけない値がそのまま残ります。ネストは必ずパス配列で指します。

マスクを SELECT に頼りきる：公開用の整形をクエリごとに書くと、1か所書き漏らした経路から生の値が出ます。マスク済みの形をビューとして固定し、公開経路にはそのビューだけを見せるほうが、漏れを構造で防げます。

実務コラム：削除と「見せない」は別のこと

この設問の削除は、あくまでクエリの出力から外す操作で、テーブルの中の値は元のままです。個人情報の削除要求に応えるなら、行そのものを UPDATE して値を消す必要があり、さらに過去のバックアップ、WAL、監査ログ、レプリカにも同じ値が残っていることを踏まえた手順が要ります。設計の段階でできる最も効果的な対策は、消す可能性のある項目を最初から別テーブルへ分けておくことです。そうすれば、削除は行の削除1回で済み、参照側は結合が1つ消えるだけで壊れません。

QUESTION 9

@@ 述語 — 数値の範囲でJSONを絞り込む

@@ 演算子JSONPath型の不一致

前提知識 ▼

包含演算子 @> が判定できるのは「この値を含むか」までで、数値の大小は書けません。JSONPath の述語を評価する @@ なら、>= や && を含む条件をそのまま渡せます。@? がパス式に該当する要素の有無を返すのに対し、@@ は述語そのものの真偽を返します。

SELECT * FROM table_name
WHERE  json_col @@ '$.num_key >= 1000 && $.text_key == "value"';

CREATE INDEX ON table_name USING gin (json_col jsonb_path_ops);
-- jsonb_path_ops は @>・@?・@@ に効く

型が違うと真でも偽でもない：$.num_key >= 1000 は JSON の数値どうしの比較です。値が "1000" と文字列で入っている行では結果が未定義になり、WHERE を通りません。キーそのものが無い行は偽になります。

問題 ▼

events テーブルから、props の amount が 1000 以上、かつ channel が web のイベントを取得してください。取得列は event_id, props、event_id 昇順で返してください。

使用テーブル

▸ events

event_id	props（jsonb）
1	{"channel":"web","amount":1500}
2	{"channel":"app","amount":2000}
3	{"channel":"web","amount":800}
4	{"channel":"web","amount":"1200"}
5	{"channel":"web"}
6	{"channel":"web","amount":1000}

期待出力 ▼

event_id	props
1	{"amount": 1500, "channel": "web"}
6	{"amount": 1000, "channel": "web"}

模範解答コード ▼

SELECT
  event_id,
  props
FROM   events
WHERE  props @@ '$.amount >= 1000 && $.channel == "web"'  -- 述語をそのまま評価
ORDER BY event_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM events                    → 6行読み込み
  2. WHERE props @@ '...'           → 述語が真の行だけ、2行に絞り込み
  3. SELECT event_id, props         → 2列を選択
  4. ORDER BY event_id              → event_id の昇順
  */

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

SELECT
  event_id,
  props
FROM   events
WHERE  props @@ '$.amount >= 1000 && $.channel == "web"'
ORDER BY event_id;

LEGEND ▼

データ取得・読込対象

① FROM events ▼

FROM eventsevents 全6行を読み込みます。props の形は行ごとに揃っておらず、event 4 は amount が文字列、event 5 は amount キーそのものがありません。

1 / 6

event_id	props（jsonb）
1	{"amount": 1500, "channel": "web"}
2	{"amount": 2000, "channel": "app"}
3	{"amount": 800, "channel": "web"}
4	{"amount": "1200", "channel": "web"}
5	{"channel": "web"}
6	{"amount": 1000, "channel": "web"}

全 6行読込

学習ポイント

述語は SQL の三値論理と地続き：JSONPath の比較も、真・偽・未定義の3つを返します。WHERE が通すのは真の行だけなので、未定義は偽と同じ結果になりますが、NOT で反転しても未定義のままです。「条件を満たさない行」を NOT で取りに行くと、型の合わない行がどちらの結果にも現れません。

@@ と @? の使い分け：@@ は述語式（$.amount >= 1000）の真偽を、@? はパス式（$.items[*] ? (@.qty > 0)）が1件でも該当するかを返します。同じことを2通りに書けますが、取り違えても構文エラーにはならず、結果だけが静かに変わります。@? に述語式を渡すと「真偽値そのものが1件の項目として存在する」ため、'{}'::jsonb @? '$.amount >= 1000' ですら真になり、条件を満たさない行まで通ります。逆に @@ へ真偽値以外を返すパス式を渡すと結果は NULL になり、該当するはずの行が落ちます。

索引は jsonb_path_ops：既定の jsonb_ops はキー単独の存在検索（?）まで扱えるぶん索引が大きく、jsonb_path_ops は @>・@?・@@ に絞って小さく速くなります。値で絞る検索しかしないなら後者を選びます。

アンチパターン

取り出してからキャストして比較する：(props ->> 'amount')::int >= 1000 は読みやすい代わりに、GINインデックスが効かず全行を評価します。さらに、文字列以外の値が混ざった瞬間にキャストが実行時エラーで落ち、1行の異常でクエリ全体が失敗します。

型の揺れを検索側で吸収する：数値と文字列が混在する列に対し、両方の書き方を OR で並べる回避策は、条件が増えるたびに複雑になります。書き込み側に CHECK (jsonb_typeof(props -> 'amount') = 'number') のような制約を置き、入口で揃えるほうが安く済みます。

実務コラム：JSON列に索引を張る前に決めること

GINインデックスは、JSON列の中身を検索可能にする強力な手段ですが、更新のたびに索引の書き換えが発生し、書き込みが重くなります。張る前に決めておきたいのは2点です。1つは「どのキーで検索するか」——特定のキーだけなら、列全体のGINより CREATE INDEX ON events (((props ->> 'channel'))) のような式インデックスのほうが小さく効きます。もう1つは「そのキーは本当にJSONの中にあるべきか」——常に存在し、常に検索されるキーは、生成列（GENERATED ALWAYS AS ((props ->> 'channel')) STORED）で普通の列として取り出せます。型も制約も索引も統計情報も効くようになり、JSONで持つ柔軟性は残ります。

QUESTION 10

配列の包含 — 複数のタグをすべて持つ行を選ぶ

@> 演算子?& 演算子重複と順序

前提知識 ▼

JSON配列に対する @> は「右辺の要素をすべて含むか」を判定します。集合としての判定なので、要素の順序も重複も結果に影響しません。文字列だけの配列なら ?&（すべてを持つ）・?|（どれかを持つ）でも同じことが書け、どちらもGINインデックスが効きます。

SELECT * FROM table_name
WHERE  json_col -> 'arr_key' @> '["a","b"]'::jsonb;
-- 文字列の配列なら ?& array['a','b'] でも同じ判定になる

スカラーとの包含は形が違う：json_col @> '{"arr_key":"a"}' は「arr_key の値が文字列 a と等しい」という意味なので、配列を持つ行には一致しません。配列の中身を見るなら、右辺も配列で書きます。

問題 ▼

articles テーブルの meta は tags 配列を持ちます。sql と postgres を両方持つ記事を取得してください。取得列は article_id, tags、article_id 昇順で返してください。tags は meta の中の配列をそのまま返します。

使用テーブル

▸ articles

article_id	meta（jsonb）
1	{"title":"インデックス設計","tags":["sql","postgres","index"]}
2	{"title":"SQL入門","tags":["sql"]}
3	{"title":"実行計画の読み方","tags":["postgres","sql"]}
4	{"title":"チューニング事例","tags":["sql","sql","postgres"]}
5	{"title":"下書き","tags":[]}
6	{"title":"メモ"}

期待出力 ▼

article_id	tags
1	["sql", "postgres", "index"]
3	["postgres", "sql"]
4	["sql", "sql", "postgres"]

模範解答コード ▼

SELECT
  article_id,
  meta -> 'tags' AS tags
FROM   articles
WHERE  meta -> 'tags' @> '["sql","postgres"]'::jsonb  -- 両方を含む（順序・重複は無視）
ORDER BY article_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM articles                  → 6行読み込み
  2. meta -> 'tags'                 → 配列を取り出す（キーが無ければ NULL）
  3. WHERE ... @> '["sql","postgres"]' → 両方を含む3行に絞り込み
  4. SELECT article_id, tags        → 2列を選択
  5. ORDER BY article_id            → article_id の昇順
  */

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

SELECT
  article_id,
  meta -> 'tags' AS tags
FROM   articles
WHERE  meta -> 'tags' @> '["sql","postgres"]'::jsonb
ORDER BY article_id;

LEGEND ▼

データ取得・読込対象

① FROM articles ▼

FROM articlesarticles 全6行を読み込みます。タグの持ち方は行ごとにばらつき、article 4 は sql が2回、article 5 は空配列、article 6 は tags キー自体がありません。

1 / 5

article_id	meta（jsonb）
1	{"tags": ["sql", "postgres", "index"], "title": "インデックス設計"}
2	{"tags": ["sql"], "title": "SQL入門"}
3	{"tags": ["postgres", "sql"], "title": "実行計画の読み方"}
4	{"tags": ["sql", "sql", "postgres"], "title": "チューニング事例"}
5	{"tags": [], "title": "下書き"}
6	{"title": "メモ"}

全 6行読込

学習ポイント

@> は集合の包含：右辺のすべての要素が左辺にあれば真で、余分な要素があっても構いません。順序も重複も無視されるため、["sql","sql"] を含むかという問いは ["sql"] を含むかと同じ結果になります。「ちょうど一致」を判定したいなら = を使います。

?& と ?| で読みやすくする：文字列だけの配列なら meta -> 'tags' ?& array['sql','postgres']（すべて持つ）、?|（どれか持つ）と書けます。「AND 検索」「OR 検索」の意図がそのまま演算子に現れるので、タグ検索では読みやすい選択肢です。

索引を効かせるなら左辺を動かさない：GINインデックスは列そのもの、または (meta -> 'tags') のような式に対して張ります。WHERE meta @> '{"tags":["sql","postgres"]}' と列全体で書けば列のGINが、式インデックスを張るなら式と同じ形で書けばそちらが使われます。判定式の形と索引の形を揃えるのが条件です。

アンチパターン

展開して COUNT で数える：配列を jsonb_array_elements_text で開き、該当タグを数えて 2 と比べる書き方は、article 4 のような重複で 3 と数えてしまいます。重複を DISTINCT で潰す手当てが要るうえ、索引も効きません。

テキストで LIKE 検索する：meta::text LIKE '%sql%' は postgresql や、タイトルに含まれる SQL入門 にも一致します。JSONの構造を無視した検索は、必ず過剰一致か取りこぼしのどちらかを起こします。

実務コラム：タグをJSON配列で持つか、中間テーブルにするか

タグをJSON配列で持つ最大の利点は、記事1行の読み書きで完結することです。GINインデックスがあれば @> のAND検索も十分速く、タグの追加はアプリ側の配列操作だけで済みます。限界が来るのは、タグ側を主語にしたとき——「タグ一覧を使用数の多い順に出す」「タグをリネームする」「タグに説明文や色を持たせる」といった要求です。どれも全行の展開か全行更新になり、タグ自体の属性は置き場所がありません。記事から見るだけなら配列、タグを管理対象として扱い始めたら tags と article_tags の2テーブル——分岐点は「タグに名前以外の情報を持たせたくなったとき」です。