Day11 後半のゴール
「“JSONの中身をSQLで触る”感覚を手に入れる」
前半で「JSONをカラムとして持つ」「jsonbを基本にする」というところまでは来ました。
後半ではいよいよ、「その中身をSQLでどう読むか・どう検索するか」をやっていきます。
ここでのゴールはこうです。-> と ->> の違いを、感覚レベルで説明できる。
ネストしたJSONから値を取り出すイメージが持てる。
「JSONの中身で検索する」基本パターンを1つ自分のものにする。
JSONの取り出し基本：-> と ->>
「JSONのまま返す」か「テキストとして返す」か
まずは一番よく使う2つの演算子からいきます。
->->>
例として、前半で作った users テーブルの extra を使います。
SELECT
id,
email,
extra
FROM users;
ある行の extra がこうだとします。
{
"age": 25,
"tags": ["premium", "beta_user"],
"profile": {
"twitter": "@taro",
"location": "Tokyo"
}
}
ここから age を取り出すとき、こう書けます。
SELECT
extra->'age' AS age_json,
extra->>'age' AS age_text
FROM users
WHERE id = 1;
ここが超重要ポイントです。
extra->’age’
JSONとして返す（型は jsonb）。
数値でも文字列でも、「JSONの値」として扱われる。
extra->>’age’
テキストとして返す（型は text）。
「SQLの文字列」として扱える。
つまり、
「さらにJSONとして掘りたい」「JSON関数に渡したい」→ ->
「WHEREで比較したい」「CASTして数値にしたい」→ ->>
という使い分けになります。
ネストしたJSONから値を取り出す
「-> で潜って、最後だけ ->> で文字列にする」
profile.twitter のようなネストした値も、演算子をつなげて取れます。
SELECT
extra->'profile'->>'twitter' AS twitter
FROM users
WHERE id = 1;
ここでやっていることは、
extra->'profile' で、{"twitter": "...", "location": "..."} というJSONオブジェクトを取り出す。
その結果に対して ->>'twitter' で、"@taro" をテキストとして取り出す。
「途中までは -> で潜っていき、最後だけ ->> で文字列にする」というパターンは、ネストJSONを触るときの基本形です。
配列も同じように扱えます。tags[0] を取りたいなら、こうです。
SELECT
extra->'tags'->>0 AS first_tag
FROM users
WHERE id = 1;
->>0 の 0 は「配列の0番目の要素」を意味します。
JSONの配列は0始まりなので、ここはプログラミングと同じ感覚でOKです。
JSONの中身で検索する
「“タグに premium を含むユーザー”を探す」
「JSONを持つ」だけでなく、「JSONの中身で検索する」のがPostgreSQLの真骨頂です。
例えば、「tags に ‘premium’ を含むユーザー」を探したいとします。
extra がこういうJSONを持っている前提です。
{
"age": 25,
"tags": ["premium", "beta_user"]
}
一番素直な書き方は、@>（包含）演算子を使う方法です。
SELECT *
FROM users
WHERE extra->'tags' @> '["premium"]'::jsonb;
@> は、「左側のJSONが、右側のJSONを“含んでいるか”」を判定します。
extra->'tags' は ["premium", "beta_user"] というJSON配列。'["premium"]'::jsonb は ["premium"] というJSON配列。
「["premium", "beta_user"] は ["premium"] を含んでいるか？」→ YES なのでマッチします。
この書き方の良さは、「JSONとして意味のある形で条件を書ける」ことです。
文字列の部分一致ではなく、「配列として ‘premium’ を含むか」をちゃんと見てくれます。
JSONのキーの存在チェック
「“このユーザーは age を持っているか？”を調べる」
JSONは「ユーザーによって持っているキーが違う」ことがよくあります。
例えば、「age を持っているユーザーだけを抽出したい」というケースです。
このときは、? 演算子が使えます。
SELECT *
FROM users
WHERE extra ? 'age';
extra ? 'age' は、「extra のトップレベルに ‘age’ というキーが存在するか？」をチェックします。
ネストしたキーを見たいときは、-> と組み合わせます。
例えば、「profile の中に twitter があるユーザー」を探すなら、こうです。
SELECT *
FROM users
WHERE extra->'profile' ? 'twitter';
ここでのポイントは、「JSONの構造を前提にした存在チェックができる」ということです。
NULLチェックや文字列検索ではなく、「キーとして存在するか」を見られるのは、JSONならではです。
JSONの値を数値として扱う
「age を取り出して、数値比較する」
->> で取り出した値は text なので、そのままでは数値比較ができません。
「age が 20 以上のユーザー」を取りたいときは、キャストが必要です。
SELECT
id,
email,
(extra->>'age')::integer AS age
FROM users
WHERE (extra->>'age')::integer >= 20;
ここでの流れは、
extra->>'age' で “25” のような文字列を取り出す。::integer で整数にキャストする。
WHERE句で数値比較する。
このパターンは、「JSONに入っている数値をちゃんと数値として扱いたい」ときの基本形です。
注意点として、「age が入っていないユーザー」や「数値でない値が入っているユーザー」がいると、キャストでエラーになる可能性があります。
現場では、WHERE extra ? 'age' で「age を持っているユーザーだけ」に絞ってからキャストする、などの工夫をします。
JSONを“全部TEXTで持つ”のと何が違うか
「構造を理解しているからこそ、安全で速くなる」
ここまで見てきたように、JSON型（特にjsonb）を使うと、
キーの存在チェック（?）
配列の包含チェック（@>）
ネストした値の取り出し（->, ->>）
といった操作を、「JSONとして意味のある形」で書けます。
もしこれを全部TEXTで持っていたらどうなるか。LIKE '%"premium"%' のような、かなり危うい文字列検索に頼ることになります。
文字列検索だと、
たまたま別のキーの中に “premium” が入っていてもヒットしてしまう。
JSONとして壊れていても気づかない。
インデックスも効きにくい。
といった問題が出てきます。
JSON型を使うことで、
INSERT時に構文チェックが入る。
「配列として含むか」「キーとして存在するか」を正しく判定できる。
jsonbなら、専用インデックスで高速に検索できる。
というメリットが得られます。
Day11 後半では、「JSONをTEXTで持つのは“最後の手段”で、PostgreSQLなら“ちゃんとJSONとして扱う”方が安全で強い」という感覚を持っておいてほしいです。
JSON操作の“最初の一歩パターン”をまとめる
「この3つだけ、まずは体に入れる」
前半・後半を通して、最初に覚えるべきJSON操作のパターンを、ぎゅっと3つに絞るとこうなります。
1つ目：値の取り出しextra->'profile'->>'twitter'
「途中までは ->、最後だけ ->>」でネストを掘る。
2つ目：配列の包含検索extra->'tags' @> '["premium"]'::jsonb
「tags に ‘premium’ を含むユーザー」をJSONとして意味のある形で書く。
3つ目：キーの存在チェックextra ? 'age'
「このユーザーは age を持っているか？」を安全に判定する。
この3つが使えるだけで、「JSONをDBに入れたけど、結局アプリでしか使ってない」という状態から一歩抜け出せます。
Day11 後半のまとめ
PostgreSQLのJSON（特にjsonb）は、「中身を理解している」からこそ、extra->'age'（JSONとして取り出す）と extra->>'age'（テキストとして取り出す）を使い分けてネストした値を安全に取り出せるし、extra->'tags' @> '["premium"]'::jsonb のように「配列として ‘premium’ を含むか」を正しく検索できる。
さらに、extra ? 'age' でキーの存在をチェックしたり、(extra->>'age')::integer >= 20 のようにキャストして数値比較したりすることで、「柔軟に持ったJSONの中身を、SQLの世界に引き上げてロジックに使う」ことができる。
TEXTとして雑にJSONを突っ込むのではなく、「jsonbで構文チェック＋構造を前提にした検索・比較」を行うことで、安全性と表現力が一気に上がる――Day11 後半では、この“取り出し（-> / ->>）”“検索（@> / ?）”“数値として扱う（キャスト）”の3パターンを、自分の中の標準装備にするところまでをゴールにする。