What are the JSON functions in MariaDB and how to use the JSON data type?

MariaDB's JSON functions provide powerful JSON data processing capabilities, introduced in version 10.2 and continuously enhanced in subsequent versions. Here are the main JSON functions and usage methods:

1. Creating JSON Data

sql
-- Create JSON object
SELECT JSON_OBJECT('name', 'John', 'age', 30, 'city', 'New York');

-- Create JSON array
SELECT JSON_ARRAY('apple', 'banana', 'orange');

-- Merge JSON
SELECT JSON_MERGE(
    JSON_OBJECT('name', 'John'),
    JSON_OBJECT('age', 30, 'city', 'New York')
);

2. Querying JSON Data

sql
-- JSON_EXTRACT: Extract JSON value
SELECT JSON_EXTRACT(
    '{"name": "John", "age": 30, "address": {"city": "New York"}}',
    '$.name'
);

-- Use -> operator (shorthand)
SELECT data->'$.name' AS name FROM users;

-- JSON_VALUE: Extract scalar value
SELECT JSON_VALUE(
    '{"name": "John", "age": 30}',
    '$.name'
);

-- JSON_QUERY: Extract JSON object or array
SELECT JSON_QUERY(
    '{"name": "John", "address": {"city": "New York"}}',
    '$.address'
);

-- JSON_KEYS: Get all keys
SELECT JSON_KEYS('{"name": "John", "age": 30, "city": "New York"}');

3. Modifying JSON Data

sql
-- JSON_SET: Set value (update if exists, insert if not)
SELECT JSON_SET(
    '{"name": "John", "age": 30}',
    '$.age', 31,
    '$.city', 'Boston'
);

-- JSON_INSERT: Insert value (only if not exists)
SELECT JSON_INSERT(
    '{"name": "John", "age": 30}',
    '$.city', 'Boston'
);

-- JSON_REPLACE: Replace value (only if exists)
SELECT JSON_REPLACE(
    '{"name": "John", "age": 30}',
    '$.age', 31
);

-- JSON_REMOVE: Remove value
SELECT JSON_REMOVE(
    '{"name": "John", "age": 30, "city": "New York"}',
    '$.city'
);

-- JSON_ARRAY_APPEND: Append to array
SELECT JSON_ARRAY_APPEND(
    '{"fruits": ["apple", "banana"]}',
    '$.fruits', 'orange'
);

-- JSON_ARRAY_INSERT: Insert into array
SELECT JSON_ARRAY_INSERT(
    '{"fruits": ["apple", "orange"]}',
    '$.fruits[1]', 'banana'
);

4. JSON Search and Filtering

sql
-- JSON_CONTAINS: Check if value is contained
SELECT JSON_CONTAINS(
    '{"fruits": ["apple", "banana", "orange"]}',
    '"banana"',
    '$.fruits'
);

-- JSON_CONTAINS_PATH: Check if path exists
SELECT JSON_CONTAINS_PATH(
    '{"name": "John", "address": {"city": "New York"}}',
    'one',
    '$.name', '$.address.city'
);

-- JSON_SEARCH: Search for value
SELECT JSON_SEARCH(
    '{"items": [{"name": "apple"}, {"name": "banana"}]}',
    'one',
    'banana'
);

5. JSON Utility Functions

sql
-- JSON_LENGTH: Get length
SELECT JSON_LENGTH('{"name": "John", "age": 30}');
SELECT JSON_LENGTH('[1, 2, 3, 4, 5]');

-- JSON_DEPTH: Get depth
SELECT JSON_DEPTH('{"name": "John", "address": {"city": "New York"}}');

-- JSON_VALID: Validate JSON
SELECT JSON_VALID('{"name": "John"}');
SELECT JSON_VALID('invalid json');

-- JSON_PRETTY: Format JSON
SELECT JSON_PRETTY('{"name":"John","age":30}');

-- JSON_COMPACT: Compress JSON
SELECT JSON_COMPACT('{"name": "John", "age": 30}');

6. Using JSON in Tables

sql
-- Create table with JSON column
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON data
INSERT INTO products (name, attributes) VALUES (
    'Laptop',
    JSON_OBJECT('brand', 'Dell', 'specs', JSON_OBJECT('ram', '16GB', 'storage', '512GB SSD'))
);

-- Query JSON data
SELECT 
    name,
    attributes->'$.brand' AS brand,
    attributes->'$.specs.ram' AS ram
FROM products;

-- Update JSON data
UPDATE products
SET attributes = JSON_SET(attributes, '$.specs.ram', '32GB')
WHERE id = 1;

-- Create index on JSON column (MariaDB 10.3+)
CREATE INDEX idx_brand ON products((attributes->'$.brand'));

-- Query using JSON index
SELECT * FROM products WHERE attributes->'$.brand' = 'Dell';

7. Practical Use Cases

Storing Flexible Product Attributes

sql
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products VALUES
(1, 'Laptop', JSON_OBJECT('brand', 'Dell', 'ram', '16GB', 'storage', '512GB')),
(2, 'Phone', JSON_OBJECT('brand', 'Apple', 'model', 'iPhone 15', 'storage', '256GB'));

-- Query specific attributes
SELECT name, attributes->'$.brand' AS brand FROM products;

Storing User Settings

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    settings JSON
);

INSERT INTO users VALUES
(1, 'John', JSON_OBJECT('theme', 'dark', 'notifications', JSON_ARRAY('email', 'sms')));

-- Update settings
UPDATE users
SET settings = JSON_SET(settings, '$.theme', 'light')
WHERE id = 1;

JSON functions provide MariaDB with flexible data storage and query capabilities, especially suitable for storing semi-structured data and dynamic attributes.