JSON vs Relational Columns

A JSON column is flexible, and flexibility has a price. This lesson helps you decide where JSON belongs in a relational design.

What a normal column gives you that JSON does not

What JSON gives you that columns do not

Compare the same data both ways

Product attributes that differ by product type:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Approach A: one wide table with many nullable columns
CREATE TABLE products_wide (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    size VARCHAR(5) NULL,
    color VARCHAR(20) NULL,
    capacity_ml INT NULL,
    ram_gb INT NULL,
    storage_gb INT NULL
);

-- Approach B: stable columns, flexible attributes in JSON
CREATE TABLE products_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    category VARCHAR(20) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    attributes JSON NOT NULL DEFAULT (JSON_OBJECT())
);

INSERT INTO products_json (name, category, price, attributes) VALUES
    ('T-shirt', 'clothing', 499,   '{"size": "M", "color": "blue"}'),
    ('Mug',     'kitchen',  199,   '{"capacity_ml": 350, "color": "red"}'),
    ('Laptop',  'computers', 54999, '{"ram_gb": 16, "storage_gb": 512}');

SELECT name, price, attributes->>'$.color' AS color FROM products_json ORDER BY price;

1
2
3
4
5
6
7
8

+---------+----------+-------+
| name    | price    | color |
+---------+----------+-------+
| Mug     |   199.00 | red   |
| T-shirt |   499.00 | blue  |
| Laptop  | 54999.00 | NULL  |
+---------+----------+-------+
3 rows in set

Approach A grows a new nullable column for every kind of product, and most cells are NULL. Approach B keeps what every product has (name, category, price) in real columns, and puts the varying details in JSON.

The hybrid rule

> Columns for what you query, join, sort, count and constrain. JSON for the rest.

Facts that every row has and that your queries use (price, category, status, created_at, foreign keys) belong in columns. Descriptive, optional, variable details (attributes, preferences, raw_payload) can go in JSON.

Good uses of JSON

1. User preferences and settings ({"theme": "dark", "emails": false}): loaded as a whole, rarely queried.
2. Product attributes that differ by type, filtered by a few indexed paths.
3. Raw payloads from webhooks and APIs, kept for audit and replay: payload JSON.
4. Event logs with a type-specific body.
5. Snapshots: the exact order data at checkout time, kept as it was.
6. Form builders and CMS blocks with user-defined fields.
7. API output: building responses with JSON_OBJECT and JSON_ARRAYAGG.

Poor uses of JSON

1. Core business data you filter on in most queries (customer, order total, status).
2. Anything you join on. A list of ids in a JSON array is not a foreign key. Use a junction table (Many-to-Many).
3. Data you aggregate in reports every day.
4. Data with strict relationships and integrity needs.
5. “We might need it someday” structure. Model what you know.
6. A huge document that is updated often by many users (every update rewrites a big value, and concurrent edits clash).

The migration path

It is easy to start with JSON and promote a value to a column when it becomes important:

1
2

ALTER TABLE products_json ADD COLUMN color VARCHAR(20) GENERATED ALWAYS AS (attributes->>'$.color') STORED, ADD INDEX idx_color (color);
SELECT name, color FROM products_json WHERE color IS NOT NULL ORDER BY name;

1
2
3
4
5
6
7

+---------+-------+
| name    | color |
+---------+-------+
| Mug     | red   |
| T-shirt | blue  |
+---------+-------+
2 rows in set

A STORED generated column keeps the value as a real, indexable column derived from the JSON. Later you can turn it into a plain column, fill it from the JSON, and remove the key from the document.

Performance notes

• Reading a value inside a JSON document is fast (the binary format allows direct access), but slower than a plain column. Reading a whole big document is slower than reading a few columns.
• Large documents (over a few KB) are stored outside the row page, like TEXT. Reading them costs an extra lookup.
• Writes to a JSON column with an index on a path update the index entries.
• Storage: keys are repeated in every row. 10 million rows with a 20-character key repeated in each is 200 MB for the keys alone.

A decision guide

Ask these questions in order:

1. Do I filter, sort, group or join on it in common queries? -> Column.
2. Does it need a foreign key, a unique rule or a strict type? -> Column.
3. Does the structure vary from row to row, or change often? -> JSON is a good fit.
4. Is it read and written as a whole and rarely queried by its parts? -> JSON.
5. Not sure? Start with columns for what you know, and one JSON column for the variable rest.

Document databases

If most of your data is flexible documents with nested structures, a document database (MongoDB) may fit better than forcing MySQL into the role. If most of your data is structured and relational with a few flexible parts, MySQL with a JSON column is excellent.