JSON column is not a schema

ALTER TABLE users ADD profile JSON;

MySQL 5.7 went GA this week, and this line is now legal. Native JSON type: validation on insert, binary storage, functions to read paths. The question is not whether it works. It is what belongs in it.

Classic case: user profile with flexible metadata. Marketing wants a new field every other week. The old options were bad in familiar ways. Forty nullable columns and an ALTER for every idea. Or EAV, key-value rows, where every read is a self-join festival and nothing has a type.

JSON is the third option, and for this data it is honest.

The trap is stopping there and treating it as MongoDB inside MySQL. The optimizer cannot index a path inside the document. The moment you write WHERE JSON_EXTRACT(profile, '$.country') = '"DE"', you get a full table scan with a modern haircut.

5.7 gives the way out, generated columns:

ALTER TABLE users
  ADD country CHAR(2)
    AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.country'))) STORED,
  ADD INDEX idx_users_country (country);

The value lives in JSON, the searchable projection lives in a real indexed column.

Which gives the rule. If you filter by it, join by it, sort by it, or business logic depends on its type, it is a column. Real schema, real constraint, real index. JSON gets the long tail: display-only attributes, per-user settings, payloads you store today and will understand later. Data whose shape you honestly do not control.

I know the trap from the inside. Two years ago, on 5.5, I put the same kind of profile into a TEXT column with serialize() and told myself it was temporary. It is still there. Now at least the database can read it.

Schema is the database knowing what your data means. Indexes and constraints are made from that knowledge. JSON is a fine place for data that means nothing to SQL. Keep it there.