JSON & JSONB in PostgreSQL
PostgreSQL supports JSON (stored as text, exact whitespace preserved) and JSONB (stored in binary format, deduplicates keys, supports indexing). JSONB is almost always preferred for queryable, indexable JSON.
JSONB is a filing cabinet with smart labels — you can find documents by what they contain (@>) if you set up the label system (GIN index). JSON is a plain text file — you read every word to find what you want.
Key operators: -> returns JSON element (as JSON), ->> returns text, #> for path, #>> for path as text. Array access: data->0, data->'key'. JSONB-specific: @> (contains), <@ (contained by), ? (key exists), ?| (any key exists), ?& (all keys exist). Indexing JSONB: GIN index on the entire column for @>, ?, operators. Functional index on specific path for equality: CREATE INDEX ON users ((data->>'email')). jsonb_each, jsonb_array_elements expand JSONB to rows.
JSONB uses a binary decomposed format that supports efficient operations: GIN indexes enable containment queries (@>) across millions of documents. jsonb_path_query (PostgreSQL 12+) implements SQL/JSON Path Language for complex traversals: SELECT * FROM data WHERE jsonb_path_exists(doc, '$.items[*].price ? (@ > 100)'). JSONB can partially simulate a document store pattern — store semi-structured data in a JSONB column on a relational table for flexibility without full schema commitment. The JSONB GIN index can be created with gin_trgm_ops for trigram search on text fields inside JSON. For read-heavy workloads, GENERATED COLUMNS can extract frequently queried JSONB fields to regular columns with a regular B-tree index.
I use JSONB for JSON storage — it's binary-optimized and supports GIN indexes for containment queries. Common pattern: store flexible metadata in a JSONB column on a relational table with a GIN index for @> queries. For high-cardinality JSONB fields queried frequently, I add a functional index on the extracted path: CREATE INDEX ON users ((data->>'email')). Generated columns can promote JSONB fields to first-class columns with B-tree indexes.
Storing deeply nested arrays in JSONB and querying them with @> on every request without a GIN index. @> without a GIN index is a full-table scan of JSONB data — very slow. Always create a GIN index when using @>, ?, or containment operators.