{
  "age": 11,
  "name": "張三"
}

-- 定義變量json
set @json:='{"age":11,"name":"張三"}';

-- 提取所有key
SELECT JSON_KEYS(@json) AS `keys`;

-- 提取所有key的值
SELECT JSON_EXTRACT(@json, '$.*') AS `values`;

-- 提取指定key的值
SELECT JSON_EXTRACT(@json, '$."name"') AS extracted_name;

-- 提取指定key的值：去掉引號
SELECT JSON_UNQUOTE(JSON_EXTRACT(@json, '$."name"')) AS extracted_name;

[
    {
        "age": 11,
        "name": "張三"
    },
    {
        "age": 12,
        "name": "李四"
    }
]

-- 定義變量json_array
SET @json_array:='[{"age":11,"name":"張三"},{"age":12,"name":"李四"}]';

-- 提取所有元素：以表格形式展示每個對象的age和name字段
SELECT element.*
FROM 
JSON_TABLE(
    @json_array,
    '$[*]' COLUMNS (
        age INT PATH '$."age"',
        name VARCHAR(255) PATH '$."name"'
    )
) AS element;

-- 提取所有元素：指定key的值
SELECT JSON_UNQUOTE(JSON_EXTRACT(@json_array, '$[*]."name"')) AS extracted_name;

-- 提取所有元素：指定key的值用逗號分隔
SELECT GROUP_CONCAT(names.name SEPARATOR ',') AS combined_names
FROM 
JSON_TABLE(
    @json_array,
    '$[*]' COLUMNS (
        name VARCHAR(255) PATH '$."name"'
    )
) AS names;

-- 提取指定索引的元素
SELECT JSON_EXTRACT(@json_array, '$[0]') AS element_0;
-- 提取指定索引的元素中指定key的值
SELECT JSON_EXTRACT(@json_array, '$[0]."name"') AS name_0;

-- 定義變量json
set @json:='{"age":11,"name":"張三"}';
-- 提取JSON的key
SELECT JSON_KEYS(@json) AS `keys`;
-- 提取所有key的值
SELECT JSON_EXTRACT(@json, '$.*') AS `values`;
-- 提取指定key的值
SELECT JSON_EXTRACT(@json, '$."name"') AS extracted_name;
-- 提取指定key的值：去掉引號
SELECT JSON_UNQUOTE(JSON_EXTRACT(@json, '$."name"')) AS extracted_name;

-- ----------------------------------------------------------------------
-- 定義變量json_array
SET @json_array:='[{"age":11,"name":"張三"},{"age":12,"name":"李四"}]';
-- 提取所有元素：以表格形式展示每個對象的age和name字段
SELECT element.*
FROM 
JSON_TABLE(
    @json_array,
    '$[*]' COLUMNS (
        age INT PATH '$."age"',
        name VARCHAR(255) PATH '$."name"'
    )
) AS element;
-- 提取所有元素：指定key的值
SELECT JSON_UNQUOTE(JSON_EXTRACT(@json_array, '$[*]."name"')) AS extracted_name;
-- 提取所有元素：指定key的值用逗號分隔
SELECT GROUP_CONCAT(names.name SEPARATOR ',') AS combined_names
FROM 
JSON_TABLE(
    @json_array,
    '$[*]' COLUMNS (
        name VARCHAR(255) PATH '$."name"'
    )
) AS names;
-- 提取指定索引的元素
SELECT JSON_EXTRACT(@json_array, '$[0]') AS element_0;
-- 提取指定索引的元素中指定key的值
SELECT JSON_EXTRACT(@json_array, '$[0]."name"') AS name_0;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    profile JSON
);

INSERT INTO users (name, profile) VALUES
('Alice', '{"age": 30, "hobbies": ["reading", "hiking", "coding"]}'),
('Bob', '{"age": 25, "hobbies": ["gaming", "cycling"]}'),
('Carol', '{"age": 28, "hobbies": ["painting", "traveling"]}');

-- 獲取Alice的年齡
SELECT JSON_EXTRACT(profile, '$.age') AS age FROM users WHERE name = 'Alice';

-- 獲取Bob的愛好列表
SELECT JSON_EXTRACT(profile, '$.hobbies') AS hobbies FROM users WHERE name = 'Bob';

-- 給Carol添加一個新的愛好
UPDATE users
SET profile = JSON_INSERT(profile, '$.hobbies[2]', 'swimming')
WHERE name = 'Carol';

-- 移除Alice的愛好列表中的“coding”
UPDATE users
SET profile = JSON_REMOVE(profile, '$.hobbies[2]')
WHERE name = 'Alice';

-- 檢查JSON是否有效
SELECT JSON_VALID(profile) AS is_valid FROM users;