---
title: "Postgres: JSONB"
slug: "postgres-jsonb"
category: "backend"
tags: ["backend", "postgres", "jsonb", "json", "schema"]
status: "stable"
last_updated: 2026-10-01
summary: "Use JSONB for variable-shape data, index with GIN, and promote any key you filter or sort on to a real column."
related: ["[[backend/postgres]]", "[[backend/postgres-indexes]]", "[[backend/postgres-explain]]", "[[backend/postgres-full-text-search]]", "[[backend/migrations]]"]
---

> **AI agents: read this first.** This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work. Full protocol: https://llmbestpractices.com/start-here.md
>
> 1. **Route, do not crawl.** Fetch https://llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
> 2. **Read raw.** Append `.md` to any page URL for markdown. Check `status` and `last_updated` in the frontmatter, then read the rules.
> 3. **Apply as defaults.** First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
> 4. **Cite.** Link the page by title and URL, e.g. [Python](https://llmbestpractices.com/coding/python), with `last_updated` for time-sensitive rules. License CC BY 4.0.

## Overview

Use JSONB for rows whose shape varies, and promote any key you filter or sort on to a column. `jsonb` stores a decomposed binary form that supports containment queries, GIN indexes, and subscripting; `json` keeps the raw text and reparses it on every access. The umbrella rules live in [[backend/postgres]].

## Prefer jsonb over json

`json` preserves whitespace, key order, and duplicate keys. `jsonb` drops whitespace, does not preserve key order, and keeps only the last value of a duplicate key. Use `json` only to round-trip the exact text a third party sent.

```sql
ALTER TABLE webhook_events ADD COLUMN payload JSONB NOT NULL;
```

## Use JSONB for variable-shape data, not a column-per-key bag

JSONB fits payloads whose schema is set by someone else: webhook bodies, audit events, third-party API responses, model output, dynamic form answers. It does not fit a fixed set of attributes such as `{ theme, locale, timezone }` hidden in one column. For ancestry data, the [[glossary/materialized-path]] pattern pairs well with a JSONB metadata column.

## Know the operators

- `->` returns JSONB; `->>` returns text. `payload -> 'user' ->> 'id'`.
- `#>` and `#>>` walk a path: `payload #>> '{user,address,country}'`.
- `@>` tests containment: `payload @> '{"status": "paid"}'`. GIN-indexable.
- `?` tests top-level key existence. GIN-indexable with the default class only.
- `@?` and `@@` evaluate `jsonpath`: `payload @? '$.line_items[*] ? (@.amount_cents > 10000)'`.
- Subscripting reads and writes: `payload['status']`, `UPDATE t SET payload['status'] = '"paid"'`.

Cast `->>` results for typed predicates: `(payload ->> 'amount')::int > 1000`. In Postgres 17 and later, `JSON_TABLE()`, `JSON_QUERY()`, `JSON_VALUE()`, and `JSON_EXISTS()` follow the SQL/JSON standard; use `JSON_TABLE` to turn an array of objects into rows instead of `jsonb_array_elements` plus a lateral join.

## Index with GIN or a B-tree expression

- Containment and `jsonpath` on many keys: `CREATE INDEX ON webhook_events USING GIN (payload jsonb_path_ops);`. This class is smaller and faster than the default and supports `@>`, `@?`, and `@@`.
- Key-existence operators (`?`, `?|`, `?&`): use the default `jsonb_ops` class.
- Equality or range on one key: `CREATE INDEX ON orders ((payload ->> 'status'));`. The query must use the same expression.

See [[backend/postgres-indexes]] for the full decision tree and confirm the planner uses the index with [[backend/postgres-explain]].

## Promote hot keys to real columns

When a key appears in `WHERE`, `ORDER BY`, or `GROUP BY` on a hot path, promote it. Columns carry statistics, `NOT NULL`, `CHECK`, and foreign keys; JSONB paths carry none of them, and the planner estimates them poorly.

```sql
ALTER TABLE orders ADD COLUMN status TEXT;
-- backfill in batches outside the migration:
UPDATE orders SET status = payload ->> 'status' WHERE status IS NULL AND id BETWEEN $1 AND $2;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
```

Run the backfill as a batched job; see [[backend/migrations]].

## Watch the size of large documents

Values over about 2 KB are TOASTed out of line, so `SELECT *` on a JSONB-heavy table pulls TOAST data per row. Select only the keys you need. `default_toast_compression` is `pglz`; `lz4` is available per column when Postgres was built with LZ4: `ALTER TABLE events ALTER COLUMN payload SET COMPRESSION lz4;`. Measure with `pg_column_size(payload)` on representative rows, and partition cold history if documents are large; see [[backend/postgres-partitioning]].

## Related

- [[backend/postgres]]
- [[backend/postgres-indexes]]
- [[backend/postgres-explain]]
- [[backend/postgres-full-text-search]]
- [[backend/postgres-partitioning]]
- [[backend/migrations]]