SQLSERVER2008 解析JSON函数

解析JSON的sql函数脚本文件下载地址：https://files.cnblogs.com/files/YoungHeart/parseJSON.rar

使用场景：1，接口输入比较复杂时可使用json传输，相比逗号隔开数据值的意义更明确，避免输入参数过多，这个只需要一个json字符串参数。
                  2，在一下配置场景可使用json存储，使用时再解析。不适合作为查询条件。

使用方法

 
        DECLARE @parent_ID INT ,
            @Object_ID INT;
        DECLARE @index INT ,
            @rate_start FLOAT ,
            @rate_end FLOAT ,
            @diffId INT;
            
       --自己需要的json结果
        CREATE TABLE #temp_question_recommend_diff_config
            (
              [index] INT ,
              rate_start FLOAT ,
              rate_end FLOAT ,
              diffId INT
            );
        SELECT  *
        INTO    #temp_question_recommend_diff_config_json
        FROM    Xxyx_System.dbo.parseJSON('[{"index":0,"rate_start":100.00,"rate_end":80.00,"diffId":-1},{"index":1,"rate_start":80.00,"rate_end":70.00,"diffId":-1},{"index":2,"rate_start":70.00,"rate_end":60.00,"diffId":-1},{"index":3,"rate_start":60.00,"rate_end":0.00,"diffId":-1}]');
 
               
 
        SELECT  @parent_ID = Object_ID
        FROM    #temp_question_recommend_diff_config_json
        WHERE   ValueType = 'array';
  
               
        --解析json
        WHILE ( EXISTS ( SELECT 1
                         FROM   #temp_question_recommend_diff_config_json
                         WHERE  parent_ID = @parent_ID ) )
            BEGIN
 
                SELECT TOP 1
                        @Object_ID = Object_ID
                FROM    #temp_question_recommend_diff_config_json
                WHERE   parent_ID = @parent_ID;
 
                SELECT  @index = StringValue
                FROM    #temp_question_recommend_diff_config_json
                WHERE   parent_ID = @Object_ID
                        AND NAME = 'index';
                
                SELECT  @rate_start = StringValue
                FROM    #temp_question_recommend_diff_config_json
                WHERE   parent_ID = @Object_ID
                        AND NAME = 'rate_start';
                
                SELECT  @rate_end = StringValue
                FROM    #temp_question_recommend_diff_config_json
                WHERE   parent_ID = @Object_ID
                        AND NAME = 'rate_end';
                
                SELECT  @diffId = StringValue
                FROM    #temp_question_recommend_diff_config_json
                WHERE   parent_ID = @Object_ID
                        AND NAME = 'diffId';
                
                INSERT  INTO #temp_question_recommend_diff_config
                        ( [index] ,
                          rate_start ,
                          rate_end ,
                          diffId
                        )
                VALUES  ( @index , -- index - int
                          @rate_start , -- rate_start - float
                          @rate_end , -- rate_end - float
                          @diffId  -- diffId - int
                        );
 
                DELETE  #temp_question_recommend_diff_config_json
                WHERE   Object_ID = @Object_ID;
            END;
            
         --解析后的json结构     
        SELECT  *
        FROM    #temp_question_recommend_diff_config_json;
        
        --最终自己需要的数据
        SELECT  *
        FROM    #temp_question_recommend_diff_config;
                    
        DROP TABLE #temp_question_recommend_diff_config;
        DROP TABLE #temp_question_recommend_diff_config_json;

JSON解析结果：

 

重组后的数据结果：