mysql(5.6及以下)解析json的方法实例详解

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的方法实例详解,希望对大家有所帮助