• Sql Server数据库json数组解析转换为表结构数据


    方案一:使用openjson函数

    PS:使用openjson函数需要目标数据库兼容级别支持(130)

    可用以下查询语句查看数据库兼容级别 :

    SELECT name,compatibility_level FROM sys.databases WHERE name ='数据库名'

    修改目标数据库兼容级别:

    ALTER DATABASE 数据库名 SET compatibility_level = 130

    DECLARE @jsonStr VARCHAR(MAX)
    SET @jsonStr ='[{"perName": "张三","perAge": 20},{"perName": "李四","perAge": 23},{"perName": "王五","perAge": 25}]'
    
    SELECT JSON_VALUE(value,'$.perName') AS perName,JSON_VALUE(value,'$.perAge') AS perAge
    FROM openjson(@jsonStr) AS A

    执行结果:

    方案二:创建表值函数 ParseJsonFunc

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE FUNCTION [dbo].[ParseJsonFunc]( @json nvarchar(max) ) 
    RETURNS @hierarchy table 
    ( 
    object_id int NOT NULL, /* [0 -- Not an object] each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ 
    parent_id int NOT NULL, /* [0 -- Root] if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */ 
    name nvarchar(2000), /* the name of the object */ 
    stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */ 
    valuetype nvarchar(100) NOT NULL, /* the declared type of the value represented as a string in stringvalue*/ 
    bigintvalue bigint, 
    boolvalue bit 
    ) 
    
    AS 
    
    BEGIN 
    DECLARE 
    @firstobject int, --the index of the first open bracket found in the JSON string 
    @opendelimiter int, --the index of the next open bracket found in the JSON string 
    @nextopendelimiter int,--the index of subsequent open bracket found in the JSON string 
    @nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string 
    @type nvarchar(10),--whether it denotes an object or an array 
    @nextclosedelimiterChar CHAR(1),--either a '}' or a ']' 
    @contents nvarchar(MAX), --the unparsed contents of the bracketed expression 
    @start int, --index of the start of the token that you are parsing 
    @end int,--index of the end of the token that you are parsing 
    @param int,--the parameter at the end of the next Object/Array token 
    @endofname int,--the index of the start of the parameter at end of Object/Array token 
    @token nvarchar(4000),--either a string or object 
    @value nvarchar(MAX), -- the value as a string 
    @name nvarchar(200), --the name as a string 
    @parent_id int,--the next parent ID to allocate 
    @lenjson int,--the current length of the JSON String 
    @characters NCHAR(62),--used to convert hex to decimal 
    @result BIGINT,--the value of the hex symbol being parsed 
    @index SMALLINT,--used for parsing the hex value 
    @escape int; --the index of the next escape character 
    
    /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' 
    * in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in 
    * the JSON string by tokens representing the string 
    */ 
    DECLARE @strings table 
    ( 
    string_id int IDENTITY(1, 1), 
    stringvalue nvarchar(MAX) 
    ) 
    
    /* initialise the characters to convert hex to ascii */ 
    SET @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    SET @parent_id = 0; 
    
    /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */ 
    WHILE 1 = 1 /* forever until there is nothing more to do */ 
    BEGIN 
    SET @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */ 
    
    IF @start = 0 BREAK; /*no more so drop through the WHILE loop */ 
    
    IF SUBSTRING(@json, @start+1, 1) = '"' 
    BEGIN /* Delimited name */ 
    SET @start = @start+1; 
    SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin); 
    END 
    
    IF @end = 0 /*no end delimiter to last string*/ 
    BREAK; /* no more */ 
    
    SELECT @token = SUBSTRING(@json, @start+1, @end-1) 
    
    /* now put in the escaped control characters */ 
    SELECT @token = REPLACE(@token, from_string, to_string) 
    FROM 
    ( 
    SELECT '\"' AS from_string, '"' AS to_string 
    UNION ALL 
    SELECT '\\', '\' 
    UNION ALL 
    SELECT '\/', '/' 
    UNION ALL 
    SELECT '\b', CHAR(08) 
    UNION ALL 
    SELECT '\f', CHAR(12) 
    UNION ALL 
    SELECT '\n', CHAR(10) 
    UNION ALL 
    SELECT '\r', CHAR(13) 
    UNION ALL 
    SELECT '\t', CHAR(09) 
    ) substitutions; 
    
    SET @result = 0; 
    SET @escape = 1; 
    
    /*Begin to take out any hex escape codes*/ 
    WHILE @escape > 0 
    BEGIN 
    
    /* find the next hex escape sequence */ 
    SET @index = 0; 
    SET @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin); 
    
    IF @escape > 0 /* if there is one */ 
    BEGIN 
    
    WHILE @index < 4 /* there are always four digits to a \x sequence */ 
    BEGIN 
    /* determine its value */ 
    SET @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 - @index, 1), @characters) - 1); 
    SET @index = @index + 1; 
    END 
    
    /* and replace the hex sequence by its unicode value */ 
    SET @token = STUFF(@token, @escape, 6, NCHAR(@result)); 
    END 
    
    END 
    
    /* now store the string away */ 
    INSERT INTO @strings (stringvalue) SELECT @token; 
    
    /* and replace the string with a token */ 
    SET @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5), @@identity)); 
    
    END 
    
    /* all strings are now removed. Now we find the first leaf. */ 
    WHILE 1 = 1 /* forever until there is nothing more to do */ 
    BEGIN 
    
    SET @parent_id = @parent_id + 1; 
    
    /* find the first object or list by looking for the open bracket */ 
    SET @firstobject = PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin); /*object or array*/ 
    
    IF @firstobject = 0 BREAK; 
    
    IF (SUBSTRING(@json, @firstobject, 1) = '{') 
    SELECT @nextclosedelimiterChar = '}', @type = 'object'; 
    ELSE 
    SELECT @nextclosedelimiterChar = ']', @type = 'array'; 
    
    
    SET @opendelimiter = @firstobject; 
    
    WHILE 1 = 1 --find the innermost object or list... 
    BEGIN 
    SET @lenjson = LEN(@json+'|') - 1; 
    
    /* find the matching close-delimiter proceeding after the open-delimiter */ 
    SET @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json, @opendelimiter + 1); 
    
    /* is there an intervening open-delimiter of either type */ 
    SET @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin); /*object*/ 
    
    IF @nextopendelimiter = 0 BREAK; 
    
    SET @nextopendelimiter = @nextopendelimiter + @opendelimiter; 
    
    IF @nextclosedelimiter < @nextopendelimiter BREAK; 
    
    IF SUBSTRING(@json, @nextopendelimiter, 1) = '{' 
    SELECT @nextclosedelimiterChar = '}', @type = 'object'; 
    ELSE 
    SELECT @nextclosedelimiterChar = ']', @type = 'array'; 
    
    SET @opendelimiter = @nextopendelimiter; 
    END 
    
    /* and parse out the list or name/value pairs */ 
    SET @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1); 
    
    SET @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter + 1, '@' + @type + CONVERT(nvarchar(5), @parent_id)); 
    
    WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin)) < > 0 
    BEGIN /* WHILE PATINDEX */ 
    
    IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/ 
    BEGIN 
    
    SET @end = CHARINDEX(':', ' '+@contents); /*if there is anything, it will be a string-based name.*/ 
    SET @start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); /*AAAAAAAA*/ 
    
    SET @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1); 
    SET @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin); 
    SET @param = RIGHT(@token, LEN(@token)-@endofname + 1); 
    
    SET @token = LEFT(@token, @endofname - 1); 
    SET @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1); 
    
    SELECT @name = stringvalue FROM @strings WHERE string_id = @param; /*fetch the name*/ 
    
    END 
    ELSE 
    BEGIN 
    SET @name = null; 
    END 
    
    SET @end = CHARINDEX(',', @contents); /*a string-token, object-token, list-token, number,boolean, or null*/ 
    
    IF @end = 0 
    SET @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1; 
    
    SET @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); 
    
    /*select @start,@end, LEN(@contents+'|'), @contents */ 
    
    SET @value = RTRIM(SUBSTRING(@contents, @start, @end-@start)); 
    SET @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end); 
    
    IF SUBSTRING(@value, 1, 7) = '@object' 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) 
    SELECT @name, @parent_id, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object'; 
    
    ELSE 
    IF SUBSTRING(@value, 1, 6) = '@array' 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) 
    SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array'; 
    ELSE 
    IF SUBSTRING(@value, 1, 7) = '@string' 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
    SELECT @name, @parent_id, stringvalue, 'string', 0 
    FROM @strings 
    WHERE string_id = SUBSTRING(@value, 8, 5); 
    ELSE 
    IF @value IN ('true', 'false') 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, boolvalue) 
    SELECT @name, @parent_id, @value, 'boolean', 0, CASE @value WHEN 'true' THEN 1 ELSE 0 END; 
    ELSE 
    IF @value = 'null' 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
    SELECT @name, @parent_id, @value, 'null', 0; 
    ELSE 
    IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
    SELECT @name, @parent_id, @value, 'real', 0; 
    ELSE 
    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, bigintvalue)
    SELECT @name, @parent_id, @value, 'bigint', 0, CONVERT(BIGINT,@value);
    
    END /* WHILE PATINDEX */ 
    
    END /* WHILE 1=1 forever until there is nothing more to do */ 
    
    INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) 
    SELECT '', 0, '', @parent_id - 1, @type; 
    
    RETURN; 
    
    END
    
    GO
    View Code

    使用方法:

    DECLARE @jsonStr VARCHAR(MAX)
    SET @jsonStr ='[{"perName": "张三","perAge": 20},{"perName": "李四","perAge": 23},{"perName": "王五","perAge": 25}]'
    
    IF OBJECT_ID(N'tempdb..#tempJson', N'U') IS NOT NULL
    BEGIN
      DROP TABLE #tempJson;
    END
    SELECT parent_id,name,stringvalue
    INTO #tempJson
    FROM dbo.ParseJsonFunc(@jsonStr) WHERE ISNULL(name,'')<> ''
    
    SELECT parent_id, perName = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE parent_id = T.parent_id AND name='perName' FOR XML PATH('')), 1, 1, ''))
    , perAge = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE parent_id = T.parent_id AND name='perAge' FOR XML PATH('')), 1, 1, ''))
    FROM #tempJson AS T
    GROUP BY parent_id

    执行结果:

  • 相关阅读:
    H5基础
    函数
    分支结构/循环结构
    图解 idea打jar包的步骤
    jmeter查看使用文档后总结
    Ride的使用
    Robot Framework
    jmeter+ant+jenkins
    ant
    Mysql选择合适的数据类型
  • 原文地址:https://www.cnblogs.com/xy0710/p/16362753.html
Copyright © 2020-2023  润新知