CREATE TABLE users (
	id INT PRIMARY KEY auto_increment,
	user_id INT NOT NULL UNIQUE,
	NAME VARCHAR ( 32 ),
	details JSON NOT NULL COMMENT '用戶詳情信息',
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

INSERT INTO users (user_id,NAME,details) VALUES (1001,'張三','{
    "age": 30,
    "sex": 1,
    "email": "zhangsan@example.com",
		"hobbies": ["coding", "reading"],
    "address": {
        "city": "北京",
        "zip": "10001"
    },
    "role": [
        {
            "roleCode": "guest",
            "roleName": "訪客人員"
        },
        {
            "roleCode": "tester",
            "roleName": "測(cè)試人員"
        }
    ]
}
');

INSERT INTO users (user_id,NAME,details) VALUES (1002,'李四','{
    "age": 25,
    "sex": 2,
    "email": "lisi@example.com",
		"hobbies": ["coding", "reading"],
    "address": {
        "city": "上海",
        "zip": "90001"
    },
    "role": [
        {
            "roleCode": "system",
            "roleName": "系統(tǒng)管理員"
        },
        {
            "roleCode": "developer",
            "roleName": "系統(tǒng)開發(fā)人員"
        }
    ]
	}
');

-- 構(gòu)造一個(gè) JSON 對(duì)象
SELECT JSON_OBJECT('name', 'John Doe', 'age', 30) AS person;

-- 構(gòu)造一個(gè) JSON 數(shù)組
SELECT JSON_ARRAY('apple', 'banana', 'cherry') AS fruits;

JSON_EXTRACT(json_doc, path[, path] ...)

-- 提取 JSON 字段中的 "email"
SELECT JSON_EXTRACT(details, '$.email') AS email FROM users;

-- 提取JSON字段的“email”和“age”字段，結(jié)果以數(shù)組形式返回
SELECT JSON_EXTRACT(details,'$.email','$.age')  FROM users;

-- 獲取JSON數(shù)組的SQL
SELECT JSON_EXTRACT(details,'$.hobbies[0]') AS name FROM users;

-- 查詢嵌套字段 "details.location.city"
SELECT JSON_EXTRACT(details, '$.address.city') AS city FROM users;

-- 查詢 JSON 數(shù)據(jù)中 "age" 大于 25 的記錄
SELECT * FROM users WHERE JSON_EXTRACT(details, '$.age') > 25;

-- 查詢 JSON 數(shù)據(jù)中 "email" 為 "zhangsan@example.com" 的記錄
SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$.email')) = 'zhangsan@example.com';

-- 更新張三的城市為shandong
UPDATE users SET details = JSON_SET(details, '$.address.city', '山東') WHERE name = '張三';

-- 刪除張三的地址信息
UPDATE users SET details = JSON_REMOVE(details, '$.address') WHERE name = '張三';

-- 原始數(shù)據(jù)
SET @user = '{
  "age": 30,
  "sex": 1,
  "email": "zhangsan@example.com",
  "hobbies": ["coding", "reading"],
  "address": {
    "city": "北京",
    "zip": "10001"
  },
  "role": [
    {
      "roleCode": "guest",
      "roleName": "訪客人員"
    },
    {
      "roleCode": "tester",
      "roleName": "測(cè)試人員"
    }
  ]
}';

-- 從JSON對(duì)象中取出單個(gè)值（帶引號(hào)）
SELECT JSON_EXTRACT(@user, '$.address.city');
SELECT JSON_EXTRACT(@user, '$.email');

-- 從JSON數(shù)組取出單個(gè)值
SELECT JSON_EXTRACT(@user, '$.hobbies[0]');
SELECT JSON_EXTRACT(@user, '$.role[0]');

-- 提取名字（帶雙引號(hào)）
SELECT name, details->'$.email' FROM users;

-- 提取名字（純文本，推薦）
SELECT name, details->>'$.email' FROM users;

-- 原始數(shù)據(jù)
SET @user = '{
    "id": 14,
    "name": "張三",
    "age": 30
}';

-- 僅在 email 不存在時(shí)添加
SELECT json_insert(@user, '$.email', 'test@example.com');

-- 存在則更新字段
SELECT JSON_SET(@user, '$.age', 35);
-- 不存在則新增字段
SELECT JSON_SET(@user, '$.gender', 'male');

-- 如果目標(biāo)路徑中已經(jīng)存在值，那么會(huì)將其替換為新的值
SELECT json_replace(@user, '$.age', 35);
SELECT json_replace(@user, '$.gender', 'male');

-- 對(duì)于JSON對(duì)象，只需指定要?jiǎng)h除的鍵即可
SELECT json_remove(@user, '$.age');

-- 原始數(shù)據(jù)
SET @user = '{
    "id": 14,
    "name": "張三",
    "age": 30,
    "address": {"city": "北京", "zip": "100000"},
    "hobbies": ["coding", "籃球"]
}';

SELECT JSON_TYPE(@user);

SELECT JSON_TYPE(JSON_EXTRACT(@user,'$.age'));    -- INTEGER
SELECT JSON_TYPE(JSON_EXTRACT(@user,'$.name'));  -- STRING
SELECT JSON_TYPE(JSON_EXTRACT(@user,'$.hobbies')); -- ARRAY

SELECT JSON_VALID('{"name": "test"}');
SELECT JSON_VALID('{name: test}');
SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');

-- 判斷 hobbies 中是否包含 "coding"（注意值需要用 JSON 字符串格式 '"coding"'）
SELECT json_contains('{"name":"李四","hobbies":["coding","reading"]}', '"coding"', '$.hobbies');

-- 搜索值為 "張三" 的路徑（返回第一個(gè)匹配）
SELECT json_search('{"name": "張三", "age": 25, "city": "北京"}', 'one', '張三');
-- 搜索包含 "海" 的值（使用通配符）
SELECT json_search('[{"name":"張三","age":25,"city":"北京"},{"name":"李四","age":30,"city":"上海"}]', 'one', '%海%')
-- 搜索所有包含 "張" 的值
SELECT json_search('[{"name":"張三","age":25,"city":"北京"},{"name":"李四","age":30,"city":"上海"}]', 'all', '%張%')
-- 指定搜索路徑：只在 name 字段中搜索
SELECT json_search('{"name": "張三", "age": 25, "city": "北京"}', 'one', '%三%', NULL, '$.name')

-- 輸出結(jié)果：[1, 2, true, false, "a", "b"]
SELECT json_merge_preserve('[1, 2]', '[true, false]','["a","b"]');
-- 輸出結(jié)果：[1, 2, {"key1": "value1"}]
SELECT json_merge_preserve('[1, 2]', '{"key1": "value1"}');

-- 輸出結(jié)果：{"key1": "value1", "key2": "value2"}
SELECT json_merge_preserve('{"key1": "value1"}','{"key2": "value2"}');

-- 輸出結(jié)果：{"key1": ["value1", "value3"], "key2": "value2"}
SELECT json_merge_preserve('{"key1": "value1"}','{"key2": "value2","key1":"value3"}') duplicate_key1;

-- 輸出結(jié)果：{"key1": ["value1", "value3", null], "key2": "value2"}
SELECT json_merge_preserve('{"key1": "value1"}','{"key2": "value2","key1":"value3"}','{"key1": null}') last_value_is_null;

-- 輸出結(jié)果：["a", "b"]
SELECT json_merge_patch('[1, 2]', '[true, false]','["a","b"]');
-- 輸出結(jié)果：{"key1": "value1"}
SELECT json_merge_patch('[1, 2]', '{"key1": "value1"}');
-- 輸出結(jié)果：[1, 2]
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2"}','[1, 2]');

-- 輸出結(jié)果：{"key1": "value1", "key2": "value2"}
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2"}');
-- 輸出結(jié)果：{"key1": "value3", "key2": "value2"}
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2","key1":"value3"}') duplicate_key;
-- 輸出結(jié)果：{"key2": "value2"}
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2","key1":"value3"}','{"key1": null}') last_value_is_null;

SELECT JSON_ARRAYAGG(name) AS names 
FROM ( SELECT JSON_EXTRACT(details, '$.name') AS name FROM users) AS subquery;

-- 獲取數(shù)組的第二、第三和第四個(gè)元素
SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');

SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[*]');

SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');

ALTER TABLE users ADD COLUMN email VARCHAR(255) GENERATED ALWAYS AS (details->>'$.email') STORED;
CREATE INDEX idx_email ON users (email);

ALTER TABLE users ADD FULLTEXT(details);
SELECT * FROM users WHERE MATCH(details) AGAINST('shanghai');