SQL 2012 解析JSON字符串
USE [NewFlexDB] GO /****** Object: UserDefinedFunction [dbo].[parseJSON] Script Date: 2019/12/17 11:30:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[parseJSON] ( @JSON NVARCHAR(MAX) ) RETURNS @hierarchy TABLE ( element_id INT IDENTITY(1, 1) NOT NULL , /* 内部代理主键给出解析顺序和列表顺序*/ sequenceNo [INT] NULL , /* 元素顺序中的位置 */ parent_ID INT , /* 如果元素具有父元素，则它在此列中。 该文档是最终的父文档，因此您可以从文档的递归中获得结构 */ Object_ID INT , /* 每个列表或对象都有一个对象ID。 这将所有元素绑定到父元素。 列表在这里被视为对象 */ NAME NVARCHAR(2000) , /* 对象的名称 */ StringValue NVARCHAR(MAX) NOT NULL , /* 元素值的字符串表示形式. */ ValueType VARCHAR(10) NOT NULL /* 在StringValue中表示为字符串的值的声明类型*/ ) AS BEGIN DECLARE @FirstObject INT , --JSON字符串中找到的第一个开括号的索引 @OpenDelimiter INT , --JSON字符串中找到的下一个开括号的索引 @NextOpenDelimiter INT , --JSON字符串中找到的后续开括号的索引 @NextCloseDelimiter INT , --JSON字符串中找到的后续右括号的索引 @Type NVARCHAR(10) , --数据类型 @NextCloseDelimiterChar CHAR(1) , --either a '}' or a ']' @Contents NVARCHAR(MAX) , --方括号表达式的未解析内容 @Start INT , --您正在解析的令牌开始的索引 @end INT , --您正在解析的令牌结尾的索引 @param INT , --下一个对象/数组标记末尾的参数 @EndOfName INT , --在对象/数组标记末尾的参数开始的索引 @token NVARCHAR(200) , --字符串或对象 @value NVARCHAR(MAX) , --字符串形式的值 @SequenceNo INT , --列表中的序列号 @name NVARCHAR(200) , --名称为字符串 @parent_ID INT , --下一个要分配的父ID @lenJSON INT , --JSON字符串的当前长度 @characters NCHAR(36) , --用于将十六进制转换为十进制 @result BIGINT , --解析的十六进制符号的值 @index SMALLINT , --用于解析十六进制值 @Escape INT; --下一个转义字符的索引 DECLARE @Strings TABLE /* 在此临时表中，我们保留所有字符串，甚至包括元素的名称，因为它们以不同的方式“转义”，并且可能包含未转义的表示对象或列表的括号。 这些在JSON字符串中被表示字符串的标记替换 */ ( String_ID INT IDENTITY(1, 1) , StringValue NVARCHAR(MAX) ); SELECT --初始化字符以将十六进制转换为ASCII @characters = '0123456789abcdefghijklmnopqrstuvwxyz' , @SequenceNo = 0 , --设置顺序号 /*首先，我们处理所有字符串。 这样做是因为[{}和]不会在字符串中转义，这会使迭代解析变得复杂。*/ @parent_ID = 0; WHILE 1 = 1 --forever until there is nothing more to do BEGIN SELECT @Start = PATINDEX('%[^a-zA-Z]["]%',@JSON COLLATE SQL_Latin1_General_CP850_BIN);--下一个定界字符串 IF @Start = 0 BREAK; --no more so drop through the WHILE loop IF SUBSTRING(@JSON, @Start + 1, 1) = '"' BEGIN --分隔名称 SET @Start = @Start + 1; SET @end = PATINDEX('%[^\]["]%',RIGHT(@JSON,LEN(@JSON + '|') - @Start)); END; IF @end = 0 --没有最后一个字符串的定界符 BREAK; --no more SELECT @token = SUBSTRING(@JSON, @Start + 1, @end - 1); --现在放入转义的控制字符 SELECT @token = REPLACE(@token, FromString, ToString) FROM ( SELECT '\"' AS FromString , '"' AS ToString 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; SELECT @result = 0 , @Escape = 1; --开始取出任何十六进制转义码 WHILE @Escape > 0 BEGIN SELECT @index = 0 , --找到下一个十六进制转义序列 @Escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%',@token); IF @Escape > 0 --if there is one BEGIN WHILE @index < 4 --\ x序列中始终有四个数字 BEGIN SELECT --determine its value @result = @result + POWER(16, @index) * ( CHARINDEX(SUBSTRING(@token, @Escape + 2 + 3 - @index, 1), @characters) - 1 ) , @index = @index + 1; END; -- 并用其unicode值替换十六进制序列 SELECT @token = STUFF(@token, @Escape, 6,NCHAR(@result)); END; END; --现在将字符串存储起来 INSERT INTO @Strings ( StringValue ) SELECT @token; --并将字符串替换为令牌 SELECT @JSON = STUFF(@JSON, @Start, @end + 1, '@string' + CONVERT(NVARCHAR(5), @@identity)); END; -- 现在将删除所有字符串。 现在我们找到了第一个节点。 WHILE 1 = 1 --forever until there is nothing more to do BEGIN SELECT @parent_ID = @parent_ID + 1; --通过查找方括号找到第一个对象或列表 SELECT @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'; SELECT @OpenDelimiter = @FirstObject; WHILE 1 = 1 --find the innermost object or list... BEGIN SELECT @lenJSON = LEN(@JSON + '|') - 1; --在打开定界符之后找到匹配的关闭定界符 SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar, @JSON, @OpenDelimiter + 1); --是否存在任何一种中间的打开定界符 SELECT @NextOpenDelimiter = PATINDEX('%[{[[]%', RIGHT(@JSON, @lenJSON - @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_BIN);--object IF @NextOpenDelimiter = 0 BREAK; SELECT @NextOpenDelimiter = @NextOpenDelimiter + @OpenDelimiter; IF @NextCloseDelimiter < @NextOpenDelimiter BREAK; IF SUBSTRING(@JSON, @NextOpenDelimiter, 1) = '{' SELECT @NextCloseDelimiterChar = '}' , @Type = 'object'; ELSE SELECT @NextCloseDelimiterChar = ']' , @Type = 'array'; SELECT @OpenDelimiter = @NextOpenDelimiter; END; ---并解析出列表或名称/值对 SELECT @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1, @NextCloseDelimiter - @OpenDelimiter - 1); SELECT @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 IF @Type = 'Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null BEGIN SELECT @SequenceNo = 0 , @end = CHARINDEX(':', ' ' + @Contents);--if there is anything, it will be a string-based name. SELECT @Start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @Contents);--AAAAAAAA SELECT @token = SUBSTRING(' ' + @Contents, @Start + 1, @end - @Start - 1) , @EndOfName = PATINDEX('%[0-9]%', @token COLLATE SQL_Latin1_General_CP850_BIN) , @param = RIGHT(@token, LEN(@token) - @EndOfName + 1); SELECT @token = LEFT(@token, @EndOfName - 1) , @Contents = RIGHT(' ' + @Contents, LEN(' ' + @Contents + '|') - @end - 1); SELECT @name = StringValue FROM @Strings WHERE String_ID = @param; --fetch the name END; ELSE SELECT @name = NULL , @SequenceNo = @SequenceNo + 1; SELECT @end = CHARINDEX(',', @Contents);-- a string-token, object-token, list-token, number,boolean, or null IF @end = 0 SELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents + ' ') + 1; SELECT @Start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @Contents); --select @start,@end, LEN(@contents+'|'), @contents SELECT @value = RTRIM(SUBSTRING(@Contents, @Start, @end - @Start)) , @Contents = RIGHT(@Contents + ' ', LEN(@Contents + '|') - @end); IF SUBSTRING(@value, 1, 7) = '@object' INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , Object_ID , ValueType ) SELECT @name , @SequenceNo , @parent_ID , SUBSTRING(@value, 8, 5) , SUBSTRING(@value, 8, 5) , 'object'; ELSE IF SUBSTRING(@value, 1, 6) = '@array' INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , Object_ID , ValueType ) SELECT @name , @SequenceNo , @parent_ID , SUBSTRING(@value, 7, 5) , SUBSTRING(@value, 7, 5) , 'array'; ELSE IF SUBSTRING(@value, 1, 7) = '@string' INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , ValueType ) SELECT @name , @SequenceNo , @parent_ID , StringValue , 'string' FROM @Strings WHERE String_ID = SUBSTRING(@value, 8, 5); ELSE IF @value IN ( 'true', 'false' ) INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , ValueType ) SELECT @name , @SequenceNo , @parent_ID , @value , 'boolean'; ELSE IF @value = 'null' INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , ValueType ) SELECT @name , @SequenceNo , @parent_ID , @value , 'null'; ELSE IF PATINDEX('%[^0-9]%', @value COLLATE SQL_Latin1_General_CP850_BIN) > 0 INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , ValueType ) SELECT @name , @SequenceNo , @parent_ID , @value , 'real'; ELSE INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , ValueType ) SELECT @name , @SequenceNo , @parent_ID , @value , 'int'; IF @Contents = ' ' SELECT @SequenceNo = 0; END; END; INSERT INTO @hierarchy ( NAME , sequenceNo , parent_ID , StringValue , Object_ID , ValueType ) SELECT '-' , 1 , NULL , '' , @parent_ID - 1 , @Type; -- RETURN; END;
转载自：https://www.cnblogs.com/suizhikuo/p/8675366.html
