How to Use JSON Functions in MariaDB

What is JSON in MariaDB?

MariaDB 10.2+ supports a native JSON data type that stores text validated against JSON rules.Under the hood it is an alias for LONGTEXT, but additional functions treat the value as structured data.

How do I add a JSON column to an existing table?

Add a column with the JSON type so the server rejects invalid documents:

ALTER TABLE Products ADD COLUMN specs JSON;

How do I insert JSON values safely?

Wrap the JSON string in single quotes and ensure valid formatting:

INSERT INTO Products (name, price, stock, specs)
VALUES ('Drone X', 299.99, 25,
'{"weight":1.2, "dimensions":{"l":30,"w":30,"h":10}}');

Which functions read JSON values?

Key helpers include JSON_EXTRACT(), JSON_UNQUOTE(), JSON_SET(), JSON_REMOVE(), and JSON_ARRAYAGG().They let you fetch, update, or aggregate JSON parts without rewriting the entire document.

How do I query nested JSON keys?

Use the $ path syntax inside JSON_EXTRACT():

SELECT id,
JSON_EXTRACT(specs,'$.dimensions.l') AS length_cm
FROM Products
WHERE JSON_EXTRACT(specs,'$.weight') < 2;

Can I index JSON data for speed?

Create a virtual generated column that pulls out the value, then index it:

ALTER TABLE Products
ADD COLUMN weight_kg DECIMAL(5,2)
AS (JSON_UNQUOTE(JSON_EXTRACT(specs,'$.weight'))*1) STORED,
ADD INDEX idx_weight_kg (weight_kg);

Best practices for JSON columns?

Validate JSON on insert, use generated columns for heavy-read keys, keep documents small, and avoid mixing unrelated structures in the same column.

Common mistakes

Using single quotes inside JSON strings

MariaDB expects double quotes around keys and string values.Escape double quotes or use \" when needed.

Forgetting to cast numbers

Arithmetic on JSON_EXTRACT() results fails because the return type is string. Multiply by *1 or use CAST(... AS DECIMAL).

Need a quick recap?

Create a JSON column, insert well-formed documents, query parts with JSON_EXTRACT(), and index via generated columns for speed.

.