JSONB & Semi-Structured Data

json vs. jsonb

	`json`	`jsonb`
Storage	exact text, as submitted (preserves whitespace, key order, duplicate keys)	parsed, binary form — normalized, no duplicate keys, no whitespace/order preserved
Write speed	faster (no parsing on insert)	slightly slower (parses on insert)
Read/query speed	slower (re-parses on every access)	faster
Indexable	no	yes — GIN indexes

Use jsonb by default. json exists mainly for the rare case where you need to preserve the exact original text byte-for-byte (e.g. a legal requirement to store precisely what was submitted).

core operators

SELECT data->'address' FROM users;          -- -> returns jsonb (still a JSON value)
SELECT data->>'name' FROM users;             -- ->> returns text (unwrapped)
SELECT data#>'{address,city}' FROM users;    -- #> path into nested structure, returns jsonb
SELECT data#>>'{address,city}' FROM users;   -- #>> same, returns text

SELECT * FROM users WHERE data @> '{"active": true}';   -- @> "contains" — the classic filter operator
SELECT * FROM users WHERE data ? 'phone';                -- ? key exists at the top level
SELECT * FROM users WHERE data->'tags' ? 'vip';           -- key/element exists within a nested value

indexing jsonb: GIN

CREATE INDEX idx_users_data ON users USING GIN (data);

-- now this uses the index:
SELECT * FROM users WHERE data @> '{"active": true}';

A default GIN index on a jsonb column supports @>, ?, ?|, and ?& efficiently. If you only ever query one specific key, an expression index on just that path (CREATE INDEX ON users ((data->>'email'))) is smaller and often faster than indexing the whole document.

when JSONB is the right call — and when it isn't

Good fit	Poor fit
Genuinely variable, sparse, or client-defined attributes (e.g. per-integration webhook payloads)	data with a fixed, known shape that's the same for every row — that's just columns
Storing a third-party API response as-is for audit/replay	anything you need to `JOIN` against relationally or enforce foreign-key integrity on
Rapid prototyping before the schema has settled	values you frequently aggregate/sum/index across many rows — a real column is faster and simpler

The trap is using JSONB as a substitute for schema design because adding a real column feels like more work — it defers the cost rather than removing it, and gives up constraint enforcement, straightforward indexing, and query planner statistics along the way.

where to go from here

Data Types & Schema Design — the case for a normalized column instead.
PostgreSQL Extensions — pgvector, which stores a different kind of semi-structured data: embeddings.