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;