mysql(5.6及以下)解析json

转自：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

巴特西

mysql(5.6及以下)解析json

最新文章

热门文章