MySQL 8.0 JSON Columns: When to Use Them and How to Keep Queries Fast

Your product team wants to store per-user feature flags, and the flag list changes every sprint. Adding a column each time means migrations; a key-value table means joins. MySQL 8.0's native JSON column type is the middle path: flexible documents inside a relational table, with real SQL support for querying them. The catch is that unindexed JSON queries scan and parse every row — so the practical skill is knowing how to index the fields you actually filter on.

What the JSON type actually gives you

A column declared as JSON is not a text blob. MySQL parses the value on insert, rejects invalid JSON, and stores it in a binary format that allows fast access to nested keys without re-parsing the whole document on every read. You also get a family of functions — JSON_EXTRACT, JSON_SET, JSON_TABLE, and the -> / ->> shorthand operators — for reading and modifying nested structures in plain SQL.

That validation-on-write is a feature, not just overhead: malformed data fails at insert time instead of surfacing as a parsing bug in application code months later.

A worked example: feature flags per account

Run these statements in any MySQL 8.0 client (mysql CLI, Workbench) against a test database. You need CREATE and INSERT privileges. Nothing here touches existing tables.

CREATE TABLE account_settings (
  account_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  settings JSON NOT NULL
);

INSERT INTO account_settings (settings) VALUES
  ('{"plan": "pro", "flags": {"beta_dashboard": true, "dark_mode": false}, "locale": "en-US"}'),
  ('{"plan": "free", "flags": {"beta_dashboard": false}, "locale": "de-DE"}');

Reading a nested value uses the ->> operator, which extracts and unquotes in one step:

SELECT account_id,
       settings->>'$.plan' AS plan,
       settings->>'$.flags.beta_dashboard' AS beta_dashboard
FROM account_settings
WHERE settings->>'$.plan' = 'pro';

Verify the result: you should get one row, with beta_dashboard shown as true. If the path doesn't exist in a document, the expression returns NULL rather than an error — worth remembering when you debug "missing" rows.

The performance problem and the generated-column fix

The WHERE settings->>'$.plan' = 'pro' query works, but MySQL cannot index the inside of a JSON document directly. Run EXPLAIN on it and you'll see a full table scan. Fine at 500 rows, painful at 5 million.

The supported solution is a generated column: a column whose value is computed from an expression, which can then be indexed like any scalar column.

ALTER TABLE account_settings
  ADD COLUMN plan VARCHAR(20)
    GENERATED ALWAYS AS (settings->>'$.plan') VIRTUAL,
  ADD INDEX idx_plan (plan);

Now rewrite the filter as WHERE plan = 'pro' and run EXPLAIN again — you should see idx_plan in the key column and a much lower rows estimate. That before/after EXPLAIN comparison is the practical check that the index is being used.

Choose VIRTUAL (computed on read, index stores the value) or STORED (materialized in the table). Virtual is the usual default; stored costs disk but can help read-heavy workloads without an index.

Trade-offs worth knowing before you commit

• Storage and backups. JSON documents, especially with long key names repeated in every row, take more space than flat columns, and larger tables mean slower backups and restores.
• Write cost. Every insert pays for JSON validation, and every indexed generated column pays for index maintenance. If you bulk-load large documents, measure insert latency with and without the generated columns before rolling out.
• No constraints inside the document. You can't put a foreign key or unique constraint on a JSON key. Anything that needs relational integrity belongs in a real column.
• Schema drift. JSON frees you from migrations, which means nothing stops three services from writing three different shapes. Document the expected structure somewhere, or use JSON_SCHEMA_VALID in a CHECK constraint (supported from MySQL 8.0.17) to enforce it.

Deciding what goes in JSON

A workable rule of thumb: if a field is filtered, joined, sorted, or aggregated in queries, make it a scalar column (or a generated column with an index). If it's a bag of attributes you mostly read whole and write whole — settings, metadata, third-party payloads — JSON is a good fit.

Your next step: take one table where you're currently serializing JSON into a TEXT column from application code, convert it to the native type (ALTER TABLE ... MODIFY col JSON after confirming all existing values parse), and add a generated column for the one field you filter on most. The EXPLAIN diff will tell you immediately whether it was worth it.