Detailed explanation of the method of parsing json by mysql (5.6and below)

mysql(5.6 and below) parse json

#json parsing function 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;

self-test

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;

Improved, no longer find key, return '' value, the previous is in the case of no find, find the first value.

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

summary

The above is a small series to introduce mysql(5.6 and below) to resolve json method examples detailed explanation, hope to help you, if you have any questions please give me a message, small series will reply to you in time. Thank you very much for your support!

If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!