mysql5.6解析json函数(JSON_EXTRACT函数的替代品)

需要创建以下两个函数 主函数:json_extract_nested 和 辅助函数: json_array_value

---------------------------主函数 json_extract_nested 创建开始-----------------------
CREATE FUNCTION `json_extract_nested`(`_field` text,`_variable` text) RETURNS text CHARSET utf8
BEGIN
                DECLARE X INT DEFAULT 0;
                DECLARE fieldval1 TEXT;
                DECLARE arrayName,arrayValue TEXT;

                SET arrayName = SUBSTRING_INDEX(_variable, '.', 1);

                IF(LOCATE('%',arrayName)> 0) THEN
                    SET _field = SUBSTRING_INDEX(_field, "{", -1);
                    SET _field = SUBSTRING_INDEX(_field, "}", 1);
RETURN TRIM(
        BOTH '"' FROM SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                        SUBSTRING_INDEX(
                                _field,
                                CONCAT(
                                        '"',
                                        SUBSTRING_INDEX(_variable,'$.', - 1),
                                        '":'
                                    ),
                                - 1
                            ),
                        ',"',
                        1
                    ),
                ':',
                -1
            )
    ) ;
ELSE
                    SET arrayValue = json_array_value(_field, arrayName);
                    WHILE X < (LENGTH(_variable) - LENGTH(REPLACE(_variable, '.', ""))) DO
                        IF(LENGTH(_variable) - LENGTH(REPLACE(_variable, '.', ""))>X) THEN
                            SET arrayName = SUBSTRING_INDEX(SUBSTRING_INDEX(_variable, '.', X+2),'.',-1);
END IF;
                        IF(arrayName<>'') THEN
                            SET arrayValue = json_array_value(arrayValue, arrayName);
END IF;
                        SET X = X + 1;
END WHILE;
END IF;
RETURN arrayValue;
END
---------------------------主函数 json_extract_nested 创建结束-----------------------

---------------------------辅助函数 json_array_value 创建开始-----------------------
CREATE FUNCTION `json_array_value`(`_field` text,`arrayName` varchar(255)) RETURNS text CHARSET utf8
BEGIN
                DECLARE arrayValue, arrayValueTillDelimit TEXT;
                DECLARE arrayStartDelimiter, arrayEndDelimiter VARCHAR(10);
                DECLARE arrayCountDelimiter INT;
                DECLARE countBracketLeft, countBracketRight INT DEFAULT 0;
                DECLARE X INT DEFAULT 0;
                DECLARE arrayNameQuoted VARCHAR(255);
                SET arrayNameQuoted = CONCAT('"',arrayName,'"');
                /*check arrayname exist*/
                IF(LOCATE(arrayNameQuoted,_field)= 0) THEN
                    RETURN NULL;
ELSE
                    /*get value behind arrayName1*/
                    SET _field = SUBSTRING(_field,1,LENGTH(_field)-1);
                    SET arrayValue = SUBSTRING(_field, LOCATE(arrayNameQuoted,_field)+LENGTH(arrayNameQuoted)+1, LENGTH(_field));
                    /*get json delimiter*/
                    SET arrayStartDelimiter = LEFT(arrayValue, 1);
                    IF(arrayStartDelimiter='{') THEN
                        SET arrayEndDelimiter = '}';
                        loopBrackets: WHILE X < (LENGTH(arrayValue)) DO
                            SET countBracketLeft = countBracketLeft +IF(SUBSTRING(arrayValue,X,1)=arrayStartDelimiter,1,0);
                        SET countBracketRight = countBracketRight +IF(SUBSTRING(arrayValue,X,1)=arrayEndDelimiter,1,0);
                        IF(countBracketLeft<>0 AND countBracketLeft=countBracketRight) THEN
                            SET arrayCountDelimiter = X;
                            LEAVE loopBrackets;
ELSE
                            SET X = X + 1;
END IF;
END WHILE;
                                ELSEIF(arrayStartDelimiter='[') THEN
                                    SET arrayEndDelimiter = ']';
                                    SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
                                 ELSEIF(arrayStartDelimiter='"') THEN
                                    SET arrayEndDelimiter = '"';
                                    SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
ELSE
                                    SET arrayStartDelimiter = "";
                                    IF((LOCATE(",",arrayValue)> LOCATE("}",arrayValue))) THEN
                        SET arrayEndDelimiter = ",";
ELSE
                        SET arrayEndDelimiter = "}";
END IF;
                        SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
END IF;
                    SET arrayValueTillDelimit = SUBSTRING(arrayValue, 1, arrayCountDelimiter);
                    SET arrayCountDelimiter = LENGTH(arrayValueTillDelimit) - LENGTH(REPLACE(arrayValueTillDelimit, arrayStartDelimiter, ""));
                    SET arrayValue = SUBSTR(arrayValue,LENGTH(arrayStartDelimiter)+1);
                    IF(arrayStartDelimiter='{') THEN
                        SET arrayValue = SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, arrayCountDelimiter);
ELSE
                        SET arrayValue = SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, arrayCountDelimiter+1);
END IF;
RETURN (arrayValue);
END IF;
END
---------------------------辅助函数 json_array_value 创建结束-----------------------
参考链接:How to get values from MySQL(5.6) column if that contains json document as string - Stack Overflow