使用' dw`$$
如果存在,則刪除函數` fn_Json_getKeyValue`$$
CREATE DEFINER = ` data `@ `% ` FUNCTION ` fn _ Json _ get key value `(
In _ JSON數組varchar (4096),# JSON數組字符串
In_Index TINYINT,#JSON對象序列號,從1開始。
In_KeyName VARCHAR(64)#密鑰名
)返回VARCHAR(512)字符集utf8
開始
聲明vs _ return VARCHAR(4096);
聲明vs_JsonArray,vs_Json,vs _ KeyName VARCHAR(4096);
# declare vs _ Json varchar(4096);
聲明vi_pos1,vi_pos2 SMALLINT無符號;
#寫監控日誌
#插入到dw.t_etl_log(sp_name,title,description)
# values(' dw . fn _ Json _ get key value ','通過Json鍵名獲取鍵值',concat ('in _ JSON array = ',in _ Json array));
SET vs _ JSON array = TRIM(in _ JSON array);
SET vs _ KeyName = TRIM(in _ KeyName);
如果vs_JsonArray =“”或vs_JsonArray為空
或者vs_KeyName =“”或vs_KeyName為空
或者in _ Index & lt= 0或in_Index為空,則
SET vs _ return = NULL
其他
#去掉方括號
SET vs _ JSON array = REPLACE(REPLACE(vs _ JSON array,'[',''),']',' ');
#獲取指定的JSON對象
SET vs _ JSON = SUBSTRING _ INDEX(SUBSTRING _ INDEX(vs _ JSON array,' } ',in_index),' } ',-1);
如果vs_json = ' '或vs_json為空,則
SET vs _ return = NULL
其他
SET vs_KeyName = CONCAT(' " ',vs_KeyName,' ":');
SET vi_pos1 = INSTR(vs_json,vs _ KeyName);
IF VI _ pos 1 & gt;那麽0
#如果鍵名存在
SET VI _ pos 1 = VI _ pos 1+CHAR _ LENGTH(vs _ KeyName);
SET vi_pos2 = LOCATE(',',vs_json,VI _ pos 1);
如果vi_pos2 = 0,則
#最後壹個元素沒有“,”分隔符,也沒有終止符“}”
SET VI _ pos 2 = CHAR _ LENGTH(vs _ JSON)+1;
結束IF;
SET vs _ return = REPLACE(MID(vs _ JSON,vi_pos1,vi_pos2 - vi_pos1),'"',' ');
結束IF;
結束IF;
結束IF;
RETURN(vs _ RETURN);
結束$$
分隔符;
測試:{"old _ current _ score": "2 "," new _ current _ score": "0 "," old _ grade _ id": "1 "," new _ grade _ id": "1 "," grade "。
select fn _ Json _ get key value(reason,1,' old _ grade _ id ');