MySQL – JSON Columns

MySQL 5.7 added a native JSON type and MySQL 8 made it genuinely useful. It is not a TEXT column with a naming convention: values are validated on write, stored in a parsed binary form, and addressable by path without re-parsing the document each time.

The pizza schema does not use it, which is worth saying plainly — everything the application needs is well enough understood to be a column. This lesson adds a table for the shape JSON is actually good at: per-order options that vary by order and that nothing queries in bulk.

A JSON column

CREATE TABLE order_options (
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    options  JSON   NOT NULL
);

INSERT INTO order_options (order_id, options) VALUES
 (1, '{"delivery": {"leaveAtDoor": true, "note": "ring twice"}, "cutlery": false, "tags": ["rush","gift"]}'),
 (2, '{"delivery": {"leaveAtDoor": false}, "cutlery": true, "tags": []}'),
 (3, '{"cutlery": true, "tags": ["contactless"]}');

Invalid JSON is rejected at insert time with an error, which is the first thing a TEXT column cannot do. A JSON column also cannot have a DEFAULT, and it is always nullable-or-not like any other column.

Reading values out

SELECT order_id,
       options->'$.cutlery'                  AS cutlery_json,
       options->>'$.delivery.note'           AS note,
       JSON_EXTRACT(options, '$.tags')       AS tags
FROM   order_options ORDER BY order_id;

+----------+--------------+------------+------------------+
| order_id | cutlery_json | note       | tags             |
+----------+--------------+------------+------------------+
|        1 | false        | ring twice | ["rush", "gift"] |
|        2 | true         | NULL       | []               |
|        3 | true         | NULL       | ["contactless"]  |
+----------+--------------+------------+------------------+

Three ways of saying nearly the same thing:

• -> is shorthand for JSON_EXTRACT. It returns a JSON value, so a string comes back with its quotes.
• ->> is JSON_UNQUOTE(JSON_EXTRACT(…)). It returns text, which is what you want for display and for comparing to a string.

That distinction causes real confusion: options->'$.delivery.note' = 'ring twice' is false, because the left side is "ring twice" with quotes. Use ->> whenever you are comparing to a plain value.

A missing path yields NULL rather than an error — orders 2 and 3 have no note. Paths start at $, use . for object keys and [n] for array elements, and [*] for all of them.

Inspecting

SELECT JSON_TYPE(options->'$.tags') AS tags_type,
       JSON_LENGTH(options->'$.tags') AS tag_count,
       JSON_CONTAINS(options->'$.tags', '"rush"') AS has_rush
FROM   order_options WHERE order_id = 1;

+-----------+-----------+----------+
| tags_type | tag_count | has_rush |
+-----------+-----------+----------+
| ARRAY     |         2 |        1 |
+-----------+-----------+----------+

Note the candidate in JSON_CONTAINS is '"rush"' — a JSON string, quotes included. Passing 'rush' is not valid JSON and errors.

Building and modifying

SELECT JSON_SET('{"a":1}', '$.b', 2) AS after_set,
       JSON_OBJECT('id', 7, 'ok', TRUE) AS built,
       JSON_ARRAY('x', 'y') AS arr;

+------------------+-----------------------+------------+
| after_set        | built                 | arr        |
+------------------+-----------------------+------------+
| {"a": 1, "b": 2} | {"id": 7, "ok": true} | ["x", "y"] |
+------------------+-----------------------+------------+

Three that look alike and are not: JSON_SET inserts or replaces, JSON_INSERT only adds if the path is absent, JSON_REPLACE only changes what is already there. JSON_REMOVE deletes a path.

Use them in an UPDATE to change one field without rewriting the document:

UPDATE order_options SET options = JSON_SET(options, '$.cutlery', TRUE) WHERE order_id = 1;

JSON_TABLE — turning a document into rows

SELECT o.order_id, t.tag
FROM   order_options o,
       JSON_TABLE(o.options, '$.tags[*]' COLUMNS (tag VARCHAR(40) PATH '$')) AS t
ORDER  BY o.order_id, t.tag;

+----------+-------------+
| order_id | tag         |
+----------+-------------+
|        1 | gift        |
|        1 | rush        |
|        3 | contactless |
+----------+-------------+

This is the MySQL 8 feature that makes JSON columns properly usable. Once the array is rows, the whole of SQL applies — join it, group it, filter it. Note order 2 disappears: its tags array is empty, so it produces no rows. Use LEFT JOIN … JSON_TABLE(…) if you need it kept.

Indexing: only through a generated column

You cannot index a JSON column directly. What you can do is extract the value into a generated column and index that:

ALTER TABLE order_options
    ADD COLUMN cutlery BOOLEAN AS (options->'$.cutlery') VIRTUAL,
    ADD INDEX idx_order_options_cutlery (cutlery);

A VIRTUAL column is computed on read and stores nothing; the index on it is stored, which is what makes the lookup fast. STORED writes the value to the row as well — worth it only when the expression is expensive.

Without this, every query filtering on a JSON path reads every row. That is usually acceptable for a document you fetch by primary key and ignore otherwise, and completely unacceptable for something you search on.

Should this be a column?

Ask it every time, because JSON is easy to reach for and expensive to live with:

The thing you give up is the database's help. A JSON field has no type checking beyond "valid JSON", no NOT NULL, no foreign key, no unique constraint, and no index unless you add the generated column above. A typo in a key name is not an error — it is a NULL, appearing wherever that value was meant to be read.

The pizza schema's customer_order could have been one JSON blob. It is columns because every order has a status, a total and a customer, and the reports group by all three.

What to remember

• -> returns JSON (quotes included); ->> returns text. Compare with ->>.
• A missing path is NULL, not an error — including when you misspell a key.
• JSON_TABLE turns a document into rows, and then normal SQL applies.
• Indexing needs a generated column; without one, filtering scans everything.
• If every row has the field and anything queries it, make it a column.