jsonb は PostgreSQL のバイナリ形式の JSON 列型です。-> (jsonb を返す) と ->> (text を返す) でフィールドを読み、包含演算子 @> で行を探し、jsonb_set や || でフィールドを変更します。GIN インデックスを作ると、こうしたクエリが速くなります。
一致するのは注文 1 と 3 です。data->'customer' は入れ子のオブジェクトを jsonb で返し、->>'name' は名前をテキストで取り出します。->> にキャストを組み合わせると、合計を数値にできます。
JSONB から値を取り出す
| 演算子 | 戻り値 | 例 |
|---|---|---|
-> キーまたは添字 | jsonb | data->'customer', data->'tags'->0 |
->> キーまたは添字 | text | data->>'status' |
#> パス | jsonb | data#>'{customer,city}' |
#>> パス | text | data#>>'{customer,city}' |
[...] 添字 (PostgreSQL 14 以降) | jsonb | data['customer']['city'] |
配列の添字は 0 から始まり、負の添字は末尾から数えます (-> -1 は最後の要素)。キーが存在しないとエラーではなく SQL の NULL になるので、上の注文 3 の city は NULL です。
テキストと比較するときやキャストするときは ->> を使います: data->>'status' = 'paid'。続けてたどるときは -> を使います。よくある間違いが data->'status' = 'paid' です。PostgreSQL は 'paid' を JSON として読みますが、引用符のない単語は正しい JSON ではないので、クエリは "invalid input syntax for type json" で失敗します。
JSON の中身で行を探す
@> は左の値が右の値を含むかどうかを調べます。どの深さでも、配列の中でも使え、GIN インデックスも効きます。
商品 1 と 2 には M サイズと価格があり、30 を超えるのは 2 だけです。検索に使う演算子は次のとおりです。
@>は含む、<@は含まれる。?はキー (または配列内の文字列) が存在する、?|はいずれかのキーが存在する、?&はすべてのキーが存在する。@?と@@は JSON パスクエリを実行します (PostgreSQL 12 以降)。ドキュメント内の値の比較に使います。
JSONB 内のフィールドを更新する
PostgreSQL は更新のたびに値全体を書き直しますが、次の関数を使えば変更する部分だけを書けます。
jsonb_set(target, path, new_value)はパス上の1つの値を置き換えます。-はキーを削除し、#-は'{notify,sms}'のような入れ子のパスを削除します。||は最上位のキーを追加または上書きします。
PostgreSQL 14 以降は添字で代入することもできます: SET prefs['notify']['email'] = 'false'。
落とし穴が2つあります。
- 新しい値が SQL の
NULLだとjsonb_setはNULLを返し、そのUPDATEで列全体が消えます。COALESCE(to_jsonb(x), 'null')を渡すか、デフォルトで JSON のnullを保存するjsonb_set_lax(PostgreSQL 13 以降) を使います。 jsonb_setは存在しない最後のキーは追加しますが、存在しない親は作りません。{}に'{a,b}'を設定しても変わりません。添字による代入なら親も作られます。
JSON の配列を行に展開する
jsonb_array_elements は配列の要素ごとに1行を返し、jsonb_to_recordset はオブジェクトの配列を型付きの列に変換します。PostgreSQL 17 以降は、標準 SQL の JSON_TABLE で両方を1か所で書けます。
A-1 は 3 個、B-2 は 1 個売れました。逆方向には、jsonb_build_object('id', id, 'name', name) で列からオブジェクトを作り、jsonb_agg で行を配列にまとめます (string_agg と array_agg を参照)。
GIN で JSONB にインデックスを作る
列全体に作った GIN インデックスは @>、?、?|、?&、@?、@@ に効きます。
CREATE INDEX orders_data_idx ON orders USING gin (data);
-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);
-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));
クエリはインデックスが対応する形で書きます。WHERE data @> '{"status": "paid"}' は GIN インデックスを使えます。WHERE data->>'status' = 'paid' は使えず、式インデックスが必要です。(data->>'total')::numeric > 50 のような範囲の条件には、まったく同じ式に対する式インデックスが必要です。
JSON と JSONB の違い
json | jsonb | |
|---|---|---|
| 保存形式 | 入力したテキスト | 解析済みのバイナリ形式 |
| 空白、キーの順序、重複キー | 保持する | 取り除く。重複は最後のものが残る |
| 書き込み | 少し速い (検証のみ) | 入力時に解析する |
| 読み込みと検索 | アクセスのたびに解析し直す | 速い |
@>、? と GIN インデックス | 使えない | 使える |
json はテキストをそのまま返し、jsonb は "a": 3 だけを残してキーを並べ替えます。保存した Webhook のペイロードのように元のテキストをバイト単位でそのまま返す必要がない限り、jsonb を使います。すべての行が持つフィールドや、外部キーが必要なフィールドは通常の列にしておきます。公式の JSON 関数リファレンス にすべての演算子と関数が載っています。
よくある質問
PostgreSQL の -> と ->> の違いは何ですか?
-> は値を jsonb で返すので、そのまま続けてたどれます (data->'customer'->'city')。->> は text で返すので、文字列と比較したり数値にキャストしたりするときに使います: (data->>'total')::numeric。
JSONB の配列に値が含まれているか調べるには?
包含演算子を使います。data->'tags' @> '["gift"]'、またはドキュメントの最上位から調べるなら data @> '{"tags": ["gift"]}' です。文字列の配列なら data->'tags' ? 'gift' でも書けます。data に GIN インデックスを作ると、2つ目の書き方が速くなります。
JSONB はキーの順序を保持しますか?
保持しません。jsonb は独自の順序でキーを保存し、空白を取り除き、重複したキーは最後のものだけを残します。元のテキストをそのまま取り出す必要があるなら json 型を使います。
JSONB にキーが存在するか調べるには?
data ? 'key' は、最上位のオブジェクトにそのキーがあれば、値が JSON の null でも true になります。?| は配列を受け取り、どれか1つのキーがあれば true、?& はすべてのキーがあれば true です。
JSONB 内のフィールドに外部キーや NOT NULL 制約を付けられますか?
直接は付けられません。CHECK 制約でフィールドを検査することはできます (CHECK (data ? 'email')) が、外部キーには本物の列が必要です。すべての行が持ち、他のテーブルから参照されるフィールドは通常の列にしておきます。