MariaDB JSON Storage: Choosing Between LONGTEXT, Validation, and Generated Columns

MariaDB does not have a native binary JSON type. Since version 10.2, the JSON keyword is an alias for LONGTEXT with a JSON_VALID check constraint. That single fact forces an engineering decision before you store your first document: are you optimizing for write speed, data integrity, or query performance? The wrong choice turns simple path lookups into full table scans or lets malformed data into production.

This guide compares the supported storage and indexing strategies, shows a concrete implementation using generated columns, and lists the validation steps you should run before committing to a schema.

The Core Constraint: JSON Is Text Plus a Check

When you declare a column as JSON, MariaDB stores it as a LONGTEXT string. The server adds a CHECK constraint that calls JSON_VALID() on every insert and update. If the text is not well-formed JSON, the statement fails.

There is no binary parsing, no automatic path index, and no compression beyond what LONGTEXT already provides. Every indexing strategy must be explicit.

Comparing Supported Options

Option	Storage	Validation	Indexing	Schema flexibility	Best for
Plain LONGTEXT	LONGTEXT	None	None; full scans	High	Write-heavy feeds from trusted producers
JSON alias	LONGTEXT + CHECK	JSON_VALID	None	High	Integrity without schema changes
JSON + generated columns	LONGTEXT + generated cols	JSON_VALID	B-tree on extracted paths	Medium	Mixed workloads with known query paths
Relational columns	Native types	Native constraints	Native indexes	Low	Structured data and heavy analytics

Trade-offs Behind the Table

Plain LONGTEXT avoids validation overhead entirely. Writes are as fast as the storage engine allows. The risk is that a bug in the producer can persist truncated or malformed documents that break every downstream consumer.

The JSON alias moves that risk to the database. The server rejects invalid text at write time. The cost is CPU spent parsing the document on every write. Reads gain nothing; a WHERE clause on a JSON path still scans the whole table.

Generated columns close the read gap. You extract a scalar from a known path into a typed column and index it. MariaDB supports both VIRTUAL columns, computed on read, and PERSISTENT columns, stored on disk. Persistent columns use more storage and slower writes but faster reads. The limitation is rigidity: if your application starts querying a new path, you must alter the table and rebuild the index.

Relational normalization remains the performance winner for predictable data. Native types and indexes outperform any text-based approach. You lose the ability to add fields without a migration.

Concrete Implementation

The following example stores event documents and indexes the user_id path. Run these statements in your MariaDB client or migration tool with CREATE and INSERT privileges.

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doc JSON,
  user_id INT AS (CAST(JSON_VALUE(doc, '$.user_id') AS UNSIGNED)) PERSISTENT,
  KEY idx_user (user_id)
);

Breakdown:

• doc JSON enforces well-formed JSON on write.
• user_id is a generated column. JSON_VALUE() extracts the scalar at $.user_id, and CAST converts it to an unsigned integer.
• PERSISTENT stores the extracted value on disk so the index does not recompute it on every read.
• idx_user is a standard B-tree index on the generated column.

Populate a row:

INSERT INTO events (doc)
VALUES (JSON_OBJECT('user_id', 42, 'event', 'login'));

Query by the indexed path:

SELECT * FROM events WHERE user_id = 42;

Query by an unindexed path:

SELECT * FROM events WHERE JSON_VALUE(doc, '$.event') = 'login';

The second query performs a full table scan unless you add another generated column for $.event. That is the central trade-off: every new indexed path is a schema change.

Validating the Design

Before you rely on this pattern in production, run four checks.

1. Confirm version support

SELECT VERSION();

Generated columns and the JSON alias require MariaDB 10.2 or newer. If your server predates 10.2, use plain LONGTEXT and validate in the application.

2. Verify index usage

EXPLAIN SELECT * FROM events WHERE user_id = 42;

Check that the output references idx_user in the key column. If it does not, the generated column expression may not match the predicate, or statistics may be stale.

3. Test malformed JSON rejection

INSERT INTO events (doc) VALUES ('{bad json');

The server should reject the statement with a constraint violation from JSON_VALID. If it succeeds, the column is not using the JSON alias.

4. Measure write overhead

Compare insert throughput on a JSON column versus a plain LONGTEXT column using your real document sizes. Use BENCHMARK() for micro-measurements or time a batch insert from your application. Validation cost scales with document size and CPU.

Limitations and Version Assumptions

• Row and packet limits. Because JSON is LONGTEXT, large documents are bounded by max_allowed_packet and row-size limits. A single document cannot exceed those boundaries.
• No dynamic path indexing. Generated columns require a fixed path expression. You cannot create a generic index that accelerates any arbitrary JSON_VALUE() call.
• Validation is not schema enforcement. JSON_VALID checks syntax only. It does not verify that user_id is an integer or that required keys exist. Add CHECK constraints or validate in the application.
• Function availability varies. While JSON_VALUE and generated columns exist in 10.2+, later releases added refinements. Verify the exact functions in your target version before deploying.

Rollback and Final Check

If the generated-column approach does not fit, roll back the schema change by dropping the column or table:

ALTER TABLE events DROP COLUMN user_id;
-- or
DROP TABLE events;

After implementation, confirm the result by running EXPLAIN on your hottest queries and inserting a deliberately malformed document. If the index is used and invalid text is rejected, the storage strategy matches your integrity and performance requirements.