mysql自定义函数，获取Json列中json数组某个属性的值

原创已于 2022-12-15 09:30:47 修改 · 1.7k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

mysql

于 2022-12-14 20:14:39 首次发布

本文介绍了一个MySQL函数，用于从JSON格式的数据中精确提取特定值。通过示例展示了如何定位并获取复杂JSON结构中的数据，包括使用JSON_SEARCH确定值的位置、JSON_EXTRACT获取属性值等步骤。

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("王五")

标签

#mysql #json #java