轉自:https://blog.csdn.net/weixin_33979203/article/details/87621768 改進,再找不到key,返回''值,之前的是在找不到的情況下,查找到第一的值。 ...
轉自:https://blog.csdn.net/weixin_33979203/article/details/87621768
#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