The JSON Column Temptation: When Semi-Structured Data Helps and When It Hides Debt

A JSON or jsonb column earns its place when data is genuinely variable across rows, sparse, and rarely filtered, sorted, or joined on. The moment you're filtering on a key buried in that blob, or validating a "required" field only in application code, you've used JSON to dodge a modeling decision — not to solve one.

Quick answer: Use a JSON column for data that's genuinely variable, sparse, and rarely queried on its own — like optional product specs. Use real columns for anything you'll filter, sort, aggregate, or validate. When in doubt, model it as a structured entity first, then push only the true leftovers into JSON.

Every backend engineer has shipped a jsonb column that felt like the pragmatic call on a Tuesday afternoon. Most of the time it is. Sometimes it's the first entry in a debt ledger nobody opens again until a data analyst asks why SELECT COUNT(*) WHERE plan_type = 'enterprise' won't run, or a support engineer discovers half the rows have plan_type and half have planType. This piece is about telling the two cases apart before you commit.

The Rule: What Makes JSON Defensible

JSON columns earn their place under three conditions: the data's shape genuinely varies row to row, most rows don't have most fields (it's sparse), and you rarely need to filter, sort, aggregate, or enforce constraints on the values inside. Fail any of the three, and you're better off with real columns.

Each condition is doing real work, and it's worth separating them because teams usually only check one before reaching for jsonb:

• Genuinely variable shape. Not "we haven't finished designing it yet" — actually variable, like a product catalog spanning categories with unrelated attribute sets, or a webhook payload whose shape is dictated by a third-party API you don't control.
• Sparse. If 80% of rows would have NULL in a column, that's a signal the attribute doesn't belong to every row of that entity, which is itself a modeling clue (maybe it belongs to a subtype, not a bag of extra fields).
• Rarely queried directly. You render it, you display it, you round-trip it — but you don't WHERE, GROUP BY, ORDER BY, or join on it in a query that needs to be fast or correct at scale.

If your data passes all three tests, JSON is a legitimate, boring modeling choice — not a shortcut. If it fails even one, you're trading a schema you understand today for a debugging session six months from now. The broader question of what deserves to be its own column, table, or entity in the first place is covered in more depth in a complete guide to data modeling; this article is about one specific, recurring fork in that decision tree.

The Product-Attributes Example: Where JSON Is Genuinely Defensible

A product catalog with wildly different attributes per category is the textbook case for a JSON attributes column, because the alternative — a table with one column per possible attribute across every category ever sold — collapses under its own width. A laptop has RAM and battery capacity; a t-shirt has size and fabric; a dining table has dimensions and material. None of that belongs in shared columns.

Picture an e-commerce catalog with three categories: electronics, apparel, and furniture. Each has attributes the others don't share, and the list of categories will keep growing as the catalog does.

Three modeling approaches handle this, and they trade off differently:

For this scenario, the jsonb column is the right call, and it's the one most teams pick correctly. The attribute set genuinely varies by category, most products are sparse in most attributes outside their own category, and shoppers filter within a category ("screen size" facet on the electronics page) rather than across the whole catalog. That last part matters: you're not writing WHERE attributes->>'battery_capacity' > 5000 against a table with apparel and furniture rows mixed in — the query is already scoped by category.

The honest caveat: even here, a pure JSON blob usually isn't the whole answer. price, sku, category_id, stock_quantity, and weight_kg (needed for shipping calculations across every category) stay as real, typed, constrained columns because you filter, sum, and validate them constantly. The jsonb column holds only the category-specific long tail.

That hybrid — a handful of first-class columns plus one flexible column for genuine variability — is usually what "using JSON well" looks like in practice, not an all-or-nothing choice. It's also worth asking, before reaching for either JSON or EAV, whether "product spec" deserves to be modeled as its own entity with its own lifecycle; the criteria for what counts as an entity apply just as much to a spec sheet as they do to a customer or an order.

The Silent Costs: What a JSON Column Quietly Gives Up

Reaching for JSON trades three things you don't notice on day one for speed you do notice immediately: type safety, database-enforced constraints, and cheap analytics. None of these show up as an error message when you create the column — they show up months later as bugs, bad data, or a slow, brittle reporting query.

No Type Safety

A jsonb value can be a string, a number, a boolean, an object, or an array, and Postgres will happily store any of them under the same key across different rows. Nothing stops one service from writing "price": "19.99" and another from writing "price": 19.99. Your application code becomes the type system, which means every consumer of that field has to defend against a shape it can't trust — and eventually one of them won't.

No Constraints

A real column can carry a NOT NULL, a CHECK, a foreign key, or a UNIQUE constraint that the database enforces on every write, from every caller, forever. A key inside a JSON blob can carry none of that natively. You can bolt on a CHECK with a JSON path expression, but it's clunkier to write, easier to forget, and invisible to anyone skimming the schema.

That's the same failure mode covered in soft delete vs. hard delete modeling: a convenient shortcut in the schema quietly turns into a data-integrity assumption nobody actually enforces.

Painful Analytics Later

This is the cost technical PMs feel most directly, usually from a data analyst's Slack message. Every report that needs a value buried in JSON requires unnesting it first — a ->> operator in Postgres, a JSON_EXTRACT in MySQL, a json_normalize in a notebook — before a BI tool can even see it as a column. Tools that introspect schemas to auto-suggest fields (most self-serve BI does this) simply can't see inside the blob at all; someone has to hand-write the extraction every time.

Martin Kleppmann's Designing Data-Intensive Applications frames this precisely as schema-on-write vs. schema-on-read: a normalized column pays a validation cost once, at write time, while a JSON blob defers that cost to every single reader, indefinitely. You don't eliminate the cost of structure — you just decide who pays it and how many times.

The indexing story reinforces the same point. PostgreSQL's own documentation on jsonb indexing recommends a GIN index to make key lookups fast, but a GIN index over a JSON column is typically substantially larger on disk than an equivalent B-tree index over a normalized column holding the same values. You're paying storage and write overhead to partially recover query performance you'd have gotten for free with a real column.

MongoDB's own schema-design guidance is more candid still: its documented Attribute Pattern exists specifically to handle sparse, variable fields inside a document store. Even MongoDB's engineers frame it as a pattern for a specific, bounded subset of fields — not a license to put everything in one flexible bag.

Uber Engineering's widely read 2016 postmortem on moving away from a Postgres schema that had leaned heavily on wide, JSON-ish flexible columns made a related point from the operations side: flexible, loosely typed schemas that felt fast to build against became genuinely difficult to reason about and safely migrate once the data volume and number of consuming services grew. The lesson generalizes past their specific database choice — flexibility that isn't bounded tends to become a liability roughly in proportion to how many teams end up reading that data.

jsonb vs. Normalized Columns: A Decision Framework

The fastest way to decide is to ask five questions about the field before you write the migration, not after a bug report. If the answer to any of the first three is "yes," it belongs in a real column; if all five point the other way, JSON is a defensible choice.

1. Will you ever filter, sort, or group by this field in a query that needs to be fast? If yes, it needs to be a real, indexable column.
2. Does this field need a foreign key, a uniqueness guarantee, or a NOT NULL the database itself enforces? If yes, real column.
3. Will a BI tool, data warehouse, or non-engineer need to see this field without a developer writing an extraction query first? If yes, real column.
4. Is the field's shape stable across roughly 95% of the rows that would have it? If yes, it's a candidate for a real (possibly nullable) column even if it started life as "just some extra metadata."
5. Is the field genuinely different in shape or presence from row to row, in a way that would require dozens of nullable columns to represent? If yes, and only if the first three questions were all "no," JSON is reasonable.

This is, in miniature, the same discipline as drawing the model before writing any code: sketching the entity-relationship diagram first forces you to name every field and ask what it means before you decide where it lives, instead of discovering the shape of your data by staring at whatever jsonb blob accumulated in production.

It's also worth remembering that the fields worth turning into real columns are usually tied to what your users are actually trying to accomplish. The same lens used in jobs-to-be-done research — separating what a customer is truly hiring your product for from incidental detail — applies just as well to deciding which attributes deserve first-class treatment in your schema.

If you're modeling anything resembling a sequence of stages or events — the kind of data that shows up in customer journey mapping — assume you'll eventually need to filter and aggregate by stage, timestamp, and channel. That argues strongly for real columns over a JSON blob of "journey metadata" from day one.

Modeling the Structured Alternative Before You Commit

Before defaulting to a catch-all JSON blob, it's worth spending fifteen minutes modeling the entity the "hard" way and seeing what the resulting schema actually makes possible. In Prodinja's Data Modelling tool, you can define a structured entity — with typed fields, constraints, and relationships — as the deliberate alternative to a JSON blob, and see the generated SQL DDL for both approaches side by side.

Watching a CHECK constraint, a foreign key, or an index show up in one version of the DDL and simply not exist in the other is a much more concrete way to weigh the tradeoff than debating it in the abstract. It doesn't run your production database or make the call for you — it's a modeling workspace for seeing the consequence of the choice before you've written a migration you'll regret.

Key Takeaways

• Use a JSON or jsonb column only when data is genuinely variable in shape, sparse across rows, and rarely filtered, sorted, or joined on directly.
• Use real, typed, constrained columns for anything you'll query, validate, aggregate, or connect to another table with a foreign key.
• The product-attributes example (varying specs across categories) is a legitimate use of JSON — but even there, keep universally-queried fields like price and SKU as real columns.
• The costs of a JSON column are silent at write time: no type safety, no native constraints, and analytics that require manual extraction before any report can use the field.
• A hybrid schema — a few first-class columns plus one flexible column for the true long tail — beats both a fully wide table and a fully JSON one in most real catalogs.
• Ask whether you'll filter, sort, group, constrain, or report on a field before you decide where it lives; the answer, not habit, should choose the column type.
• Model the structured alternative before committing to JSON so you can see, in the DDL itself, exactly what you're giving up.

Frequently Asked Questions

Should I use JSON or a normalized column in Postgres?

Use a normalized column whenever you'll filter, sort, aggregate, or enforce a constraint on the field; use a jsonb column only when the data's shape is genuinely variable, sparse, and rarely queried directly. Postgres supports both in the same table, so the honest answer is usually "both," applied field by field rather than as a single decision for the whole table.

What is the JSON column anti-pattern in database design?

The anti-pattern is using a JSON column as a catch-all for fields that are actually stable, frequently queried, or in need of validation — essentially skipping schema design rather than making a deliberate tradeoff. It shows up as slow reporting queries, silent type inconsistencies across rows, and missing constraints that application code was supposed to enforce but doesn't consistently.

Can you index a JSON column, and is it as fast as a normal index?

Yes — Postgres supports GIN indexes on jsonb columns for key and containment lookups, and expression indexes on specific extracted keys. It's typically slower to write and larger on disk than an equivalent B-tree index on a real column holding the same value, so indexing a JSON column recovers some query performance but rarely all of it.

Is jsonb better than json in Postgres?

For almost every real use case, jsonb is the better choice because it stores data in a decomposed binary form that supports indexing and efficient key lookups, while json stores an exact text copy and re-parses it on every read. The plain json type is mainly useful when you need to preserve exact formatting, key order, or duplicate keys, which is rare in application data.

When should I use EAV instead of a JSON column?

Choose Entity-Attribute-Value modeling over a JSON column when the number of distinct attribute types is itself large and needs to be managed as data — for example, letting admins define new custom fields at runtime without a deployment. If the attribute set is fixed by your own category logic and only the values vary, a JSON column is usually simpler to write to and read from than EAV's pivot-heavy queries.