Storing Everything as JSON Blobs
Defaulting to opaque JSON text columns abandons constraints, indexing, and queryability, turning the database into a dumb blob store with drifting keys. Model structured data as columns, use typed JSONB for genuine flexibility, and promote hot fields to real columns.
This anti-pattern stores structured, queryable data as opaque JSON (or other serialized) text inside a single column, used as the default rather than for genuinely document-shaped or schema-flexible data. The database sees one text blob; the structure inside is invisible to it.
Semi-structured columns are a powerful feature when used deliberately. The anti-pattern is reaching for them to avoid modeling, so the database becomes a dumb key-value store wrapped in SQL.
Why It Happens
It is fast to ship: serialize an object, write one column, never run a migration. Requirements feel fluid, so committing to columns feels premature. Developers comfortable in application code prefer to keep the shape there. Sometimes data arrives as JSON from an API and gets stored verbatim out of convenience.
Why It Hurts
The database cannot enforce types, required fields, or foreign keys on values buried in a blob, so integrity moves into scattered application code or vanishes. Filtering, joining, or aggregating on a value inside the blob means parsing it in the application or scanning every row, which is slow and unindexable when stored as plain text. Keys drift across rows — userId here, user_id there, missing elsewhere — producing silent schema sprawl. Reporting and analytics tools cannot interpret the data. Migrations of the internal shape are unmanaged and error-prone.
Warning Signs
- Frequently filtered or joined data lives inside a JSON text column.
- Code parses blobs in the application to filter rows the database should filter.
- The same logical field appears under different keys or is sometimes absent.
- Analysts cannot query the data without custom extraction scripts.
Better Alternatives
Model data that is structured and queried as proper columns and tables — relational modeling gives you constraints, indexes, and joins. Where flexibility is genuinely needed, use a typed semi-structured column (such as JSONB) that supports indexing on internal paths and partial validation, rather than an opaque text blob. A hybrid design works well: promote the hot, queried fields to real columns and keep a JSONB column for the truly variable remainder.
How to Refactor Out of It
Profile which fields inside the blobs are actually queried or constrained. Add real columns for those, backfill by extracting values from existing blobs, and add constraints and indexes. Convert plain-text JSON columns to a typed semi-structured type so the remaining flexible data is still indexable. Move filtering from application parsing into SQL. Standardize the keys that remain. The aim is a schema the database can validate and query, with flexibility confined to where it is truly needed.