[转]在SqlServer中解析JSON数据

CREATE FUNCTION dbo . parseJSON ( @JSON NVARCHAR ( MAX )) RETURNS @hierarchy TABLE ( element_id INT IDENTITY ( 1 , 1 ) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ sequenceNo [int] NULL, /* t

CREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))

RETURNS @hierarchy TABLE

(

element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */

sequenceNo [int] NULL, /* the place in the sequence for the element */

parent_ID INT,/* 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 */

Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */

NAME NVARCHAR(2000),/* the name of the object */

StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */

ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/

)

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(200),--either a string or object

@value NVARCHAR(MAX), -- the value as a string

@SequenceNo int, -- the sequence number within a list

@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(36),--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

DECLARE @Strings TABLE /* 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 */

(

String_ID INT IDENTITY(1, 1),

StringValue NVARCHAR(MAX)

)

SELECT--initialise the characters to convert hex to ascii

@characters='0123456789abcdefghijklmnopqrstuvwxyz',

@SequenceNo=0, --set the sequence no. to something sensible.

/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */

@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);--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) collateSQL_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, 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

--Begin to take out any hex escape codes

WHILE @escape>0

BEGIN

SELECT @index=0,

--find the next hex escape sequence

@escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collateSQL_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

SELECT --determine its value

@result=@result+POWER(16, @index)

*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),

@characters)-1), @index=@index+1 ;

END

-- and replace the hex sequence by its unicode value

SELECT @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

SELECT @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

SELECT @parent_ID=@parent_ID+1

--find the first object or list by looking for the open bracket

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

--find the matching close-delimiter proceeding after the open-delimiter

SELECT

@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,

@OpenDelimiter+1)

--is there an intervening open-delimiter of either type

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

---and parse out the list or name/value pairs

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 collateSQL_Latin1_General_CP850_Bin)--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+' ' collateSQL_Latin1_General_CP850_Bin)

+1

SELECT

@start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collateSQL_Latin1_General_CP850_Bin)

--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

GO