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
Tree 1 : "Which extraction function do I need?"
What do I want to extract from the JSON document?
A single scalar value (string, number, bool) to compare or index ?
-> JSON_VALUE(doc, '$.path'). Returns unwrapped scalar. Use in WHERE,
ORDER BY, and as the expression in a generated column for indexing.
A nested object or array to feed to another JSON function ?
-> JSON_QUERY(doc, '$.path'). Returns JSON sub-tree, never a scalar.
Either, and I will handle wrapping downstream ?
-> JSON_EXTRACT(doc, '$.path'). Returns JSON ; scalars come back
JSON-quoted ("open"), which is rarely what WHERE-clauses want.
Tree 2 : "How do I merge two JSON documents?"
What is the intent of the merge?
Partial update : "apply these changes, NULL removes a key" (PATCH /resource) ?
-> JSON_MERGE_PATCH(target, patch). RFC 7396 semantics.
Patch '{"a":null}' on '{"a":1,"b":2}' yields '{"b":2}'.
Combine two documents, preserve all keys and concatenate arrays ?
-> JSON_MERGE_PRESERVE(a, b). PRESERVE preserves duplicates.
Merge of [1,2] and [2,3] yields [1,2,2,3].
Legacy code calls JSON_MERGE(a, b) ?
-> REWRITE. JSON_MERGE is deprecated and silently aliases
JSON_MERGE_PRESERVE. MySQL 5.7 code that expected PATCH semantics
must be migrated to JSON_MERGE_PATCH.
Tree 3 : "How do I index a JSON path?"
The query filters on a stable JSON key path ?
Yes :
Step 1 : Add a generated column over the path.
ALTER TABLE t
ADD COLUMN status VARCHAR(32)
AS (JSON_VALUE(doc, '$.status')) PERSISTENT;
VIRTUAL : zero disk, computed on read, still indexable.
PERSISTENT : materialised, faster reads, costs disk + write CPU.
Step 2 : Index the column.
CREATE INDEX ix_t_status ON t (status);
Step 3 : Verify with EXPLAIN that the query uses the index.
No, JSON keys are dynamic ?
-> Cannot be efficiently indexed. Consider Dynamic Columns,
a key-value table, or schema redesign.
Tree 4 : "Do I store this as JSON or normalise it?"
Is the document structure stable, queried often, and joined to other tables ?
Yes -> Normalise. Columns and rows. Indexes work as designed.
Is the document a flexible attribute bag with sparse, varied keys ?
Yes -> JSON with CHECK (JSON_VALID(col)). Add functional indexes for
the few paths you query often.
Is the document a black-box payload that you store and replay ?
Yes -> LONGTEXT or JSON. Whether to use JSON depends on whether
downstream code needs JSON_* functions.
Are you considering Dynamic Columns instead ?
See mariadb-syntax-dynamic-columns. Default to JSON for new schemas
because of standards compliance and JSONPath ergonomics.
Patterns
Pattern 1 : JSON column with validation
CREATE TABLE event (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
payload JSON NOT NULL CHECK (JSON_VALID(payload)),
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;
INSERT INTO event (payload) VALUES ('not json');
INSERT INTO event (payload) VALUES ('{"type":"ok"}');
Pattern 2 : JSON_VALUE versus JSON_QUERY versus JSON_EXTRACT
SET @doc = '{"status":"open","tags":["red","blue"]}';
SELECT JSON_VALUE(@doc, '$.status');
SELECT JSON_QUERY(@doc, '$.tags');
SELECT JSON_EXTRACT(@doc, '$.status');
SELECT 1 FROM dual WHERE JSON_EXTRACT(@doc,'$.status') = 'open';
SELECT 1 FROM dual WHERE JSON_VALUE (@doc,'$.status') = 'open';
Pattern 3 : Functional index on a JSON path (D-010)
CREATE TABLE event (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
payload JSON NOT NULL CHECK (JSON_VALID(payload)),
event_type VARCHAR(64) AS (JSON_VALUE(payload, '$.type')) VIRTUAL,
INDEX ix_event_type (event_type)
) ENGINE=InnoDB;
SELECT id FROM event WHERE event_type = 'order.created';
EXPLAIN SELECT id FROM event WHERE event_type = 'order.created';
Pattern 4 : JSON_MERGE_PATCH for partial-update API
SET @current = '{"name":"alice","email":"a@x","verified":true}';
SET @patch = '{"email":"alice@x","verified":null}';
SELECT JSON_MERGE_PATCH(@current, @patch);
Pattern 5 : JSON_MERGE_PRESERVE for array concatenation
SELECT JSON_MERGE_PRESERVE('[1,2]', '[2,3]');
SELECT JSON_MERGE_PRESERVE('{"k":1}', '{"k":2}');
Pattern 6 : JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE
SELECT JSON_SET('{"a":1}', '$.a', 9, '$.b', 2);
SELECT JSON_INSERT('{"a":1}', '$.a', 9, '$.b', 2);
SELECT JSON_REPLACE('{"a":1}', '$.a', 9, '$.b', 2);
SELECT JSON_REMOVE('{"a":1,"b":2}', '$.b');
Pattern 7 : JSON_TABLE to project an array to rows (10.6+)
SET @doc = '[
{"name":"Laptop","color":"black","price":1000},
{"name":"Jeans","color":"blue"}
]';
SELECT id, name, color, price
FROM JSON_TABLE(@doc, '$[*]' COLUMNS (
id FOR ORDINALITY,
name VARCHAR(40) PATH '$.name',
color VARCHAR(20) PATH '$.color',
price DECIMAL(10,2) PATH '$.price' DEFAULT '0' ON EMPTY
)) AS jt;
Pattern 8 : NESTED PATH for hierarchies
SET @doc = '{
"orders":[
{"id":1,"items":["a","b"]},
{"id":2,"items":["c"]}
]
}';
SELECT order_id, item
FROM JSON_TABLE(@doc, '$.orders[*]' COLUMNS (
order_id INT PATH '$.id',
NESTED PATH '$.items[*]' COLUMNS (
item VARCHAR(40) PATH '$'
)
)) AS jt;
Pattern 9 : JSON path expressions cheat-sheet
SELECT JSON_VALUE('{"a":{"b":{"c":42}}}', '$.a.b.c');
SELECT JSON_QUERY('[[1,2],[3,4]]', '$[1]');
Pattern 10 : Detecting and rejecting invalid JSON on import
CREATE TABLE event_staging (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
raw LONGTEXT NOT NULL
) ENGINE=InnoDB;
SELECT id, LEFT(raw, 80) AS preview
FROM event_staging
WHERE JSON_VALID(raw) = 0;
INSERT INTO event (payload)
SELECT raw FROM event_staging WHERE JSON_VALID(raw) = 1;
Reference Links
references/methods.md : full JSON_* function reference with signature, version, return-type, KB link.references/examples.md : 12+ working JSON examples covering DDL, extraction, mutation, merging, projection, indexing.references/anti-patterns.md : 8+ real-world JSON mistakes on MariaDB with the fix.
External References
- MariaDB KB :
https://mariadb.com/kb/en/json-data-type/
- MariaDB KB :
https://mariadb.com/kb/en/json-functions/
- MariaDB KB :
https://mariadb.com/kb/en/json_value/
- MariaDB KB :
https://mariadb.com/kb/en/json_query/
- MariaDB KB :
https://mariadb.com/kb/en/json_extract/
- MariaDB KB :
https://mariadb.com/kb/en/json_table/
- MariaDB KB :
https://mariadb.com/kb/en/json_merge_patch/
- MariaDB KB :
https://mariadb.com/kb/en/json_merge_preserve/
- MariaDB KB :
https://mariadb.com/kb/en/jsonpath-expressions/
- MariaDB KB :
https://mariadb.com/kb/en/generated-columns/
