java mysql5.6解析json函数(JSON

需要创建以下两个函数 主函数: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

好文链接

 您阅读本篇文章共花了：