MariaDB 的 JSON 函数有哪些？如何使用 JSON 数据类型？

MariaDB 的 JSON 函数提供了强大的 JSON 数据处理能力，从 10.2 版本开始引入，并在后续版本中不断增强。以下是主要的 JSON 函数和使用方法：

1. 创建 JSON 数据

sql
-- 创建 JSON 对象
SELECT JSON_OBJECT('name', 'John', 'age', 30, 'city', 'New York');

-- 创建 JSON 数组
SELECT JSON_ARRAY('apple', 'banana', 'orange');

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

2. 查询 JSON 数据

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

-- 使用 -> 操作符（简写）
SELECT data->'$.name' AS name FROM users;

-- JSON_VALUE：提取标量值
SELECT JSON_VALUE(
    '{"name": "John", "age": 30}',
    '$.name'
);

-- JSON_QUERY：提取 JSON 对象或数组
SELECT JSON_QUERY(
    '{"name": "John", "address": {"city": "New York"}}',
    '$.address'
);

-- JSON_KEYS：获取所有键
SELECT JSON_KEYS('{"name": "John", "age": 30, "city": "New York"}');

3. 修改 JSON 数据

sql
-- JSON_SET：设置值（如果存在则更新，不存在则插入）
SELECT JSON_SET(
    '{"name": "John", "age": 30}',
    '$.age', 31,
    '$.city', 'Boston'
);

-- JSON_INSERT：插入值（仅当不存在时）
SELECT JSON_INSERT(
    '{"name": "John", "age": 30}',
    '$.city', 'Boston'
);

-- JSON_REPLACE：替换值（仅当存在时）
SELECT JSON_REPLACE(
    '{"name": "John", "age": 30}',
    '$.age', 31
);

-- JSON_REMOVE：删除值
SELECT JSON_REMOVE(
    '{"name": "John", "age": 30, "city": "New York"}',
    '$.city'
);

-- JSON_ARRAY_APPEND：追加到数组
SELECT JSON_ARRAY_APPEND(
    '{"fruits": ["apple", "banana"]}',
    '$.fruits', 'orange'
);

-- JSON_ARRAY_INSERT：插入到数组
SELECT JSON_ARRAY_INSERT(
    '{"fruits": ["apple", "orange"]}',
    '$.fruits[1]', 'banana'
);

4. JSON 搜索和过滤

sql
-- JSON_CONTAINS：检查是否包含值
SELECT JSON_CONTAINS(
    '{"fruits": ["apple", "banana", "orange"]}',
    '"banana"',
    '$.fruits'
);

-- JSON_CONTAINS_PATH：检查路径是否存在
SELECT JSON_CONTAINS_PATH(
    '{"name": "John", "address": {"city": "New York"}}',
    'one',
    '$.name', '$.address.city'
);

-- JSON_SEARCH：搜索值
SELECT JSON_SEARCH(
    '{"items": [{"name": "apple"}, {"name": "banana"}]}',
    'one',
    'banana'
);

5. JSON 实用函数

sql
-- JSON_LENGTH：获取长度
SELECT JSON_LENGTH('{"name": "John", "age": 30}');
SELECT JSON_LENGTH('[1, 2, 3, 4, 5]');

-- JSON_DEPTH：获取深度
SELECT JSON_DEPTH('{"name": "John", "address": {"city": "New York"}}');

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

-- JSON_PRETTY：格式化 JSON
SELECT JSON_PRETTY('{"name":"John","age":30}');

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

6. 在表中使用 JSON

sql
-- 创建包含 JSON 列的表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- 查询 JSON 数据
SELECT 
    name,
    attributes->'$.brand' AS brand,
    attributes->'$.specs.ram' AS ram
FROM products;

-- 更新 JSON 数据
UPDATE products
SET attributes = JSON_SET(attributes, '$.specs.ram', '32GB')
WHERE id = 1;

-- 在 JSON 列上创建索引（MariaDB 10.3+）
CREATE INDEX idx_brand ON products((attributes->'$.brand'));

-- 使用 JSON 索引查询
SELECT * FROM products WHERE attributes->'$.brand' = 'Dell';

7. 实际应用场景

存储灵活的产品属性

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'));

-- 查询特定属性
SELECT name, attributes->'$.brand' AS brand FROM products;

存储用户配置

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 users
SET settings = JSON_SET(settings, '$.theme', 'light')
WHERE id = 1;

JSON 函数为 MariaDB 提供了灵活的数据存储和查询能力，特别适合存储半结构化数据和动态属性。