/****** 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;