📦JSON data type

Semi-structured inside a column

Native JSON / JSONB (PostgreSQL) columns hold evolving payloads, instrument configs, assay metadata, feature flags, without pre-declaring every attribute as a nullable column. They are still one attribute domain: validate at the edge and keep money, keys, and audit fields relational when possible.

SQL

Insert JSON (PostgreSQL)

INSERT INTO instrument_profiles (instrument_id, settings)
VALUES ('SEQ-01', '{"lanes":8,"mode":"rapid"}'::jsonb);

✓Illustrative result set

rows
1

⚡ Advanced / dialect note

PostgreSQL JSONB is binary, index-friendly, and normalizes duplicate keys on input; JSON preserves textual formatting and insertion order but is slower for containment-heavy queries.

JSON as the only schema

Money, timestamps, or IDs only inside JSON strings without timezone and precision rules.
Unbounded document growth on hot OLTP rows, TOAST and bloat follow.
No story for evolving contracts, silent breakage across services.

Contracts + relational spine

Prefer jsonb when you need @> and expression indexes on Postgres.
Keep transactional keys and amounts in typed columns; version JSON payloads during migrations.
Enforce max size and shape at the API; add CHECK or JSON schema validation where the engine supports it.

API boundary

Expose stable typed fields to clients even when storage is JSON, reduces surprises and keeps OpenAPI honest.

When to promote

When a path becomes a filter or join key on every request, consider a generated column or a real column with a migration path.

Storage

Large payloads may live out-of-line (TOAST) with different compression, benchmark row width and backup size, not only query microbenchmarks.

TL;DR

Native JSON/JSONB columns store semi-structured documents inside the relational model. PostgreSQL JSONB is binary, de-duplicates keys on input, and supports GiN indexes; textual JSON preserves formatting but is slower for containment queries. Treat JSON as an attribute domain: validate shape at the boundary and promote fields you filter on into columns or indexed expressions.