When JSON Belongs in a Relational Database

Learning outcomes

Modern relational databases often support JSON as a native data type. That does not mean every flexible-looking object belongs in JSON. The key question is semantic: which facts need relational identity, constraints, joins, and independent lifecycle, and which facts are legitimately semi-structured?

JSON is a storage format, not a modeling exemption

A JSON document can be convenient, but it does not remove the need to define:

• ownership;
• row grain;
• required fields;
• uniqueness;
• references;
• history;
• query patterns.

Good JSON candidate: external diagnostic payload

DiagnosticCapture(  capture_id,  work_order_id,  source_system,  captured_at,  payload_json)

If payload shape differs by diagnostic device and the raw response must be preserved, JSON can be a sensible boundary.

Bad JSON candidate: hidden foreign keys

{  "technician_ids": [17, 23, 44],  "part_ids": [91, 105]}

If these IDs participate in relationships, joins, integrity, or lifecycle, they should generally be represented by WorkOrderAssignment and PartUsage rows rather than hidden arrays.

Good JSON candidate: sparse vendor metadata

A Part may receive optional manufacturer-specific metadata:

{  "torque_rating_nm": 120,  "seal_material": "FKM",  "vendor_family": "X7"}

If only some Part families expose these fields and they do not justify independent entities, JSON may be practical.

When stable fields should become columns

If every query needs:

payload->>'serial_number'payload->>'manufacturer_code'payload->>'status'

those fields may no longer be genuinely semi-structured. Promote stable, business-critical attributes into typed columns or related tables.

Type safety

Relational columns provide clear domains:

quantity INTEGERunit_price NUMERIC(12,2)occurred_at TIMESTAMP

JSON can contain unexpected strings, nulls, arrays, or type changes unless validation exists.

Schema validation for JSON

Validation can live in:

• application schemas;
• database CHECK constraints;
• JSON Schema validation where supported;
• generated/computed columns;
• ingestion pipelines.

Version the payload shape

{  "schema_version": 3,  "temperature_c": 81.4,  "vibration": {...}}

Without a version, old and new payloads become difficult to interpret safely.

Indexing JSON

Frequently queried JSON paths may need expression, path, GIN/inverted, or engine-specific indexes. Index only paths tied to real access patterns.

Promoted columns

A hybrid row can use:

DiagnosticCapture(  capture_id,  work_order_id,  captured_at,  severity_code,  payload_json)

severity_code is promoted because it is common, constrained, and frequently filtered; the rest remains flexible.

Do not duplicate without ownership

If severity exists both as a column and inside JSON, define which is authoritative and how they remain consistent. Better yet, avoid duplicate representation unless it solves a measured need.

JSON and normalization

JSON can contain nested structures without violating the physical capabilities of the DBMS, but from a modeling perspective ask whether nested arrays represent independent repeating facts. If so, relational decomposition may still be preferable.

JSON and history

Immutable event snapshots are strong JSON candidates because preserving the exact original payload may be more important than normalizing every field.

WorkshopHub JSON map

Practice: JSON or relation?

Summary and next lesson

JSON is valuable for semi-structured, sparse, or externally shaped data, especially immutable snapshots. It becomes dangerous when used to hide relational identity, foreign keys, or frequently queried stable facts. The next lesson compares schema-on-write with flexible attribute strategies more broadly.

References

• Martin Kleppmann, Designing Data-Intensive Applications.
• PostgreSQL documentation on JSON/JSONB and indexing.
• DBMS documentation for JSON validation and generated columns.