MariaDB JSON函数 #

-- MariaDB 10.2+ 支持原生JSON类型
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

-- JSON类型特点
-- 1. 自动验证JSON格式
-- 2. 更高效的存储
-- 3. 支持索引

-- JSON对象
{"name": "John", "age": 30, "active": true}

-- JSON数组
[1, 2, 3, "a", "b", "c"]

-- 嵌套JSON
{
    "user": {
        "name": "John",
        "email": "john@example.com"
    },
    "orders": [
        {"id": 1, "amount": 100},
        {"id": 2, "amount": 200}
    ]
}

-- 创建JSON对象
SELECT JSON_OBJECT('name', 'John', 'age', 30, 'active', TRUE);
-- {"name": "John", "age": 30, "active": true}

-- 嵌套对象
SELECT JSON_OBJECT(
    'user', JSON_OBJECT('name', 'John', 'email', 'john@example.com'),
    'status', 'active'
);
-- {"user": {"name": "John", "email": "john@example.com"}, "status": "active"}

-- 创建JSON数组
SELECT JSON_ARRAY(1, 2, 3, 'a', 'b', 'c');
-- [1, 2, 3, "a", "b", "c"]

-- 嵌套数组
SELECT JSON_ARRAY(
    JSON_OBJECT('id', 1, 'name', 'John'),
    JSON_OBJECT('id', 2, 'name', 'Jane')
);
-- [{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]

-- 引用JSON字符串
SELECT JSON_QUOTE('Hello "World"');
-- "Hello \"World\""

-- 提取JSON值
SET @json = '{"name": "John", "age": 30, "skills": ["MySQL", "Python"]}';

SELECT JSON_EXTRACT(@json, '$.name');      -- "John"
SELECT JSON_EXTRACT(@json, '$.age');       -- 30
SELECT JSON_EXTRACT(@json, '$.skills[0]'); -- "MySQL"

-- 路径语法
-- $: 根元素
-- $.key: 对象属性
-- $[index]: 数组索引
-- $[*]: 所有数组元素
-- $.**.key: 递归查找

-- -> 操作符（返回JSON）
SELECT @json->'$.name';      -- "John"
SELECT @json->'$.skills[0]'; -- "MySQL"

-- ->> 操作符（返回文本，去掉引号）
SELECT @json->>'$.name';      -- John
SELECT @json->>'$.skills[0]'; -- MySQL

-- MariaDB 10.6+ 提取标量值
SELECT JSON_VALUE(@json, '$.name');  -- John
SELECT JSON_VALUE(@json, '$.age');   -- 30

-- 获取JSON对象的键
SELECT JSON_KEYS(@json);
-- ["name", "age", "skills"]

-- 获取嵌套对象的键
SELECT JSON_KEYS(@json, '$.skills');
-- NULL（skills是数组）

-- 获取JSON长度
SELECT JSON_LENGTH(@json);           -- 3（对象有3个属性）
SELECT JSON_LENGTH(@json->'$.skills'); -- 2（数组有2个元素）

-- 获取JSON类型
SELECT JSON_TYPE(@json);            -- OBJECT
SELECT JSON_TYPE(@json->'$.skills'); -- ARRAY
SELECT JSON_TYPE(@json->'$.age');   -- INTEGER

-- 设置JSON值（存在则更新，不存在则添加）
SET @json = '{"name": "John", "age": 30}';

SELECT JSON_SET(@json, '$.age', 31);
-- {"name": "John", "age": 31}

SELECT JSON_SET(@json, '$.email', 'john@example.com');
-- {"name": "John", "age": 30, "email": "john@example.com"}

-- 设置嵌套值
SELECT JSON_SET(@json, '$.address.city', 'New York');
-- {"name": "John", "age": 30, "address": {"city": "New York"}}

-- 插入JSON值（只添加，不更新）
SELECT JSON_INSERT(@json, '$.age', 31);
-- {"name": "John", "age": 30}（age已存在，不更新）

SELECT JSON_INSERT(@json, '$.email', 'john@example.com');
-- {"name": "John", "age": 30, "email": "john@example.com"}

-- 替换JSON值（只更新，不添加）
SELECT JSON_REPLACE(@json, '$.age', 31);
-- {"name": "John", "age": 31}

SELECT JSON_REPLACE(@json, '$.email', 'john@example.com');
-- {"name": "John", "age": 30}（email不存在，不添加）

-- 删除JSON值
SELECT JSON_REMOVE(@json, '$.age');
-- {"name": "John"}

-- 删除数组元素
SET @arr = '[1, 2, 3, 4, 5]';
SELECT JSON_REMOVE(@arr, '$[1]');
-- [1, 3, 4, 5]

-- 合并JSON（MariaDB 10.2+）
SELECT JSON_MERGE(
    '{"a": 1}',
    '{"b": 2}'
);
-- {"a": 1, "b": 2}

-- JSON_MERGE_PRESERVE（保留重复键）
SELECT JSON_MERGE_PRESERVE(
    '{"a": 1, "b": 2}',
    '{"b": 3, "c": 4}'
);
-- {"a": 1, "b": [2, 3], "c": 4}

-- JSON_MERGE_PATCH（覆盖重复键）
SELECT JSON_MERGE_PATCH(
    '{"a": 1, "b": 2}',
    '{"b": 3, "c": 4}'
);
-- {"a": 1, "b": 3, "c": 4}

-- 追加数组元素
SET @arr = '[1, 2, 3]';
SELECT JSON_ARRAY_APPEND(@arr, '$', 4);
-- [1, 2, 3, 4]

-- 追加到对象中的数组
SET @json = '{"skills": ["MySQL", "Python"]}';
SELECT JSON_ARRAY_APPEND(@json, '$.skills', 'Java');
-- {"skills": ["MySQL", "Python", "Java"]}

-- 在指定位置插入数组元素
SET @arr = '[1, 2, 3]';
SELECT JSON_ARRAY_INSERT(@arr, '$[1]', 10);
-- [1, 10, 2, 3]

-- 检查JSON是否包含指定值
SET @json = '{"skills": ["MySQL", "Python"]}';

SELECT JSON_CONTAINS(@json, '"MySQL"', '$.skills');
-- 1（true）

SELECT JSON_CONTAINS(@json, '"Java"', '$.skills');
-- 0（false）

-- 检查JSON是否包含指定路径
SELECT JSON_CONTAINS_PATH(@json, 'one', '$.name');
-- 0

SELECT JSON_CONTAINS_PATH(@json, 'one', '$.skills');
-- 1

-- 'one': 任一路径存在
-- 'all': 所有路径存在
SELECT JSON_CONTAINS_PATH(@json, 'all', '$.skills', '$.name');
-- 0

-- 搜索JSON中的值
SET @json = '{"name": "John", "skills": ["MySQL", "Python"]}';

SELECT JSON_SEARCH(@json, 'one', 'MySQL');
-- "$.skills[0]"

SELECT JSON_SEARCH(@json, 'all', 'John');
-- "$.name"

-- 使用通配符
SELECT JSON_SEARCH(@json, 'one', 'J%');
-- "$.name"

-- 获取JSON路径
SET @json = '{"a": {"b": {"c": 1}}}';

SELECT JSON_EXTRACT(@json, '$.a.b.c');
-- 1

-- 递归查找
SELECT JSON_EXTRACT(@json, '$**.c');
-- [1]

-- MariaDB 10.6+ JSON_TABLE函数
SET @json = '[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]';

SELECT * FROM JSON_TABLE(
    @json,
    '$[*]' COLUMNS(
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) AS jt;

+------+------+
| id   | name |
+------+------+
|    1 | John |
|    2 | Jane |
+------+------+

SET @json = '{
    "users": [
        {"id": 1, "name": "John", "orders": [{"id": 101}, {"id": 102}]},
        {"id": 2, "name": "Jane", "orders": [{"id": 201}]}
    ]
}';

SELECT * FROM JSON_TABLE(
    @json,
    '$.users[*]' COLUMNS(
        user_id INT PATH '$.id',
        user_name VARCHAR(50) PATH '$.name',
        orders JSON PATH '$.orders'
    )
) AS jt;

-- 创建带JSON列的表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

-- 创建虚拟列
ALTER TABLE products
ADD COLUMN brand VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.brand'))) STORED;

-- 创建索引
CREATE INDEX idx_brand ON products(brand);

-- 使用索引查询
SELECT * FROM products WHERE brand = 'Apple';

-- MariaDB 10.6+ 多值索引
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    tags JSON,
    INDEX idx_tags ((CAST(tags AS CHAR(100) ARRAY)))
);

-- 查询
SELECT * FROM products WHERE 'electronics' MEMBER OF(tags);

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    settings JSON
);

-- 插入配置
INSERT INTO user_settings VALUES (
    1,
    JSON_OBJECT(
        'theme', 'dark',
        'language', 'zh-CN',
        'notifications', JSON_OBJECT('email', true, 'push', false)
    )
);

-- 更新配置
UPDATE user_settings 
SET settings = JSON_SET(settings, '$.theme', 'light')
WHERE user_id = 1;

-- 查询配置
SELECT settings->>'$.theme' AS theme FROM user_settings WHERE user_id = 1;

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

-- 插入商品
INSERT INTO products VALUES (
    1,
    'Laptop',
    JSON_OBJECT(
        'brand', 'Dell',
        'cpu', 'Intel i7',
        'ram', '16GB',
        'storage', JSON_ARRAY('256GB SSD', '1TB HDD')
    )
);

-- 查询属性
SELECT 
    name,
    attributes->>'$.brand' AS brand,
    attributes->>'$.cpu' AS cpu,
    JSON_EXTRACT(attributes, '$.storage[0]') AS primary_storage
FROM products;