解析JSON的sql函数脚本文件​

使用场景：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解析结果：

SQLSERVER2008 解析JSON函数_数据

 

重组后的数据结果：

SQLSERVER2008 解析JSON函数_解析json_02