How to use JSON in MariaDB

Since version 10.2, MariaDB has supported JSON as a data type. You can store and process flexible, semi-structured data directly in relational tables. This guide explains the most important functions and steps for working with JSON in MariaDB.

What JSON functions does MariaDB provide?

MariaDB provides several functions for working with JSON data directly in the database. You don’t need an additional NoSQL database. With the following tools, you can access information, change content or validate structured data:

• JSON_VALUE(json_doc, json_path): This function extracts a specific value from a JSON field. You specify the JSON document and the path to the desired element. MariaDB returns only that value.
• JSON_SET(json_doc, path, value): This changes an existing entry or adds a new field. If the path exists, MariaDB replaces the value. If it does not exist, MariaDB creates it.
• JSON_REMOVE(json_doc, path): This MariaDB JSON function removes an element from a JSON document. Provide the exact path to the key you want to remove.
• JSON_CONTAINS(json_doc, value, path): Checks whether a specific value is present in a JSON document. This is useful for filtering queries.
• JSON_VALID(json_doc): Validates JSON text. Returns whether the JSON is correctly structured so you can catch errors early.

How to use JSON in MariaDB

In this section, we will show you how to store, query and manipulate JSON data in MariaDB.

Step 1: Create a table with a JSON field

Create a table with a column of type LONGTEXT or JSON. MariaDB stores JSON internally as text. When you use MariaDB JSON functions, the syntax is automatically checked.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    properties JSON
);

The properties column can store any JSON object.

Step 2: Insert JSON data

Use MariaDB INSERT INTO to add records containing valid JSON:

INSERT INTO products (name, properties) VALUES (
    'desk',
    '{"color": "brown", "material": "timber", "weight": 12.5}'
);

Format the JSON string correctly and enclose it in quotes. Escape any double quotes within the string with a backslash (\”).

Step 3: Read data from a JSON column

To extract a specific value from a JSON object, use JSON_VALUE():

SELECT name, JSON_VALUE(properties, '$.material') AS material
FROM products;

This returns the product name and the material value from the JSON field.

Step 4: Update JSON data in MariaDB

To update a value inside a JSON object, use JSON_SET():

UPDATE products
SET properties = JSON_SET(properties, '$.color', 'white')
WHERE name = 'desk';

You can also add new key-value pairs if they do not already exist.

Step 5: Remove an element from JSON

Use JSON_REMOVE() to delete a value from a JSON object:

UPDATE products
SET properties = JSON_REMOVE(properties, '$.weight')
WHERE name = 'desk';

Step 6: Search for values in JSON

You can filter entries with specific JSON values:

SELECT * FROM products
WHERE JSON_VALUE(properties, '$.material') = 'timber';

This is useful for complex filters, for example in product searches or personalized content.

Step 7: Validate and debug JSON

Check if a column contains valid JSON with JSON_VALID():

SELECT name, JSON_VALID(properties) FROM products;

A return value of “1” means the JSON is valid. A return value of “0” means there are syntax errors.

Step 8: Use generated columns and indexes with JSON

MariaDB stores JSON as text. Each query must parse the text, which can impact performance on large tables or frequent queries. Generated columns solve this problem by extracting values from JSON into separate, indexed columns. For example, you can extract the material value from the properties column and store it in a new, indexable column:

ALTER TABLE products
ADD COLUMN material VARCHAR(100) AS (JSON_VALUE(properties, '$.material')) STORED,
ADD INDEX idx_material (material);

This command creates the column material, which is automatically populated from $.material. The STORED keyword saves the value in the table instead of calculating it at query time. The index (idx_material) also makes searches more efficient.

You can now perform significantly faster queries like this:

SELECT name FROM products WHERE material = 'timber';

This technique is particularly useful in production environments with a high number of read operations. It reduces the amount of processing the server must perform during each query. By avoiding repeated parsing of JSON data, it helps maintain fast response times even when working with large datasets.

Why use JSON in MariaDB?

JSON is JSON is a good choice when you need to store data with a flexible structure. Common examples include configurations, API responses, user profiles or logs. Because JSON supports nested and variable content in a single column, you do not need to create a new column for every possible field. Instead, you can expand the JSON structure whenever new data needs to be stored. This flexibility makes JSON in MariaDB especially practical for systems that combine fixed table structures with data that changes frequently. It lets you store dynamic content without losing the advantages of classic SQL queries. In this way, you can take advantage of both the reliability of relational databases and the adaptability of NoSQL.