mysql 解析json字符串相关问题

mysql 解析json字符串相关问题

很多时候，我们需要在sql里面直接解析json字符串。这里针对mysql5.7版本的分水岭进行区分。

1、mysql5.7以上版本

使用mysql的内置函数JSON_EXTRACT(column, ‘$.key’)，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。

 select json_extract('[10, 20, [30, 40]]', '$.1');
 结果是：20

对于简单的json字符串肯定是可以解析成功，但是对于嵌套数组的没试过。

2、mysql5.7以下版本

只能充分发挥已有函数的功能去截取实现，无论实现方式是存储过程还是简单的sql语句，其原理都是一样的

2.1、普通json

[{“xxx”:“111”,“zzz”:“222”}]

CREATE DEFINER=`root`@`localhost` FUNCTION `json_extract_c`(details TEXT,
required_field VARCHAR (1000)) RETURNS text CHARSET utf8
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

参数说明： details 传入的json字符串 required_field 需要获取到的字段名

例： SELECT json_extract_c(value,'$.xxx') FROM user WHERE  id= '1'
结果为： 111

将 user表中的 value字段（value中存放的是json格式的数据 [{“xxx”:“111”,“zzz”:“222”}] ）中的 xxx的值提取出来。

---

2.2、jsonArray

[{“xxx”:“111”,“zzz”:“222”},{“xxx”:“333”,“zzz”:“444”},{“xxx”:“555”,“zzz”:“666”}]

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_Json_getKeyValue`(in_JsonArray LONGTEXT character set utf8 ,
in_Index INT, 
in_KeyName VARCHAR(64)) RETURNS varchar(512) CHARSET utf8
BEGIN
DECLARE vs_return LONGTEXT character set utf8 DEFAULT '';
DECLARE vs_Json LONGTEXT character set utf8 DEFAULT '';
DECLARE vs_KeyName LONGTEXT character set utf8 DEFAULT '';
DECLARE vs_JsonArray LONGTEXT character set utf8 DEFAULT '';
DECLARE vi_pos1, vi_pos2 BIGINT;
SET vs_JsonArray = TRIM(in_JsonArray);
SET vs_KeyName = TRIM(in_KeyName);
IF vs_JsonArray = '' OR vs_JsonArray IS NULL
	OR vs_KeyName = '' OR vs_KeyName IS NULL
	OR in_Index <= 0 OR in_Index IS NULL THEN
	SET vs_return = NULL;
ELSE
	
	SET vs_JsonArray = REPLACE(REPLACE(vs_JsonArray, '[', ''), ']', '');
	
	SET vs_json = SUBSTRING_INDEX(SUBSTRING_INDEX(vs_JsonArray,'}', in_index),'}',-1);
	IF vs_json = '' OR vs_json IS NULL THEN
		SET vs_return = NULL;
	ELSE
		SET vs_KeyName = CONCAT('"', vs_KeyName, '":');
		SET vi_pos1 = INSTR(vs_json, vs_KeyName);
		IF vi_pos1 > 0 THEN
		
			SET vi_pos1 = vi_pos1 + CHAR_LENGTH(vs_KeyName);
			SET vi_pos2 = LOCATE(',', vs_json, vi_pos1);
			IF vi_pos2 = 0 THEN
			
				SET vi_pos2 = CHAR_LENGTH(vs_json) + 1;
			END IF;
			SET vs_return = REPLACE(MID(vs_json, vi_pos1, vi_pos2 - vi_pos1), '"', '');
		END IF;
	END IF;
END IF;
RETURN(vs_return);
END

参数说明： in_JsonArray 传入的jsonArray字符串 in_Index 第几个json串 in_KeyName 需要获取到的字段名

例： SELECT fn_Json_getKeyValue(value,2,'xxx') FROM user WHERE  id= '1'
结果为： 333

将 user表中的 value字段（value中存放的是json格式的数据 [{“xxx”:“111”,“zzz”:“222”},{“xxx”:“333”,“zzz”:“444”},{“xxx”:“555”,“zzz”:“666”}] ）中的 xxx的值提取出来。

---

上面用的到相关函数介绍

substring_index(str,delim,count)

      str:要处理的字符串

      delim:分隔符

      count:计数

例子：str=www.wikibt.com

  substring_index(str,'.',1)

  结果是：www

  substring_index(str,'.',2)

  结果是：www.wikibt

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

 substring_index(str,'.',-2)

  结果为：wikibt.com

有人会问，如果我要中间的的wikibt怎么办？

很简单的，两个方向：

从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

substring_index(substring_index(str,'.',-2),'.',1);

replace(str,from_str,to_str)
		str：传入的字符串
		from_str：需要替换的字符串
		to_str：替换的字符串

把object对象中出现的的search全部替换成replace。

实例：
将xxx字符串中的 “我” 替换成 “你”

replace(xxx,'我','你')