Practical PostgreSQL JSONB guide (difference from json, operators, GIN/expression index design, type-safe boundary, v18 support)

"The schema won't settle," "settings and attributes are variable" — at such times, before adding a dedicated NoSQL, remember PostgreSQL's JSONB. While keeping RDB transactions, joins, and constraints, you can store semi-structured data in one column and even index it.

But JSONB is not "a convenient bag you can put anything in." Misjudge the choice vs. normalized columns, the pitfalls of the containment/existence operators, and the judgment of index design, and you get a slow, fragile table. This article organizes the decision criteria to use JSONB at production quality, faithful to the PostgreSQL official documentation. It's a practical theme continuous with the database / RLS cluster.

Rules for this article: type behavior, operators, indexes, functions, and PostgreSQL 18 changes are all based on the PostgreSQL 18 official documentation (as of June 2026). Because JSON functions differ greatly by version (JSON_TABLE is PG17, IS JSON is PG16, etc.), versions are noted.

1. json and jsonb: jsonb by default

PostgreSQL has two types, json and jsonb. The official states the difference clearly.

The json type stores an exact copy of the input text, and processing functions re-parse on each execution. Meanwhile jsonb is stored in a decomposed binary form, slightly slower at the conversion cost on input, but processing is much faster because no re-parsing is needed. jsonb also supports indexes.

And "which to use" is also stated explicitly.

In general, most applications should store in jsonb, except when there are special requirements such as legacy assumptions about object-key order.

Conclusion: when in doubt, jsonb. But jsonb has normalization side effects (official).

• Doesn't preserve whitespace (formatting is lost).
• Doesn't preserve key order.
• Doesn't preserve duplicate keys (with the same key multiple times, only the last value survives).
• Numbers become the numeric type internally (rejects out-of-range, normalizes E notation, preserves meaningless trailing 0s).

SELECT '{"b":1, "a":2, "a":3}'::jsonb;
-- → {"a": 3, "b": 1}   キー順は変わり、重複 a は最後の 3 が勝つ

2. Access operators: don't confuse -> and ->>

The most frequent operation. -> returns the JSON type, and ->> returns text — this difference is critical.

-- 比較・WHERE には ->>（テキスト）。-> はJSON型なので文字列比較に使えない
SELECT * FROM users WHERE profile ->> 'plan' = 'pro';

-- ネストはパスで一発
SELECT profile #>> '{address,city}' AS city FROM users;

-- 添字アクセス（PG14+）。代入も可能
UPDATE users SET profile['plan'] = '"enterprise"'::jsonb WHERE id = 1;

The extraction operators return NULL if the key or element doesn't exist (no error). This is safe, but if you need to distinguish "the key is absent" from "the value is NULL," confirm with ? (the existence operator).

3. Containment @> and existence ?: the pitfall is here

The protagonists of JSONB search are containment @> and existence ?, but both have counterintuitive behavior.

Containment @>: ignores order, but doesn't flatten nesting

The official principle: "the contained side must match the containing side in structure and data content (some non-matching elements may be discarded). The order of array elements is ignored, and duplicate elements are treated as once."

-- オブジェクトの部分一致：tags に "pg" を含み plan が "pro" の行
SELECT * FROM docs WHERE data @> '{"plan":"pro"}';
SELECT * FROM docs WHERE data @> '{"tags":["pg"]}';   -- 配列の順序は無視される

The pitfall: @> doesn't flatten nesting. The official example:

SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;    -- false！（ネストの中の [1,3] とは別物）
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;  -- true（同じネスト階層で問う）
SELECT '{"foo":{"bar":"baz"}}'::jsonb @> '{"bar":"baz"}'::jsonb;  -- false（階層が違う）

In other words, the querying side must also be written in the same hierarchical structure as the other side.

Existence ?: looks only at top-level keys

Official: "? asks whether a string appears as a key or array element at the top level of the jsonb value. It doesn't look at nested keys."

SELECT * FROM docs WHERE data ? 'email';       -- トップレベルに email キーがあるか
SELECT * FROM docs WHERE data ?| array['a','b'];  -- a か b のどちらかが存在
SELECT * FROM docs WHERE data ?& array['a','b'];  -- a と b の両方が存在
-- data ? 'nested_key' は、ネスト内の nested_key を見つけられない（トップレベルのみ）

To ask about nested key existence, dig the path with #> then use ?, or use the SQL/JSON path (@?) described below.

4. Updates: jsonb_set / || / - / #-

Partial JSONB updates are done with dedicated functions (note that UPDATE itself rewrites the whole row = creates a new version under MVCC).

-- 値の差し替え／追加（既定で path が無ければ作る）
UPDATE users SET profile = jsonb_set(profile, '{plan}', '"pro"') WHERE id = 1;

-- マージ（|| は浅いマージ。重複キーは右側が勝つ。再帰はしない）
UPDATE users SET profile = profile || '{"verified": true}' WHERE id = 1;

-- キー削除（- はキー/要素、#- はパス）
UPDATE users SET profile = profile - 'temp_token' WHERE id = 1;
UPDATE users SET profile = profile #- '{address,old_zip}' WHERE id = 1;

Construction and aggregation are also rich. to_jsonb() (convert any SQL value), jsonb_build_object() (alternating key/value), jsonb_agg() (rows into a JSON array), jsonb_object_agg() (pairs into a JSON object).

-- 行を1つの JSON 配列に集約してアプリに返す（N+1 を避ける定番）
SELECT u.id, jsonb_agg(jsonb_build_object('id', o.id, 'amount', o.amount)) AS orders
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

PostgreSQL 18 additions: make jsonb's null castable to a scalar type as NULL (formerly an error). Add an option to json{b}_strip_nulls to also remove nulls inside arrays. SIMD acceleration for processing long JSON strings.

5. SQL/JSON path: complex queries

For conditions beyond simple key search ("any of the array is over 5," etc.), use the SQL/JSON path language. The operators are @? (does the path return anything = boolean) and @@ (the truth of the path predicate).

-- items 配列に price > 1000 の要素が存在するか
SELECT * FROM carts WHERE data @? '$.items[*] ? (@.price > 1000)';

-- 関数版：マッチした値を行として取り出す
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 1000)') FROM carts;

? (condition) is the filter expression, and @ points at "the value currently being evaluated." Paths have lax (default) and strict; lax absorbs structural mismatches (treating an array and a single element the same, etc.), and strict throws a structural error.

Using JSON_TABLE (PG17), you can expand JSONB into a set of rows (usable as a table in the FROM clause). It's a powerful tool when you want to relationally normalize a jsonb array.

-- jsonb 配列を行に展開（JSON_TABLE は PostgreSQL 17 以降）
SELECT t.* FROM orders,
  JSON_TABLE(line_items, '$[*]'
    COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')) AS t;

6. Index design: GIN or expression B-tree (the most important judgment)

JSONB performance is decided by the index. The axis is simple — "flexibly search any key/containment" → GIN, "fetch one fixed key by equality/range" → expression B-tree.

GIN: the protagonist of flexible search (two operator classes)

The official correspondence table is the core of the judgment.

Official: "jsonb_path_ops is usually much smaller than a jsonb_ops index over the same data, and search is more specific (especially for queries containing common keys), so search performance is usually better than the default class."

-- 万能（存在 ? も使いたい）：既定の jsonb_ops
CREATE INDEX idx_docs_data ON docs USING gin (data);

-- 包含 @> 中心で速度・サイズ重視：jsonb_path_ops
CREATE INDEX idx_docs_data_path ON docs USING gin (data jsonb_path_ops);
-- これで WHERE data @> '{"plan":"pro"}' が高速化

jsonb_path_ops's only weakness (official): it doesn't create index entries for value-less structures like {"a": {}}, so searching for them becomes a full index scan. Choose based on whether you need the existence operator (?).

Expression B-tree: cheapest for equality/range on a fixed key

If the queried key is fixed like "always equality-search by data->>'status'," an expression B-tree is smaller and faster than GIN.

-- 単一キーの等値/範囲検索：式B-tree が最適（->> はテキストなので必要なら型キャスト）
CREATE INDEX idx_docs_status ON docs ((data ->> 'status'));
-- 数値・日付として範囲検索するならキャストして索引
CREATE INDEX idx_docs_priority ON docs (((data ->> 'priority')::int));

Judgment summary:

• The search keys are diverse and dynamic, heavy use of containment/existence → GIN (default jsonb_ops; jsonb_path_ops if @>-centric).
• The search keys are fixed to one to a few, equality/range → expression B-tree (also consider promoting that column to a normalized column).

7. Design guidance: when to use JSONB and when to use a column

JSONB is powerful, but JSONB-for-everything is a design failure. The official, while "recommending the coexistence of both approaches," pins it down:

Even when maximum flexibility is desired, JSON documents are recommended to have a somewhat fixed structure.

Further, a point directly tied to performance and contention:

Any update takes a row-level lock on the whole row. To reduce lock contention between update transactions, consider limiting JSON documents to a manageable size. Ideally, each JSON document should represent atomic data that cannot be subdivided further by business rules.

Practical guidance:

-- 良い例：検索/結合/制約に使う列は正規化、可変の付帯情報だけ JSONB
CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,           -- 検索・結合キーは列
  price       numeric(12,2) NOT NULL CHECK (price >= 0),
  status      text NOT NULL DEFAULT 'draft',
  attributes  jsonb NOT NULL DEFAULT '{}'     -- カテゴリ毎に異なる可変属性だけ JSONB
);
CREATE INDEX idx_products_attributes ON products USING gin (attributes jsonb_path_ops);

8. The type-safe boundary: JSONB is "untrustworthy external input"

Seen from TypeScript, a JSONB column is unknown. The type doesn't guarantee what's in the DB. So the iron rule is to validate the moment you read it — in CLAUDE style, "narrow at the boundary."

import { z } from "zod";

// JSONB に入れる構造を単一の真実源として定義
const ProductAttributes = z.object({
  color: z.string().optional(),
  weightGram: z.number().int().positive().optional(),
  certifications: z.array(z.string()).default([]),
});
type ProductAttributes = z.infer<typeof ProductAttributes>;

// DB から読んだ jsonb は unknown。必ず parse して型を確定させる
export function parseAttributes(raw: unknown): ProductAttributes {
  return ProductAttributes.parse(raw); // 不正なら例外。壊れたデータをアプリに流さない
}

// 書き込みもパラメータ化（SQLインジェクション対策）。値は JSON.stringify で渡す
export async function setAttributes(id: number, attrs: ProductAttributes) {
  const valid = ProductAttributes.parse(attrs); // 書く前にも検証
  await pool.query(
    "UPDATE products SET attributes = $1::jsonb WHERE id = $2",
    [JSON.stringify(valid), id],
  );
}

Because JSONB is flexible, its biggest risk is "the schema crumbles unnoticed." Make the Zod schema the single source of truth and validate at both read and write ends, and you can keep flexibility while making it hard to break. Combining a minimal DB-side constraint like CHECK (jsonb_typeof(attributes) = 'object') makes it robust too.

9. Conclusion

• jsonb by default. json keeps text and re-parses; jsonb is decomposed binary, fast + indexable. jsonb doesn't preserve key order or whitespace, and the last wins for duplicate keys.
• Don't confuse -> and ->>. Containment @> ignores order but doesn't flatten nesting, and existence ? is top-level only — remember these two pitfalls.
• Index judgment: flexible search = GIN (default jsonb_ops; the small, fast jsonb_path_ops if @>-centric). Equality/range on a fixed key = expression B-tree.
• Design: put values used in search/join/constraint into normalized columns. Keep JSONB to "indivisible atomic data" (updates lock the whole row).
• Type safety: a read jsonb is unknown. Validate at the boundary with Zod, and parameterize writes.
• PG18: scalar cast of jsonb null, json_strip_nulls array support, SIMD acceleration for long JSON.

JSONB is a tool to have both "the RDB's rigidity" and "the schemaless softness" in one table. Next, on to declarative partitioning that physically splits ultra-large tables.

References (PostgreSQL 18 official documentation)

• 8.14. JSON Types (json vs jsonb, containment/existence, indexing)
• 9.16. JSON Functions and Operators
• 8.14.4. jsonb Indexing
• E.4. Release 18 (JSON-related changes)