MySQL / Concurrency, performance, and operations / Lesson 15

JSON and relational boundaries

Use flexible documents where they fit while keeping typed identities, relationships, and query paths explicit.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish JSON values, SQL values, JSON null, and missing paths.
Project arrays with JSON_TABLE and plan typed scalar indexing.

Flexible attributes are not a whole schema

The shop's product identity, price, and stock remain typed relational columns. Optional catalogue attributes such as color or packaging can vary by product and may fit a JSON document. Putting customer ids and order lines into opaque documents would weaken the ordinary foreign-key model and complicate joins. Flexibility moves validation responsibility; it does not remove the need for a documented contract.

MySQL uses a native JSON type

MySQL 8.4 validates JSON input and stores it in an internal binary representation. A JSON string, number, boolean, object, array, and null are distinct types. SQL NULL is separate again. MariaDB's JSON representation is different and must not be assumed to share MySQL storage or indexing behavior. The following standalone literal examples do not add columns or change the shop fixture.

sql Separate extraction from presence

WITH sample AS (
 SELECT CAST('{"color":"blue","note":null}' AS JSON) AS doc
)
SELECT JSON_EXTRACT(doc,'$.color') AS json_color,
 JSON_UNQUOTE(JSON_EXTRACT(doc,'$.color')) AS sql_color,
 JSON_CONTAINS_PATH(doc,'one','$.note') AS has_note,
 JSON_CONTAINS_PATH(doc,'one','$.missing') AS has_missing
FROM sample;

Expected output

json_color | sql_color | has_note | has_missing
"blue" | blue | 1 | 0

The JSON color is the JSON string "blue", while unquoting gives SQL text blue. The note key exists even though its JSON value is null, so has_note is 1; the absent key gives has_missing 0. An IS NULL check on an absent extraction answers a different question from explicit path existence.

Turn arrays into typed rows

JSON_TABLE projects a document into a relational shape with explicit column types and paths. FOR ORDINALITY can retain array position, beginning at one. Decide what missing fields and conversion errors should do using ON EMPTY and ON ERROR clauses. Silent defaults can make malformed catalogue data look legitimate, so strict import workflows may prefer ERROR ON ERROR where appropriate. Always validate the chosen policy with malformed examples.

sql Project a synthetic order request

SELECT j.position,j.product_id,j.quantity
FROM JSON_TABLE(
 '[{"product_id":1,"quantity":2},{"product_id":2,"quantity":2}]',
 '$[*]' COLUMNS (
 position FOR ORDINALITY,
 product_id INT PATH '$.product_id' ERROR ON EMPTY ERROR ON ERROR,
 quantity INT PATH '$.quantity' ERROR ON EMPTY ERROR ON ERROR
 )) AS j ORDER BY j.position;

Expected output

position | product_id | quantity
1 | 1 | 2
2 | 2 | 2

The output contains positions 1 and 2 with product ids 1 and 2 and quantity 2 each. Projection does not prove that products exist or stock is available. A real write must still validate positive quantities, match product keys, guard stock changes, and insert relational lines transactionally.

Index a typed scalar deliberately

A JSON column is not directly a conventional scalar B-tree key. A common design extracts a scalar into a generated column with an explicit SQL type and indexes that column. Expression compatibility, collation, and data conversion determine whether a query can use the index. Multi-valued indexes exist for supported JSON-array cases but carry restrictions; they are not a replacement for relational line tables and foreign keys.

Version the document contract

Specify required keys, numeric versus string values, maximum nesting, and the meaning of absent versus explicit null. JSON_SET can update selected paths, but an eligible partial update is an optimization, not a universal guarantee for every modification. Measure storage and log behavior rather than assuming every tiny document edit is cheap. Preserve backward compatibility when old application versions can still send earlier document shapes.

Common errors and fixes

ERROR 3140 (22032): Invalid JSON text.

Why it happens: The document uses malformed syntax, such as single-quoted JSON keys.
How to fix it: Serialize with a real JSON library and reject invalid input before persistence.
Prevent it next time: Test escaping, Unicode, and nested data through the serializer.

A missing key is confused with explicit JSON null.

Why it happens: Extraction alone was used without checking presence and value type.
How to fix it: Use JSON_CONTAINS_PATH and inspect JSON_TYPE where the distinction matters.
Prevent it next time: Document null and absence separately in the API contract.

A JSON search ignores the intended scalar index.

Why it happens: The query expression or type differs from the indexed generated expression.
How to fix it: Align typed extraction and inspect EXPLAIN with representative data.
Prevent it next time: Keep generated-column definitions and query expressions reviewed together.

Check your understanding

1. Does has_note = 1 prove that note contains useful text?

Show worked solution

No. The key exists but contains JSON null in the example. Presence, type, and domain validity are separate checks. Require a JSON string and apply the intended text validation if a usable note is mandatory; simply substituting empty text would erase the distinction.

2. Why should the JSON_TABLE request not be inserted directly as trusted order lines?