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 latin1BEGINSET 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;

CREATE DEFINER=`zhangfen`@`%` FUNCTION `json_extract_c`(details TEXT,required_field VARCHAR (255)) RETURNS text CHARSET latin1BEGINSET 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