转自:
#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
如对本文有疑问, 点击进行留言回复!!
网友评论