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