The Column That Was Going to Be Temporary
A feature needs to store some flexible per-customer settings. The shape is not settled, the deadline is Thursday, and adding a JSON column takes one migration instead of four tables. Everyone knows it is a shortcut. Everyone agrees it will be normalised later.
Two years on, that column holds forty distinct keys, six of which are queried in production, three of which are written by two different services that disagree about their format, and one of which is a nested array that a reporting job unpacks with a regular expression. Nobody knows which keys are still used, because there is no schema to consult.
This outcome is not caused by laziness. It is caused by the fact that a JSON column has almost no upfront cost and a cost curve that rises invisibly. By the time the cost is obvious, the migration is large. The way to avoid it is not to ban JSON columns, which are genuinely the right answer sometimes, but to know in advance which situation you are in.
The Three Options, Honestly Compared
There are really three choices, and the middle one is worth naming explicitly because people often reach for it by accident.
Option 1: Text Column Holding Serialised JSON
The database treats it as an opaque string. It cannot validate it, cannot index inside it, and cannot query it without parsing on every row. This is the right choice only when the data is genuinely never queried by the database, such as an audit payload retained for compliance and read only by an application.
Choosing this by accident, because a driver serialised an object into a text column, is a common source of pain: you get none of the query capability and all of the schemalessness.
Option 2: Native JSON Type
PostgreSQL's jsonb, MySQL's JSON, or SQL Server's JSON functions over an nvarchar column. The database validates structure on write, exposes path-based extraction, and can index expressions over the document.
This is a real capability, not a compromise, but it comes with a specific tax: query plans over JSON expressions are harder for the optimiser to estimate, statistics on extracted values are poorer than on real columns, and every consumer must know the document shape without being able to ask the database what it is.
Option 3: Normalised Columns and Tables
Scalar attributes become columns; repeating groups become child tables. The database enforces types and referential integrity, the optimiser has full statistics, and the schema is self-documenting.
The cost is migration friction. Every new attribute is a schema change, which in a large table and a busy deployment pipeline is real work.
A Decision Framework
Rather than a general preference, ask these questions in order. The first one that gives a clear answer decides it.
| Question | If Yes | Reasoning |
|---|---|---|
| Do you filter, join, or aggregate on this attribute? | Normalise it | Query patterns are the strongest signal. A queried attribute wants statistics and a real index. |
| Must a constraint or foreign key apply? | Normalise it | JSON containers cannot carry referential integrity. Application-level enforcement drifts. |
| Is the key set defined by users rather than developers? | Use JSON | Genuinely open-ended keys cannot be columns. This is the strongest case for JSON. |
| Is it an opaque payload from an external system? | Use JSON | You do not control the shape and should not pretend to. |
| Is it a repeating group with its own attributes? | Child table | An array of objects is a table. Storing it as JSON defers a join you will end up writing anyway. |
| Do two or more services write it? | Normalise it | Shared writers without a schema will diverge. The database is the only neutral arbiter. |
| Is it write-once and read whole? | Use JSON | Event payloads and snapshots fit this well and benefit from staying intact. |
Notice the pattern: JSON is appropriate when the database does not need to understand the contents. As soon as it does, you are asking a document store to behave like a relational one, and the relational option is right there.
The Hybrid Pattern That Actually Works
The productive middle ground is not "some JSON, some columns, decided ad hoc". It is a deliberate split with a stated rule.
Promote to real columns any attribute that is queried, constrained, or shared between services. Keep in JSON everything that is genuinely open-ended or opaque. Write the rule down next to the table definition so the next person knows which side of the line a new attribute belongs on.
Two practices make this sustainable:
- Generated columns. Most engines can expose a JSON path as a computed column and index it. This gives you index support and cleaner queries without a data migration, and it is an excellent intermediate step while you decide whether to promote properly.
- A validated schema at the write boundary. A JSON column with no schema is unbounded. Validating the document shape in the application before insert restores most of the guarantees you gave up, and it means the set of keys in production matches the set of keys anyone intended.
Migrating Out: A Concrete Path
Once you have decided an attribute should be a column, the migration is mechanical. The reason it feels daunting is usually that nobody knows what is actually in the column, so the first step is discovery.
Step 1: Inventory the Keys
Extract a representative sample of documents and enumerate every key path that appears, with a count. You will almost certainly find keys nobody remembers, keys that appear in under one percent of rows, and keys that are misspelled variants of each other.
Flattening the sample is the fastest way to do this: converting nested JSON to a tabular form with dotted paths turns the question "what keys exist" into "what columns does this table have". The JSON Manipulation Tools in Develop Box handle this locally, so you can profile real production documents without copying them to a third-party service.
Step 2: Profile Each Key
For every key you intend to promote, establish presence rate, distinct value count, and observed types. A key that is a string in 90 percent of rows and a number in the rest tells you the eventual column type and warns you that a plain cast will fail.
Step 3: Add the Column, Backfill, Dual-Write
Add the new column as nullable. Backfill in batches from the JSON. Have the application write both the column and the JSON key for a period. This keeps every existing reader working while new readers move over.
Step 4: Cut Readers Over, Then Stop Writing JSON
Move readers to the column, verify, then stop writing the key. Only after that, and only once you are confident, remove it from existing documents. Doing the removal early is how you lose data you turn out to need.
Step 5: Apply the Constraints You Wanted
Now add the not-null constraint, the check, or the foreign key. This is the payoff. Add it last, because adding it before the backfill is complete will fail.
Frequently Asked Questions
Is a JSON column slower than a normal column?
For reading a whole document, barely. For filtering and aggregating, usually yes, and the reason is statistics rather than extraction cost: the optimiser has much weaker estimates for JSON path expressions than for real columns, so it picks worse plans. Indexed expressions help considerably, but they do not fully close the gap.
Should I store an array of objects as JSON or a child table?
A child table, in nearly every case. An array of objects with consistent keys is a table that has not been given a name. Storing it as JSON means every query that needs an individual element has to unnest it, which is a join written in a more awkward syntax with worse performance characteristics.
How do I know which keys in an existing column are still used?
Combine two sources. Profile the data to find which keys exist and how often, then grep every codebase and query log for those key names to find which are actually read. Keys present in data but absent from all code are candidates for removal, though verify against analytics and ad hoc reporting before deleting anything.
Conclusion: Let Query Patterns Decide
The JSON versus normalised debate is usually framed as flexibility against rigour, which makes it feel like a matter of taste. It is more concrete than that: the question is whether the database needs to understand the contents.
If it does, give it real columns and real constraints. If it genuinely does not, JSON is a good fit and you should not feel bad about it. What causes trouble is the third case, where an attribute quietly crosses from the second category into the first and nothing prompts a revisit. Write the promotion rule down, and check it whenever a new key is added.