MariaDB JSON : Type, Functions, Indexing, Validation

Deterministic guidance for working with the JSON type and JSON_* functions in MariaDB. Use this skill to declare valid JSON columns, choose the correct extraction function, index JSON paths through generated columns, merge documents with the correct semantics, and avoid the most damaging MySQL-vs-MariaDB JSON traps.

Quick Reference

• MariaDB JSON is an ALIAS for LONGTEXT, NOT native binary storage like MySQL 5.7.8+. Use CHECK (JSON_VALID(col)) for structural validation. For indexing, use functional indexes on virtual or persistent generated columns ; direct expression-indexes are not supported. This is the single most consequential MySQL divergence (D-010, L-005). Verified : KB json-data-type/.
• ALWAYS add CHECK (JSON_VALID(col)) on every JSON column. Without the CHECK constraint, INSERT INTO t (j) VALUES ('not even json') succeeds silently and breaks every downstream JSON_EXTRACT. The KB confirms : "the JSON_VALID function can be used as a CHECK constraint" ; from the JSON alias the constraint is wired automatically only when the column type is JSON, not when it is LONGTEXT.
• JSON_VALUE returns a SCALAR ; JSON_QUERY returns a JSON sub-tree ; JSON_EXTRACT returns JSON (scalar wrapped or sub-tree). Pick by what you actually need downstream. ALWAYS use JSON_VALUE in WHERE-clauses and functional indexes : it strips the JSON quoting so WHERE JSON_VALUE(doc,'$.status') = 'open' compares strings, not JSON-quoted strings. Verified : KB json_value/, json_query/, json_extract/.
• Use JSON_MERGE_PATCH for RFC 7396 semantics ; use JSON_MERGE_PRESERVE for array concatenation. NEVER use JSON_MERGE. JSON_MERGE is the deprecated alias and silently behaves like JSON_MERGE_PRESERVE, not like JSON_MERGE_PATCH. Migrations from MySQL 5.7 code that called JSON_MERGE for partial-update semantics produce wrong results on MariaDB unless rewritten. Verified : KB json_merge_patch/, json_merge_preserve/.
• JSON_MERGE_PATCH (RFC 7396) : a key whose patch value is JSON null is REMOVED from the result. Use this for "PATCH /resource"-style partial updates. Use JSON_REMOVE only when you want to drop the key by path without a patch document.
• Functional indexes on JSON require a virtual or persistent generated column. ALWAYS materialise the expression first, then index the column. Direct CREATE INDEX ix ON t ((JSON_VALUE(doc,'$.k'))) syntax (as in MySQL 8 or PostgreSQL) does NOT parse on MariaDB. Verified : KB generated-columns/.
• JSON_TABLE (10.6+) converts JSON arrays to relational rows with NESTED PATH for hierarchies, FOR ORDINALITY for row counters, and EXISTS PATH for boolean existence checks. ON EMPTY and ON ERROR accept NULL | DEFAULT 'literal' | ERROR ; both default to NULL when omitted. Verified : KB json_table/.
• JSON path uses $ for root, .key for member, [N] for index, [*] for all array elements, and ** for recursive descent. The recursive descent token is **, NOT .. (PostgreSQL / jq syntax) and ** must be followed by another step (never the final token). Negative indices [-N] and ranges [M to N] are 10.9+. Verified : KB jsonpath-expressions/.
• JSON column collation is utf8mb4_bin and storage is LONGTEXT. Inserts in any other character set MUST be transcoded by the client to utf8mb4 ; an inbound latin1 JSON document with non-ASCII bytes will be mangled. ALWAYS run SET NAMES utf8mb4 on the connection.
• Row-based replication does NOT work for JSON columns between MySQL master and MariaDB replica. MySQL ships the binary format on the wire ; MariaDB cannot decode it. Use statement-based replication or convert the column to TEXT before replication. Verified : KB json-data-type/.
• JSON comparison semantics differ from MySQL. MariaDB compares JSON columns as strings ; MySQL compares structurally. Two JSON documents that are semantically equal but differ in whitespace or key-order compare as equal in MySQL and unequal in MariaDB. ALWAYS use JSON_EQUALS(a, b) for structural equality on MariaDB.

Decision Trees