MariaDB / Advanced: evidence and reliable operations / Lesson 16

JSON and MariaDB compatibility boundaries

Store flexible parcel metadata while preserving relational integrity and searchable values. Understand why MariaDB JSON is not MySQL binary JSON.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Explain JSON text representation and validation.
Extract and index deliberate scalar values with version checks.

JSON is a text alias in MariaDB

MariaDB JSON is an alias for LONGTEXT with utf8mb4_bin collation, rather than MySQL native binary JSON storage. Modern supported releases attach JSON validity checking to the alias; inspect SHOW CREATE TABLE on the exact release and use an explicit CHECK when defining a plain text document column. Valid JSON only proves syntax, not that a required weight exists or is positive. Keep core identity, foreign keys and financial amounts relational when they need ordinary constraints and joins.

Extract a typed scalar deliberately

The literal document contains a carrier string and a numeric weight. JSON_EXTRACT returns JSON-formatted values, while JSON_UNQUOTE obtains text from a JSON string. Casting makes the desired numeric comparison explicit. The reasoned results are Harbor and 2.50. A missing path can produce SQL NULL, while a JSON null value has its own representation; inspect behavior with the selected functions rather than treating missing, null, empty string and zero as identical.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT
 JSON_UNQUOTE(JSON_EXTRACT('{"carrier":"Harbor","kg":2.5}', '$.carrier')) AS carrier,
 CAST(JSON_UNQUOTE(JSON_EXTRACT('{"carrier":"Harbor","kg":2.5}', '$.kg'))
 AS DECIMAL(6,2)) AS kg;

Expected output

carrier | kg
Harbor  | 2.50

The result is calculated from a literal document and does not depend on server records.

Promote frequently queried fields

When carrier is a stable reporting dimension, a normal column may be clearer than repeatedly parsing documents. A generated column is another option: define the extraction once and index that scalar. Ensure document validation and length limits fit the projected value. MariaDB VIRTUAL and PERSISTENT generated-column options and their indexing restrictions differ by release and engine. Query the generated column explicitly for a portable baseline; newer optimizers may recognize more expression equivalences than older releases.

Index the declared business projection

This training table keeps a document but exposes carrier as a bounded generated scalar. The example assumes a modern supported MariaDB release and InnoDB; inspect its effective definition and test invalid, missing and overlong values under the intended SQL mode. No claim is made that the index will be chosen on a tiny fixture. The query names carrier directly, which makes the report contract and candidate index visible during plan review.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE TABLE parcel_meta (
 parcel_id INT PRIMARY KEY, payload JSON NOT NULL,
 carrier VARCHAR(40) AS
 (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.carrier'))) PERSISTENT,
 INDEX ix_meta_carrier (carrier)
) ENGINE=InnoDB;
EXPLAIN SELECT parcel_id FROM parcel_meta WHERE carrier = 'Harbor';

A generated scalar avoids assuming that indexing the whole JSON text supplies efficient path lookup.

Compatibility requires data-level tests

MySQL binary JSON files and MariaDB text JSON are not interchangeable storage formats. Cross-product replication of native JSON needs a documented conversion strategy; do not assume row-based replication can transparently translate it. Duplicate object keys are another trap: MariaDB extraction can expose the first matching key, so reject ambiguous documents at ingestion. Compare ordering, numeric conversion, escaping and null semantics with representative data during migration. JSON_TABLE is available from MariaDB 10.6, but its presence does not make every MySQL JSON operator or expression identical.

Common errors and fixes

JSON text is mistaken for MySQL binary JSON

Why it happens: Shared SQL type names hid different physical representations.
How to fix it: Use a documented logical conversion and validate values on the destination.
Prevent it next time: Include JSON representation in compatibility and replication reviews.

A JSON number sorts as text

Why it happens: An extracted/unquoted string was compared without numeric conversion.
How to fix it: Validate the value and cast to the intended numeric type.
Prevent it next time: Document scalar types rather than trusting sample formatting.

A document passes validation but lacks required business fields

Why it happens: JSON_VALID checks syntax, not the application schema.
How to fix it: Add explicit field validation and relational constraints where appropriate.
Prevent it next time: Test missing, null, wrong-type and duplicate-key documents.

Check your understanding

1. The values "9" and "100" are extracted as strings. Why can lexical sorting disagree with weight order?

Show worked solution

Text ordering compares character sequences, so a leading 1 can sort before 9 even though 100 is numerically larger. Validate numeric content and cast to a suitable DECIMAL before comparing. Better still, maintain a constrained numeric column for a core weight measurement and reserve JSON for optional metadata that does not weaken the model.

2. A MySQL source has native JSON and a team proposes copying its data files into MariaDB. What should the migration review require?