DELIMITER $$
DROP function IF EXISTS `func_fetch_spec_quantity` $$
CREATE FUNCTION `func_fetch_spec_quantity`
(service_spec varchar(1000),prop_value varchar(1000),prop_name varchar(1000)) RETURNS varchar(10)
BEGIN
declare prop_value_path varchar(50) default '';
declare dest_prop_value varchar(10) default '';
declare prop_name_deep varchar(10) default '';
declare prop_name_path varchar(50) default '';
set prop_value_path = JSON_SEARCH(service_spec,'one',prop_value);
set prop_name_deep = substring_index(JSON_UNQUOTE(prop_value_path),'.',1);
set prop_name_path = concat_ws('.',prop_name_deep,prop_name);
set dest_prop_value = JSON_EXTRACT(service_spec,prop_name_path);
return JSON_UNQUOTE(dest_prop_value);
END$$
eg:
某json列的值为:
[{"code":"123","name":"张三"},{"code":"456","name":"李四"},{"code":"789","name":"王五"}]
要获取json字符串中code="789"的name值
1、通过JSON_SEARCH返回值为789的位置
select JSON_SEARCH('[{"code":"123","name":"张三"},{"code":"456","name":"李四"},{"code":"789","name":"王五"}]','one','789')
2、将结果分割,得到code=789在json字符串中的位置
select substring_index(JSON_UNQUOTE("$[2].code"),'.',1);
3、将位置和name属性做拼接
select concat_ws('.','$[2]','name');
4、获取指定路径属性的值
select JSON_EXTRACT('[{"code":"123","name":"张三"},{"code":"456","name":"李四"},{"code":"789","name":"王五"}]', '$[2].name')
5、结果去掉引号
select JSON_UNQUOTE("王五")