mysql json 方法总结

JSON数据提取

以下是MySQL中JSON数据提取的常用方式及详细说明，按使用场景分类整理：

一、基本字段提取‌

点号操作符（->）‌

 json_col->'$.key' FROM table; -- 提取JSON对象中的值（保留JSON格式）

适用于‌固定路径的顶级字段‌提取，返回结果仍为JSON类型
示例：msg_content->'$.instanceId'等价于JSON_EXTRACT(msg_content, '$.instanceId')

双箭头操作符（->>）‌

SELECT json_col->>'$.key' FROM table; -- 返回纯文本（自动去除JSON引号）

适用于需要直接使用字符串的场景（如WHERE条件比较）

JSON_EXTRACT函数‌

SELECT JSON_EXTRACT(json_col, '$.nested.key') FROM table;

支持‌嵌套路径‌（如$.data.id）及复杂表达式
可链式调用：JSON_EXTRACT(JSON_EXTRACT(json_col, '$.data'), '$.id')
二、JSON数组处理‌

提取数组元素‌

SELECT json_col->'$[1].name' FROM table; -- 获取数组第一个元素 

SELECT JSON_EXTRACT(json_col, '$[1].name') FROM table; -- 提取嵌套数组元素属性

 

使用索引访问数组元素（从0开始）

转换为关系型数据‌

sql

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_col, '$[*].name')) FROM table;

$[*]表示遍历所有数组元素
三、返回类型处理‌
JSON_UNQUOTE函数‌

SELECT JSON_UNQUOTE(json_col->'$.key') FROM table; -- 手动去除字符串引号
 
与JSON_EXTRACT配合使用，效果等同于->>操作符

 

四、方法差异与优化‌

方法‌	返回值类型	是否保留双引号	最低版本‌	测试
->	json格式	保留双引号	MySQL 5.7+	col->'$.key' 等价于 JSON_EXTRACT(col, '$.key')
->>	纯文本	自动去除引号	MySQL 5.7+	col->>'$.key' 等价于  JSON_UNQUOTE(JSON_EXTRACT(col,'$.key'))
JSON_EXTRACT	json格式	保留双引号	全版本	基础函数，  支持动态路径‌：仅JSON_EXTRACT支持变量路径（如CONCAT('$.', var_name)）

示例对比‌

-- 提取嵌套字段（需函数嵌套）
SELECT JSON_EXTRACT(JSON_EXTRACT(json_col, '$.data'), '$.id') AS id; -- 等价于
SELECT json_col->'$.data.id' AS id; -- MySQL 5.7+支持简写:ml-citation{ref="7" data="citationList"}

-- 提取并格式化字符串
SELECT json_col->>'$.name' AS name; -- 直接返回"tom"而非"\"tom\"":ml-citation{ref="7" data="citationList"}

 

注意事项‌
1. 路径合法性校验‌
　　路径不存在时返回NULL，不会报错
2. 性能优化‌
　　直接使用->比多次调用JSON_EXTRACT更高效
3. 动态路径‌
　　若字段路径需动态生成（如变量传入），必须使用JSON_EXTRACT函数