如何解决5.5版本mysql下，由json串组成的数组取值问题。

Question

问题详细场景如下：A表中有字段a，a字段类型如[{"PROCESS_ID":"e03e4049-aea2-435e-b5a0-1e808cd5b4ad","PROCESS_VALUE":1.0,"STANDARD_MH":2.00,"IsSelected":true},{"PROCESS_ID":"44742e69-5cb3-4444-a1f9-a833ee945a2d","PROCESS_VALUE":1.0,"STANDARD_MH":1.50,"IsSelected":true},{"PROCESS_ID":"a5c7b02e-2667-4704-9c50-a768f8788a3f","PROCESS_VALUE":1.0,"STANDARD_MH":1.50,"IsSelected":true},{"PROCESS_ID":"7270b3ba-c769-4813-837a-74d550459b86","PROCESS_VALUE":1.0,"STANDARD_MH":3.00,"IsSelected":true},{"PROCESS_ID":"986bac02-0765-40ae-a4fa-173911dffc8b","PROCESS_VALUE":1.0,"STANDARD_MH":1.60,"IsSelected":true},{"PROCESS_ID":"36619e86-5e38-45a5-8167-33bbb6244508","PROCESS_VALUE":1.0,"STANDARD_MH":2.00,"IsSelected":true}]
a字段由若干个json串组成，数量未知。现要计算得到sum(PROCESS_VALUE*STANDARD_MH)，自己捣鼓了很久了，没有解决，求大神帮忙解决下，最好能写个自定义函数，多谢！

Answer 1

Answer 2

你这种数据结构就不应该这样存，可以重新建立张表专门存json数据，和A表的记录关联起来。

Answer 3

这个数据结构不是我能决定的，大神们有没有什么解决方法

Answer 4

可以尝试使用mysql5.7版本了，它具有json格式的数据，效率很快，我们正在考虑mysql升级，博主也可以考虑下

Answer 5

create FUNCTION fn_json_array (proc_json_array varchar(255),proc_key varchar(255),MH_key varchar(255))
BEGIN
#声明变量
DECLARE ANS VARCHAR(255) DEFAULT 0;
DECLARE key1 VARCHAR(255);
DECLARE key2 VARCHAR(255);

#判断传入的参数是否为空
IF(length(proc_json_array) = 0) THEN
SET ANS = 0;
ELSE
SET key1 = substr(proc_json_array,2,length(proc_json_array)-2);

#循环计算结果
REPEAT
SET key2 = substr(key1,1,locate('}',key1));
SET key1 = substr(key1,(length(key2)+2),(length(key1)-length(key2)));
IF(length(key2)=110)
THEN
SET ANS = ANS + substr(key2,70,3)*substr(key2,88,4);
ELSE
SET ANS = 0;
END IF;
UNTIL length(key1) = 0;
END REPEAT;
END IF;

RETURN ANS;
END

以上是我的逻辑，应该是可以解出来的，由于基本没写过自定义函数，对于这个的语法不了解，大牛们帮忙看看这个语法有没有问题，多谢了！

Answer 6

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION simpleFun2 (jsontext TEXT,strValue VARCHAR(40),strMh VARCHAR(40)) RETURNS DECIMAL(12,2)
BEGIN
DECLARE key1 TEXT;
DECLARE key2 TEXT;
DECLARE strIndex INT;
DECLARE decValue DECIMAL(12,2);
DECLARE decMh DECIMAL(12,2);
DECLARE decSum DECIMAL(12,2);

SET strIndex = LOCATE(strValue, jsontext);
SET key1 = SUBSTR(jsontext, strIndex + LENGTH(strValue)+2);     

SET strIndex = LOCATE(',', key1);
SET key2 = SUBSTR(key1, 1,strIndex-1);      

SET decValue = CONVERT (key2 , DECIMAL(12,2)) ;
INSERT INTO ecx_test1 (text1) VALUES(decValue); 

SET strIndex = LOCATE(strMh, key1);
SET key1 = SUBSTR(key1, strIndex + LENGTH(strMh)+2);

SET strIndex = LOCATE(',', key1);
SET key2 = SUBSTR(key1, 1,strIndex-1);

INSERT INTO ecx_test1 (text1) VALUES(key2);
SET decMh = CONVERT (key2 , DECIMAL(12,2)) ;    

SET decSum = decValue * decMh;  

RETURN decSum;
END $$

DELIMITER ;

Answer 7

SELECT simpleFun(TEXT,'PROCESS_VALUE','STANDARD_MH') FROM ecx_test

Answer 8

Answer 9

Answer 10