JSON Documents, Paths, JSON_TABLE, Indexable JSON Patterns, and Validation

Learning outcomes

Operational schemas sometimes need a small amount of semi-structured data: a work order may carry channel, asset metadata, required skills, and an SLA value that vary by asset type. A native JSON column can be a good boundary when the attributes are genuinely flexible, but “put it in JSON” is not a license to abandon types, constraints, or indexes.

This lesson treats JSON as part of a relational design. We will validate documents, distinguish JSON null from SQL NULL, project arrays into rows with JSON_TABLE(), and make a frequently queried path indexable. The goal is not to turn MySQL into a document database.

Native JSON validates syntax; your schema validates meaning

A MySQL JSON column accepts valid JSON values and rejects malformed JSON text. That solves syntax validation, not domain validation. A valid JSON array is still the wrong shape if your application contract says attributes must be an object. The lab therefore adds a CHECK that allows either SQL NULL or a JSON object.

USE servicehub_analytics_lab;SHOW CREATE TABLE work_orders;SELECT work_order_id,       JSON_TYPE(attributes) AS json_typeFROM work_ordersORDER BY work_order_id;

-- Malformed JSON text: rejected by the JSON data type.INSERT INTO work_orders(work_order_id,customer_id,technician_id,status,priority,opened_at, labor_minutes,parts_cost,summary,attributes)VALUES(2001,1,11,'open',2,NOW(),10,0,'bad json syntax','{"channel": portal}');-- Valid JSON, wrong business shape: rejected by chk_attributes_object.INSERT INTO work_orders(work_order_id,customer_id,technician_id,status,priority,opened_at, labor_minutes,parts_cost,summary,attributes)VALUES(2002,1,11,'open',2,NOW(),10,0,'wrong json shape',JSON_ARRAY('portal','api'));-- Verify neither failed row exists.SELECT COUNT(*) FROM work_orders WHERE work_order_id IN (2001,2002);

Error text can vary slightly by patch/client, but the first failure is invalid JSON text and the second is an enforced check-constraint violation. The final count must remain zero.

Paths and scalar extraction: JSON text versus SQL text

A JSON path begins at $. Object members use dot notation such as $.asset.type; arrays can use indexes or wildcards. The -> operator returns a JSON value, while ->> unquotes a scalar into SQL text. JSON_VALUE() is useful when you want a typed scalar result.

SELECT work_order_id,       attributes->'$.channel' AS channel_json,       attributes->>'$.channel' AS channel_text,       JSON_VALUE(attributes,'$.sla_hours' RETURNING UNSIGNED) AS sla_hours,       attributes->>'$.asset.type' AS asset_typeFROM work_ordersWHERE attributes IS NOT NULLORDER BY work_order_id;

Choose an extraction form that matches the consumer. Quoted JSON strings are correct JSON but awkward for relational comparison/display. A typed JSON_VALUE(... RETURNING ...) expression can also be reused in a functional index.

JSON null, missing path, and SQL NULL are different states

Work order 1001 contains a JSON member "note": null. Work order 1010 has SQL NULL in the entire attributes column. A missing path is another case. If you collapse these states too early, the application cannot tell “document absent,” “member absent,” and “member explicitly JSON null” apart.

SELECT work_order_id,       attributes IS NULL AS attributes_is_sql_null,       JSON_TYPE(JSON_EXTRACT(attributes,'$.note')) AS note_json_type,       JSON_EXTRACT(attributes,'$.missing_member') IS NULL AS missing_path_is_sql_nullFROM work_ordersWHERE work_order_id IN (1001,1002,1010)ORDER BY work_order_id;

For 1001, the extracted note has JSON type NULL; for a missing path, extraction yields SQL NULL; and for 1010 the whole document is SQL NULL. Test the state you actually mean.

JSON_TABLE turns repeated JSON values into relational rows

The skills member is an array. Searching it with string patterns would be fragile. JSON_TABLE() maps array elements to a derived relational table, after which normal joins, grouping, and predicates apply.

SELECT w.work_order_id,       jt.ord,       jt.skillFROM work_orders AS wJOIN JSON_TABLE(       COALESCE(w.attributes, JSON_OBJECT('skills', JSON_ARRAY())),       '$.skills[*]'       COLUMNS (         ord FOR ORDINALITY,         skill VARCHAR(30) PATH '$'       )     ) AS jt ON TRUEWHERE w.attributes IS NOT NULLORDER BY w.work_order_id, jt.ord;

SELECT jt.skill,       COUNT(*) AS work_order_mentionsFROM work_orders AS wJOIN JSON_TABLE(       COALESCE(w.attributes, JSON_OBJECT('skills', JSON_ARRAY())),       '$.skills[*]'       COLUMNS (skill VARCHAR(30) PATH '$')     ) AS jt ON TRUEWHERE w.attributes IS NOT NULLGROUP BY jt.skillORDER BY work_order_mentions DESC, jt.skill;

JSON_TABLE() is powerful, but it is still query work. If a skill becomes a high-cardinality, heavily joined business entity with integrity rules, promote it to a relational table rather than forcing every workload through JSON expansion.

Before tuning, prove the predicate and the current access path

Assume channel becomes a frequent filter. First write the exact predicate and inspect the plan. On a tiny twelve-row table, a full scan may be rational; the important baseline is what indexes are eligible and what the optimizer currently chooses.

EXPLAIN FORMAT=TREESELECT work_order_id, summaryFROM work_ordersWHERE JSON_VALUE(attributes,'$.channel' RETURNING CHAR(20)) = 'portal';EXPLAIN ANALYZESELECT work_order_id, summaryFROM work_ordersWHERE JSON_VALUE(attributes,'$.channel' RETURNING CHAR(20)) = 'portal';

Do not turn the local timing into a performance promise. Record row count, schema, server version, and plan if you compare changes.

Tempting but ineffective tuning: index a different column

A developer sees a slow query and adds an index on status because status is “commonly used.” That index can help queries filtering status, but it does not make the JSON channel expression searchable. This is a useful failure because the DDL succeeds yet the target predicate is unchanged.

CREATE INDEX ix_work_orders_status ON work_orders(status);SHOW INDEX FROM work_orders;EXPLAINSELECT work_order_id, summaryFROM work_ordersWHERE JSON_VALUE(attributes,'$.channel' RETURNING CHAR(20)) = 'portal';DROP INDEX ix_work_orders_status ON work_orders;

For the channel-only predicate, ix_work_orders_status should not appear as a useful access path. The lesson is methodological: tune the predicate that actually limits the query, not a nearby column.

Corrected alternative: index the scalar expression you query

MySQL can index an expression based on JSON_VALUE(). The query must use a compatible expression so the optimizer can match it. This turns a frequently queried scalar inside JSON into an indexable access path without duplicating the entire document into relational columns.

CREATE INDEX ix_work_orders_channelON work_orders (  (JSON_VALUE(attributes,'$.channel' RETURNING CHAR(20))));ANALYZE TABLE work_orders;SHOW INDEX FROM work_orders WHERE Key_name='ix_work_orders_channel';EXPLAINSELECT work_order_id, summaryFROM work_ordersWHERE JSON_VALUE(attributes,'$.channel' RETURNING CHAR(20)) = 'portal';

On this tiny seed, MySQL may still choose a table scan because reading twelve rows is cheap. That does not mean the index is malformed. Check possible_keys, the chosen key, row estimates, and—on a representative larger dataset—actual execution evidence. An index is an available access structure, not a command that forces the optimizer to use it.

Hands-on lab and cleanup

1. Inspect SHOW CREATE TABLE and identify both JSON syntax validation and the object-shape check.
2. Trigger malformed-JSON and wrong-shape failures; verify neither row was inserted.
3. Compare ->, ->>, and typed JSON_VALUE().
4. Distinguish JSON null, missing path, and SQL NULL with the provided probe query.
5. Use JSON_TABLE() to count skill mentions.
6. Capture the channel-filter plan, add the irrelevant status index, then add the expression index and compare evidence on the same table.

DROP INDEX ix_work_orders_channel ON work_orders;-- The next lesson needs the data, not this optional tuning artifact.SELECT COUNT(*) AS work_orders_after_cleanup FROM work_orders;

Production judgment and next bridge

Use JSON when variability is real and the relational core remains clear. Promote frequently constrained or joined attributes to typed columns/tables when that improves integrity and access patterns. For JSON paths that stay flexible but become hot predicates, index deliberately and verify with plan evidence. Avoid storing opaque blobs of application state that no SQL consumer can reason about.

Next: we return to scalar SQL functions—date/time, collation-aware strings, ICU regular expressions, exact/approximate numeric behavior, and conditional expressions—organized around real transformation problems rather than a function catalog.

Authoritative references

• MySQL 8.4 Reference Manual — The JSON Data Type
• MySQL 8.4 Reference Manual — JSON Functions
• MySQL 8.4 Reference Manual — JSON Table Functions
• MySQL 8.4 Reference Manual — Functions That Search JSON Values / JSON_VALUE
• MySQL 8.4 Reference Manual — Optimizer Use of Generated Column Indexes
• MySQL 8.4 Reference Manual — CREATE INDEX