快捷導(dǎo)航

Mysql提取JSON對象和數(shù)組的方法示例代碼

更新時間：2025年09月24日 09:07:11 作者：小仙。

在MySQL中,JSON數(shù)據(jù)類型是一種特殊的數(shù)據(jù)類型,它可以存儲JSON格式的數(shù)據(jù),下面這篇文章主要介紹了Mysql提取JSON對象和數(shù)組的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

1. JSON對象

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

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

1.1 提取JSON的key

JSON_KEYS函數(shù)用于提取JSON的key值

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

1.2 提取JSON的值

JSON_EXTRACT函數(shù)用于提取JSON的值

$.*取所有值
$.key取單個值

1.2.1 提取所有key的值

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

1.2.2 提取指定key的值

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

1.2.3 提取指定key的值：去掉引號

JSON_UNQUOTE函數(shù)對單個結(jié)果進(jìn)行去引號操作

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

2. JSON數(shù)組

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

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

2.1 提取JSON數(shù)組的值

$[*] ：JSON數(shù)組所有對象

$[0] ：JSON數(shù)組第一個對象

$[*].key ：JOSN數(shù)組所有對象，對應(yīng)key的值

$[0].key ：JSON數(shù)組第一個對象對應(yīng)key的值

2.1.1 提取所有元素

JSON_TABLE函數(shù)將 JSON 數(shù)組展開成關(guān)系表的形式，方便獲取所有元素。

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

2.1.2 提取所有元素：指定key的值

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

2.1.3 提取所有元素：指定key的值用逗號分隔

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

2.1.4 提取指定索引的元素

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

3. 總結(jié)

-- 定義變量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;

下面附一個具體的例子

假設(shè)你有一個名為users的表，其中有一個名為profile的列，該列存儲了用戶的JSON格式的個人資料：

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;