mysql(5.6及以下)解析json的方法实例详解
程序员文章站
2022-04-06 09:53:05
mysql(5.6及以下)解析json
#json解析函数
delimiter $$
drop function if exists `json_ex...
mysql(5.6及以下)解析json
#json解析函数 delimiter $$ drop function if exists `json_extract_c`$$ create function `json_extract_c`( details text, required_field varchar (255) ) returns text charset latin1 begin set details = substring_index(details, "{", -1); set details = substring_index(details, "}", 1); return trim( both '"' from substring_index( substring_index( substring_index( details, concat( '"', substring_index(required_field,'$.', -1), '":' ), -1 ), ',"', 1 ), ':', -1 ) ) ; end$$ delimiter ; example: select json_extract_c(json, "$.totaldays"), json from item limit 100;
自测
create table `json_test` ( `id` int(11) default null, `person_desc` text collate utf8mb4_unicode_ci ) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci; insert into json_test values(2,'{"firstname":"sergei","lastname":"rachmaninoff","instrument":"piano"}'); select id,json_extract_c(person_desc,'$.lastname') as "keys" from json_test;
改进,再找不到key,返回''值,之前的是在找不到的情况下,查找到第一的值。
create definer=`zhangfen`@`%` function `json_extract_c`( details text, required_field varchar (255) ) returns text charset latin1 begin set details = substring_index(details, "{", -1); set details = substring_index(details, "}", 1); return trim( both '"' from substring_index( substring_index( substring_index( concat('"":"",',details), concat( '"', substring_index(required_field,'$.', -1), '":' ), -1 ), ',"', 1 ), ':', -1 ) ) ; end
总结
以上所述是小编给大家介绍的mysql(5.6及以下)解析json的方法实例详解,希望对大家有所帮助
下一篇: JS正则替换去空格的方法