mysql(5.6及以下)解析json

转自：https://blog.csdn.net/weixin_33979203/article/details/87621768

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

#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