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

> A practical guide to using PostgreSQL's JSONB correctly in production. Faithful to the official docs, with real code it explains the difference between json and jsonb, operators like ->/->>/@>/?, the pitfalls of containment/existence, updates like jsonb_set, SQL/JSON paths, judging index design between GIN (jsonb_ops and jsonb_path_ops) and expression B-tree, the choice vs. normalized columns, and a type-safe boundary with TypeScript/Zod.

- Published: 2026-06-22
- Author: 友田 陽大
- Tags: PostgreSQL, アーキテクチャ設計, TypeScript
- URL: https://tomodahinata.com/en/blog/postgresql-jsonb-operators-gin-index-design-guide
- Category: PostgreSQL internals & performance
- Pillar guide: https://tomodahinata.com/en/blog/postgresql-performance-tuning-production-guide

## Key points

- Use jsonb by default (json keeps text and re-parses each time; jsonb is decomposed binary, faster to process and can be indexed). jsonb doesn't preserve key order or whitespace, and the last wins for duplicate keys.
- For access, -> is the JSON type and ->> is text, #>/#>> are paths, data['key'] is subscript (PG14+). Beware the pitfalls: containment @> 'ignores order' for structural match, and existence ? looks only at top-level keys (not nested).
- Index judgment: 'flexibly search any key/containment' → GIN. The default jsonb_ops supports ? ?& ?| @> @? @@; jsonb_path_ops is only @> @? @@ but small and fast. 'Equality/range on one fixed key' → expression B-tree (col->>'field') is cheaper.
- Update with jsonb_set / || / - / #-. Because jsonb's row lock applies to the whole row, a huge document causes update contention. Keep it to atomic data indivisible by business rules.
- PostgreSQL 18 adds: cast jsonb null to a scalar type as NULL, an option for json_strip_nulls to also remove nulls in arrays, and SIMD acceleration for long JSON.

---

"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](/blog/category/database).

> **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).

```sql
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.

| Operator | Return | Example |
| --- | --- | --- |
| `->` | json/jsonb | `data -> 'user'` (object), `data -> 0` (array element) |
| `->>` | **text** | `data ->> 'name'` (extract/compare as a string) |
| `#>` | json/jsonb | `data #> '{user,address,city}'` (path) |
| `#>>` | **text** | `data #>> '{user,address,city}'` (text by path) |
| `data['key']` | jsonb | subscript access (**PG14+**, jsonb only. assignment too) |

```sql
-- 比較・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."

```sql
-- オブジェクトの部分一致：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:

```sql
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.**"

```sql
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).

```sql
-- 値の差し替え／追加（既定で 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).

```sql
-- 行を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).

```sql
-- 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.

```sql
-- 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.

| GIN operator class | Supported operators | Characteristic |
| --- | --- | --- |
| `jsonb_ops` (**default**) | `?` `?|` `?&` `@>` `@?` `@@` | the existence operators can also be used. All-rounder |
| `jsonb_path_ops` | `@>` `@?` `@@` | no existence operators, but **the index is small and search is fast** |

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.**"

```sql
-- 万能（存在 ? も使いたい）：既定の 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.

```sql
-- 単一キーの等値/範囲検索：式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:

| Do this | Don't do this |
| --- | --- |
| Put attributes used in query conditions, join keys, and constraints in **normalized columns** | Embed the searched value in JSONB and dig it each time |
| Put variable, sparse, schema-unsettled incidental info in JSONB | Stuff everything into one huge document and frequently partial-update it |
| Put an "atomic chunk" like an external-API response in JSONB | Cohabit multiple values you want to update separately in one document |
| Promote values protectable by `NOT NULL`, type, `CHECK` to columns | Leave consistency-requiring values to JSONB |

```sql
-- 良い例：検索/結合/制約に使う列は正規化、可変の付帯情報だけ 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."

```ts
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](/blog/postgresql-declarative-partitioning-range-list-hash-guide) that physically splits ultra-large tables.

---

### References (PostgreSQL 18 official documentation)

- [8.14. JSON Types (json vs jsonb, containment/existence, indexing)](https://www.postgresql.org/docs/18/datatype-json.html)
- [9.16. JSON Functions and Operators](https://www.postgresql.org/docs/18/functions-json.html)
- [8.14.4. jsonb Indexing](https://www.postgresql.org/docs/18/datatype-json.html#JSON-INDEXING)
- [E.4. Release 18 (JSON-related changes)](https://www.postgresql.org/docs/18/release-18.html)