GaussDB(DWS)实践系列-函数实现JSON类型解析
GaussDB(DWS)实践-函数实现JSON类型解析
在项目交付中会遇到针对JSON类型解析的场景，例如key值获取value，当前GaussDB(DWS)不支持（需求已规划），针对该场景可参考本文方法实现，实际使用过程中可按照注释内容按需调整。
函数定义：
| DROP FUNCTION IF EXISTS public.jsonpars; CREATE OR REPLACE FUNCTION public.jsonpars( p_str IN TEXT,key_name IN TEXT ) RETURN TEXT IMMUTABLE AS DECLARE p_str_tmp TEXT := '' ; --定义TEXT类型变量：p_str_tmp，缓存读取的json文件去除'['和']'字符 len_p_str_tmp_1 INTEGER := 0 ; --定义INTEGER类型变量：len_p_str_tmp_1，缓存读取的json文件的长度 str_tmp_1 TEXT ; --定义TEXT类型变量：str_tmp_1，缓存读取的json文件replace之后的内容 len_p_str_tmp_2 INTEGER := 0 ; --定义INTEGER类型变量：len_p_str_tmp_2，缓存读取的replace后的json文件的长度 comma_position INTEGER := 0 ; --定义INTEGER类型变量：comma_position，缓存逗号在选定文本字符串中位置 symbol_position INTEGER := 1 ; --定义INTEGER类型变量：symbol_position，缓存指定字符开始匹配起始位置 lbrace_position INTEGER := 0 ; --定义INTEGER类型变量：lbrace_position，缓存左大括号在选定文本字符串中位置 rbrace_position INTEGER := 0 ; --定义INTEGER类型变量：rbrace_position，缓存右大括号在选定文本字符串中位置 str_tmp_key_value TEXT := '' ; --定义TEXT类型变量：str_tmp_key_value，缓存解析出的"key":"value"单一值 TYPE ARRSTR IS VARRAY(1024) OF TEXT ; --定义TEXT类型数组类型：ARRSTR array_value ARRSTR := ARRSTR() ; --定义ARRSTR类型的数组：array_value，缓存解析出的"key":"value"的所有值 array_cnt INTEGER := 1 ; --定义INTEGER类型变量：array_cnt，缓存数组下标变量值，初始值为1，（GaussDB 200的数组下标值从1开始） lbrace_ajust_num INTEGER := 0 ; --定义INTEGER类型变量：lbrace_ajust_num，缓存左大括号在选定文本字符串中个数 rbrace_ajust_num INTEGER := 0 ; --定义INTEGER类型变量：rbrace_ajust_num，缓存右大括号在选定文本字符串中个数 change_position INTEGER := 0 ; --定义INTEGER类型变量：change_position，缓存指定读取右括号次数 array_loop_cnt INTEGER := 1 ; --定义INTEGER类型变量：array_loop_cnt，缓存读取数组下标值 colon_position INTEGER := 0 ; --定义INTEGER类型变量：colon_position，缓存"key":"value"值中的:所在位置 array_key_value TEXT := '' ; --定义TEXT类型变量：array_key_value，缓存从数组中取出的key值 array_value_value TEXT := '' ; --定义TEXT类型变量：array_value_value，缓存从数组中取出的value值 return_value TEXT := '' ; --定义TEXT类型变量：return_value，缓存返回值 BEGIN p_str_tmp := replace( replace( cast( p_str as TEXT ) , '[{' , '{' ) , '}]' , '}' ) ; len_p_str_tmp_1 := LENGTH( p_str_tmp ) ; str_tmp_1 := SUBSTR( p_str_tmp , 2 , ( len_p_str_tmp_1 - 2 ) ) ; len_p_str_tmp_2 := LENGTH( str_tmp_1 ) ; IF ( len_p_str_tmp_2 > 0 ) THEN WHILE ( comma_position < len_p_str_tmp_2 ) LOOP comma_position := INSTR( str_tmp_1 , ',' , symbol_position ) ; lbrace_position := INSTR( str_tmp_1 , '{' , symbol_position ) ; rbrace_position := INSTR( str_tmp_1 , '}' , symbol_position ) ; IF ( comma_position = 0 ) THEN comma_position := len_p_str_tmp_2 ; str_tmp_key_value := substr( str_tmp_1 , symbol_position , ( len_p_str_tmp_2 - symbol_position + 1 ) ) ; array_value.EXTEND ; array_value( array_cnt ) := str_tmp_key_value ; EXIT ; ELSIF ( ( lbrace_position > 0 ) AND ( lbrace_position < comma_position ) AND ( comma_position < rbrace_position ) ) THEN SELECT ( LENGTH( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) ) - LENGTH( REPLACE( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) , '{' , '' ) ) ) INTO lbrace_ajust_num ; SELECT ( LENGTH( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) ) - LENGTH( REPLACE( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) , '}' , '' ) ) ) INTO rbrace_ajust_num ; WHILE ( lbrace_ajust_num <> rbrace_ajust_num ) LOOP change_position := ( change_position + 1 ) ; rbrace_position := INSTR( str_tmp_1 , '}' , symbol_position , change_position ) ; SELECT ( LENGTH( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) ) - LENGTH( REPLACE( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) , '{' , '' ) ) ) INTO lbrace_ajust_num ; SELECT ( LENGTH( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) ) - LENGTH( REPLACE( SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) , '}' , '' ) ) ) INTO rbrace_ajust_num ; END LOOP ; change_position := 0 ; str_tmp_key_value := SUBSTR( str_tmp_1 , symbol_position , ( rbrace_position - symbol_position + 1 ) ) ; symbol_position := ( rbrace_position + 2 ) ; array_value.EXTEND ; array_value( array_cnt ) := str_tmp_key_value ; array_cnt := ( array_cnt + 1 ) ; ELSE str_tmp_key_value := SUBSTR( str_tmp_1 , symbol_position , ( comma_position - symbol_position ) ) ; symbol_position := ( comma_position + 1 ) ; array_value.EXTEND ; array_value( array_cnt ) := str_tmp_key_value ; array_cnt := ( array_cnt + 1 ) ; END IF ; END LOOP ;
FOR array_loop_cnt IN 1..array_value.count LOOP colon_position := 0 ; array_key_value := '' ; array_value_value := '' ; str_tmp_key_value := btrim( array_value( array_loop_cnt ) ) ; --去除开头和结尾的空格 colon_position := INSTR( str_tmp_key_value , TO_CHAR( ':' ), 1 , 1 ) ; array_key_value := btrim( SUBSTR( str_tmp_key_value , 1 , ( colon_position - 1 ) ) ) ; array_value_value := btrim( SUBSTR( str_tmp_key_value , ( colon_position + 1 ), LENGTH( str_tmp_key_value ) ) ) ; IF( array_key_value = ( '"' || key_name || '"' ) ) THEN return_value := array_value_value ; RETURN return_value ; END IF ; END LOOP ; END IF ; RETURN return_value ; END ; / |
测试用例：
【用例1】select jsonpars('{"a":"info_a","b":{"c":"info_c","d":"info_d"}}','a');
【用例2】select jsonpars('{"a":"info_a","b":{"c":"info_c","d":"info_d"}}','b');
【用例3】select jsonpars(jsonpars('{"a":"info_a","b":{"c":"info_c","d":"info_d"}}','b'),'d');
【用例4】
select jsonpars(jsonpars('{"a":"info_a","b":{"c1":"info_c","c2":{"d1":"info_d1","d2":"info_d2"},"d":"info_d"}}','b'),'d');
- 点赞
- 收藏
- 关注作者
评论（0）