JSON Columns in Relational Databases
JSON column types store semi-structured documents inside the relational model with queryable paths, trading schema flexibility for weaker constraints and different indexing ergonomics.
Definition
JSON types (JSONB in PostgreSQL, JSON in MySQL) store documents in a binary parsed form, enabling path extraction operators, containment queries, and functional indexes over expressions. They trade schema rigidity for flexible shape evolution. PostgreSQL distinguishes the text-preserving json from the decomposed, indexable jsonb.
Context and scope
Used for external payload archives, varying vendor metadata, feature flags per tenant, and event envelopes — anywhere the shape varies per producer or evolves without migrations.
Common misconceptions
- JSON columns are schemaless like a document store — the table stays relational; only the column is flexible, and constraints move to CHECK and validation code
- Storing JSON is faster than columns — per-row parse and larger pages often make wide, frequently read fields slower outside the document
- GIN indexes make any JSON query fast — GIN accelerates containment operators on indexed keys, not arbitrary computed paths
Anti-patterns
- Pushing frequently filtered and joined business keys into JSON and losing index and constraint support
- Storing money and timestamps as JSON strings and fighting coercion and collation surprises
- Building an entire entity model as one JSON blob and re-discovering every relational pain with weaker tools
Related concepts
Related examples
Authoritative source
What it is
Documents with evolving or producer-specific shapes do not fit fixed columns. JSON types store the parsed document in the row: queryable by path, comparable by containment, and indexable via expression or GIN indexes. jsonb normalizes the document; the original text form of json does not survive round trips.
How it works
Operators extract and test: -> and ->> fetch values, @> tests containment, jsonb_path_query runs SQL/JSON path language. GIN indexes accelerate containment on documents; expression indexes accelerate one extracted path at high speed. Statistics on document contents are weaker than column statistics, so planner estimates on JSON predicates are often wrong.
When to use it
Archiving raw external payloads, per-tenant extension attributes, event envelopes with stable core columns plus variable detail, and rapid prototyping before shapes settle. Extract hot fields into real columns once access patterns stabilize.
Pitfalls
Heavy per-row parsing on wide documents costs CPU; select paths rather than the whole blob. Validator CHECK constraints or application-side JSON Schema checks are the only schema enforcement. Mixing units and types inside documents makes migrations later far harder — keep identifiers, money, and time in typed columns.