Oracle创建自定义函数解析JSON字符串

 -- 1.创建一个type，返回table类型
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2(4000);
 -- 2.创建一个分割的函数，主要对json进行按照固定割串分割
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
  RETURN ty_str_split
IS
  j INT := 0;
  i INT := 1;
  len INT := 0;
  len1 INT := 0;
  str VARCHAR2 (4000);
  str_split ty_str_split := ty_str_split ();
BEGIN
  len := LENGTH (p_str);
  len1 := LENGTH (p_delimiter);

  WHILE j < len
  LOOP
    j := INSTR (p_str, p_delimiter, i);

    IF j = 0
    THEN
        j := len;
        str := SUBSTR (p_str, i);
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;

        IF i >= len
        THEN
          EXIT;
        END IF;
    ELSE
        str := SUBSTR (p_str, i, j - i);
        i := j + len1;
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;
    END IF;
  END LOOP;

  RETURN str_split;
END fn_split;

3.创建一个函数，解析json对象{}

  --  3.创建一个函数，解析json对象{}
CREATE OR REPLACE FUNCTION parsejson(p_jsonstr varchar2, p_key varchar2)
  RETURN VARCHAR2 IS
  rtnVal    VARCHAR2(4000);
  i         NUMBER(20);
  jsonkey   VARCHAR2(4000);
  jsonvalue VARCHAR2(4000);
  json      VARCHAR2(4000);
BEGIN
  IF p_jsonstr IS NOT NULL THEN
    json := REPLACE(p_jsonstr, '{', '');
    json := REPLACE(json, '}', '');
    json := replace(json, '"', '');
    FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOP
      IF temprow.column_value IS NOT NULL THEN
        i         := 0;
        jsonkey   := '';
        jsonvalue := '';
        FOR tem2 IN (SELECT *
                       FROM TABLE(fn_split(temprow.column_value, ':'))) LOOP
          IF i = 0 THEN
            jsonkey := tem2.column_value;
          END IF;
          IF i = 1 THEN
            jsonvalue := tem2.column_value;
          END IF;
          i := i + 1;
        END LOOP;
        IF (jsonkey = p_key) THEN
          rtnVal := jsonvalue;
        END if;
      END IF;
    END LOOP;
  END IF;
  RETURN rtnVal;
END parsejson;

json示例：

/* DATA  = {"thir":null,"loa":"","cName":888,"contacts":[{"name": "John", "age": 30},{"name": "John1", "age": 30}]}*/
SELECT parsejson (DATA,'cName') name  FROM  JSON_DATA_1;

4.解析JSON数组[{},{}]

  --  4.解析JSON数组[{},{}]
create function parsearray(p_jsonstr varchar2, p_key varchar2,p_keys varchar2)
  return varchar2 is
  rtnval    varchar2(4000);
    i        number(20);
  jsonkey   varchar2(4000);
  jsonvalue varchar2(4000);
  json      varchar2(4000);
    json2      varchar2(4000);
begin
  if p_jsonstr is not null then
    json := replace(p_jsonstr, '[', '');
    json := replace(json, ']', '');
    json := replace(json, p_keys, '');
    for temprow in (select * from table(fn_split(json, ','))) loop
      if temprow.column_value is not null then
                json2 := replace(temprow.column_value, '{', '');
                json2 := replace(json2, '}', '');
                json2 := replace(json2, '"', '');
                json2 := replace(json2, p_keys, '');
                for temprow2 in (select * from table(fn_split(json2, ','))) loop
                    i:=0;
                    jsonkey:= '';
                    jsonvalue:= '';
                    for tmpe2 in (select * from table(fn_split(temprow2.column_value, ':'))) loop
                            if i = 0 then
                                jsonkey := tmpe2.column_value;
                            end if;
                            if i = 1 then
                                jsonvalue := tmpe2.column_value;
                            end if;
                            i := i + 1;
                    end loop;
                    if (jsonkey = p_key) then
                        if rtnval is not null then
                            rtnval :=rtnval||','||jsonvalue;
                         else
                            rtnval :=jsonvalue;
                        end if;
                    end if;
                end loop;
      end if;
    end loop;
  end if;
 return rtnval;
end parsearray;
/

示例：

/**
 {"list":[{"name": "John", "age": 30},{"name": "John1", "age": 30}]}
 */
-- p_jsonstr 为数据, p_key 为需要的变量,p_keys 为list名称 （此处是未了替换为空）
SELECT parsearray(
               '{"list":[{"name": "John", "age": 30},{"name": "John1", "age": 30}]}',
               'name',
         '"list":') as extend1
FROM JSON_DATA_1 l;

5. 字符串转list

create or replace type split_table is table of varchar2 (4000);
/**
  字符串转list
 */
create or replace function splitstr(p_string    in varchar2,
                                    p_delimiter in varchar2 := ',')
  return split_table
  pipelined as
  v_length number := length(p_string);
  v_start  number := 1;
  v_index  number;
begin
  while (v_start <= v_length) loop
    v_index := instr(p_string, p_delimiter, v_start);

    if v_index = 0 then
      pipe row(substr(p_string, v_start));
      v_start := v_length + 1;
    else
      pipe row(substr(p_string, v_start, v_index - v_start));
      v_start := v_index + 1;
    end if;
  end loop;

  return;
end splitstr;

示例：

SELECT * FROM TABLE(SPLITSTR('1,2,3', ','));

SELECT *
FROM TABLE (SPLITSTR((SELECT parsearray(
                                     '[{"name": "John", "age": 30},{"name": "John1", "age": 30}]',
                                     'name') as extend1
                      FROM JSON_DATA_1 l), ','));