Optimizing MariaDB JSON Queries with Virtual Generated Columns

The Performance Gap in Schema-less Storage

Storing data as JSON in MariaDB is an excellent way to handle rapidly evolving application models or third-party API responses without running a migration every time a field is added. However, there is a significant performance trap: JSON_EXTRACT and other JSON functions perform full table scans. Because the database cannot "look inside" a JSON blob using a standard B-tree index, every query filtering by a JSON key must examine every single row in the table.

The solution is to use Virtual Generated Columns. These allow you to project a specific JSON key into a virtual column that the database can index, giving you the flexibility of a document store with the lookup speed of a relational table.

How Virtual Columns Bridge the Gap

A virtual generated column is a column whose value is derived from other columns in the same row using a deterministic function. In the case of JSON, you use a JSON path expression to extract a value. Because it is "virtual," the data is not stored twice on disk; it is calculated on the fly during reads. However, MariaDB allows you to create an index on this virtual column, which persists the extracted values in the index tree.

Implementation: Indexing a JSON Attribute

Consider a scenario where you store user profiles in a JSON column called profile_data. You frequently need to query users by their city, which is nested inside the JSON.

Run these commands on your MariaDB instance (version 10.2+). You will need CREATE and INDEX permissions on the target database.

-- 1. Create the table with a JSON column
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    profile_data JSON
);

-- 2. Add a virtual generated column for the 'city' key
-- The '->>' operator is a shortcut for JSON_UNQUOTE(JSON_EXTRACT(...))
ALTER TABLE user_profiles 
ADD COLUMN user_city VARCHAR(100) 
AS (profile_data->>>>'$.address.city') VIRTUAL;

-- 3. Create a B-tree index on the virtual column
CREATE INDEX idx_user_city ON user_profiles(user_city);

Verification: To ensure the index is actually being used, run an EXPLAIN plan on a query filtering by that city:

EXPLAIN SELECT * FROM user_profiles WHERE user_city = 'New York';

Check the type column in the output. If it says ref instead of ALL, the database is using the index rather than scanning the entire table.

Trade-offs and Engineering Constraints

While this approach solves the read-performance problem, it introduces specific trade-offs:

• Write Overhead: Every time the profile_data column is updated, MariaDB must update the associated index for the virtual column. If you have ten different indexed JSON keys, a single row update triggers ten index updates.
• Lack of Internal Constraints: The JSON data type ensures the document is well-formed (valid syntax), but it does not enforce a schema. If your application inserts {"city": 123} instead of a string, the virtual column will store that value, potentially leading to type mismatches in your application logic.
• Memory Pressure: Using JSON_TABLE to materialize complex JSON arrays into relational rows can be memory-intensive for very large documents, as it creates intermediate result sets in memory.

When to Stick with Native Columns

If a specific attribute in your JSON is queried in almost every request and rarely changes its data type, it is often more efficient to migrate that attribute to a standard native column. Use JSON for the "long tail" of data—attributes that are optional, highly variable, or updated infrequently.

Summary of Action

To optimize your MariaDB JSON implementation: identify the 2-3 most queried keys, project them as VIRTUAL columns, and apply B-tree indexes. This maintains your flexible schema while keeping query latency in the millisecond range.