mysql的json解析函数JSON_EXTRACT

mysql的json解析函数JSON_EXTRACT

MYSQl自带的解析函数JSON_EXTRACT,用JSON_EXTRACT函数解析出来的函数会包含双引号

例子
t_table表里面source字段是以json格式写的值为

{ "info" : { "color" : "白色", "inner_color" : "米色", "number" : "12345678", "registration_date" : "2012-11" },
"accessory" : [ "1", "4", "5", "6", "7", "8", "9", "10" ],
"remark" : "测试"}

查询color不去掉双引号，inner_color去掉双引号，remark去掉双引

select 
source->'$.info.color'  as color,
replace(source->'$.info.inner_color','"','')   as inner_color,
replace(source->'$.remark','"','')   as remark
 from t_table

查询结果

color	inner_color	remark
“白色”	米色	测试