数据库解析一维和二维简易JSON，

项目还在使用Oracle11，不支持后续官方的json解析方式， 在 前年、去年、今年 接连 遇到json解析问题后（其实是公司的轮子效率太慢，太复杂，决定自己造个轮子，看看到底为什么慢，是不是真的很复杂）

关于要不要自己再造轮子，其实还是比较犹豫的，后来不断遇到相似问题，才促使我自己搞一搞。

断断续续搞了1个礼拜多，算是初步有结果了。

至于说为什么JSON不放JAVA代码解析后再传数据库，项目总归是有各种各样的限制，以及人力物力的限制，不得不在数据库自己解析。

------------------------------------------------------------------------------------------------------------------------------

以上都是废话，

         如果你的JSON很复杂，请跳过本文。 这边只支持简易JSON。

        复杂JSON还未实现，也肯定不应该在数据库里处理 。

以下是代码实现

------------------------------------------------------------------------------------------------------------------------------

功能实现： json数组解析成sql语句，通过拼接sql可以执行

                已支持 字符串、数值、布尔值（转成1和0）、null；

实现方式：无循环嵌套，通过状态+队列来判断key和value ，最后使用sql语句，对数据进行整合

限制：1.由于表结构格式不一致，暂时没有实现value是 对象和数组的实现方式

           2. 目前使用场景还 比较单一，未作兼容性处理及错误判断.

正文

JSON数据：

[ { "key" : "valu e ","key2":"\\a\\\\\"","key3":4.43,"key4":true,"key5": null  },{"key5":Null,"key":"1","key3":0.02,"key4":false,"key2" : "2[{:," }] 

 生成代码：

SELECT *
  FROM (SELECT '1' AS KEY, '2[{:,' AS KEY2, 0.02 AS KEY3, 0 AS KEY4, NULL AS KEY5
          FROM DUAL
        UNION ALL
        SELECT 'valu e ' AS KEY, '\\a\\\\\"' AS KEY2, 4.43 AS KEY3, 1 AS KEY4, NULL AS KEY5
          FROM DUAL) V_JSON

 生成代码查询：

查询效率：  大约0.09S

下面是代码， 使用Oracle来处理的，使用到的函数是 SUBSTR ， LTRIM、 TRIM 、 LISTAGG

如果是其他数据库应该都有替代的函数， 稍微改下就可以用

DECLARE
  --字符串、数值、布尔值、null、对象和数组
  V_JSON VARCHAR2(2000) := ' [ { "key" : "valu e ","key2":"\\a\\\\\"","key3":4.43,"key4":true,"key5": null  },{"key5":Null,"key":"1","key3":0.02,"key4":false,"key2" : "2[{:," }] ';

  --不支持参数值是对象和数组的情况
  --V_JSON      VARCHAR2(2000) := ' [ { "key" : "valu e ","key2":"\\a\\\\\"","key3":4,"key4":true,"key5":[1,2,3,4]},{"key":"1","key3":2,"key4":false,"key2" : "2[{:,","key5":[342,3,2]}] ';

  V_LENGTH INT; --字符串总长度
  V_STR    VARCHAR2(10); --当前字符
  V_ZT     INT := -1; --状态 0 正常， -1 忽略

  V_STR_S VARCHAR2(10); --上一字符
  V_ZT_S  INT := -1; --上一字符 状态 0 正常， -1 忽略

  V_SQL     VARCHAR2(3000); --解析出来的SQL
  V_SQL_OUT VARCHAR2(3000); --输出语句 
  V_WORD    VARCHAR2(3000); --字符串key 或value

  V_CHAT_TEMP VARCHAR2(3); --临时字符
  V_CHAT      VARCHAR2(3000); --字符栈，先进后出
  V_CHAT_ALL  VARCHAR2(3000); --所有字符

  V_I         NUMBER := 0;
  V_NUM_ARRYR NUMBER := 0; --数组序号
  V_NUM_KEY   NUMBER := 0; --key-value 序号
BEGIN

  --1. 
  V_LENGTH := LENGTH(V_JSON);
  FOR V_I IN 1 .. V_LENGTH LOOP
    V_STR := SUBSTR(V_JSON, V_I, 1);
  
    --DBMS_OUTPUT.PUT_LINE(V_I || '=' || V_STR);
  
    --1.处理反斜杠
    IF (V_STR_S = '\' AND V_ZT_S = 0) THEN
      V_STR_S := V_STR;
      V_ZT_S  := -1;
      V_WORD  := V_WORD || V_STR;
      CONTINUE;
    ELSE
      V_ZT := 0;
    END IF;
  
    --2.处理双引号
    IF SUBSTR(V_CHAT, 1, 1) = '"' THEN
      V_WORD := V_WORD || V_STR;
      IF V_STR = '"' THEN
        V_CHAT := SUBSTR(V_CHAT, 2, LENGTH(V_CHAT) - 1);
        --DBMS_OUTPUT.PUT_LINE('V_CHAT=' || V_CHAT);
      END IF;
    
      V_STR_S := V_STR;
      V_ZT_S  := V_ZT;
      CONTINUE;
    END IF;
    --上一个字符状态
    V_STR_S := V_STR;
    V_ZT_S  := V_ZT;
  
    IF V_STR = '{' THEN
      V_CHAT     := V_STR || V_CHAT;
      V_CHAT_ALL := V_CHAT_ALL || V_STR;
      --DBMS_OUTPUT.PUT_LINE('V_CHAT=' || V_CHAT);
    ELSIF V_STR = '[' THEN
      V_CHAT     := V_STR || V_CHAT;
      V_CHAT_ALL := V_CHAT_ALL || V_STR;
      --DBMS_OUTPUT.PUT_LINE('V_CHAT=' || V_CHAT);
    ELSIF V_STR = '}' THEN
      V_CHAT     := SUBSTR(V_CHAT, 2, LENGTH(V_CHAT) - 1);
      V_CHAT_ALL := V_CHAT_ALL || V_STR;
    
      IF SUBSTR(V_CHAT_ALL, LENGTH(V_CHAT_ALL) - 1, 1) = ':' THEN
        DBMS_OUTPUT.PUT_LINE('V_VALUE=' || V_WORD);
        V_SQL := V_SQL || ' UNION ALL SELECT ''' || V_WORD || ''' AS V ,' || V_NUM_KEY ||
                 ' AS N, 2 AS TYPE, ' || V_NUM_ARRYR || ' AS J  FROM DUAL';
      END IF;
    
      IF SUBSTR(V_CHAT, 1, 1) = '[' THEN
        V_NUM_ARRYR := V_NUM_ARRYR + 1;
      END IF;
    
      V_NUM_KEY := 0;
      --DBMS_OUTPUT.PUT_LINE('V_CHAT=' || V_CHAT);
    ELSIF V_STR = ']' THEN
      V_CHAT     := SUBSTR(V_CHAT, 2, LENGTH(V_CHAT) - 1);
      V_CHAT_ALL := V_CHAT_ALL || V_STR;
      --DBMS_OUTPUT.PUT_LINE('V_CHAT=' || V_CHAT);
    ELSIF V_STR = ':' THEN
      V_CHAT_ALL := V_CHAT_ALL || V_STR;
      DBMS_OUTPUT.PUT_LINE('V_KEY=' || V_WORD);
      V_SQL  := V_SQL || ' UNION ALL SELECT ''' || V_WORD || ''' AS V ,' || V_NUM_KEY ||
                ' AS N , 1 AS TYPE, ' || V_NUM_ARRYR || ' AS J  FROM DUAL';
      V_WORD := '';
    ELSIF V_STR = ',' THEN
      V_CHAT_ALL := V_CHAT_ALL || V_STR;
      IF SUBSTR(V_CHAT_ALL, LENGTH(V_CHAT_ALL) - 1, 1) = ':' THEN
        DBMS_OUTPUT.PUT_LINE('V_VALUE=' || V_WORD);
        V_SQL := V_SQL || ' UNION ALL SELECT ''' || V_WORD || ''' AS V ,' || V_NUM_KEY ||
                 ' AS N , 2 AS TYPE, ' || V_NUM_ARRYR || ' AS J  FROM DUAL';
      END IF;
      V_WORD := '';
      IF SUBSTR(V_CHAT, 1, 1) = '{' THEN
        V_NUM_KEY := V_NUM_KEY + 1;
      END IF;
    ELSIF V_STR IN ('"') THEN
      V_CHAT := V_STR || V_CHAT;
      V_WORD := V_WORD || V_STR;
      --DBMS_OUTPUT.PUT_LINE('V_CHAT=' || V_CHAT);
      V_WORD := V_STR;
    ELSIF V_STR IN (' ') THEN
      --  DBMS_OUTPUT.PUT_LINE('空格');
      V_STR_S := V_STR;
    ELSE 
      V_WORD := V_WORD || V_STR;
    END IF;
  
  END LOOP;

  V_SQL := LTRIM(V_SQL, ' UNION ALL');
  --JSON结果转 输出语句
  V_SQL := ' WITH A AS ( ' || V_SQL || ' )
,b as (SELECT  A.J,A.N
, case when a_v.v like ''"%"'' then ''''''''|| substr(a_v.v,2,length(a_v.v)-2) || '''''''' 
  else  case when TRIM(upper(a_v.v)) in ( ''TRUE'') THEN ''1'' when  TRIM(upper(a_v.v)) in ( ''FALSE'')  then ''0'' else a_v.v END end  as v_param 
,substr(a.v,2,length(a.v)-2)  as v_key
 FROM A  LEFT JOIN A A_V ON A_V.N = A.N AND A_V.TYPE =2 AND A_V.J=A.J
WHERE  A.TYPE = 1  )
,c as (select  '' select '' || listagg( v_param ||'' as '' || v_key ,'','')  within group( order by v_key) || '' from dual '' as s 
from b  group by j  )
select ''SELECT * FROM ( ''||  listagg( c.s,'' union all '')  within group( order by null )  || '') V_JSON ''  as s 
from c ';

  EXECUTE IMMEDIATE V_SQL
    INTO V_SQL_OUT;

  DBMS_OUTPUT.PUT_LINE('V_SQL_OUT=');
  DBMS_OUTPUT.PUT_LINE(V_SQL_OUT);
END;

PS : 欢迎各个小伙伴积极交流