1.新建可编程性的表值函数（SQLSERVER）
1 USE [xxxx] 2 GO 3 4 /****** Object: UserDefinedFunction [dbo].[parseJSON] Script Date: 2018/3/15 15:35:27 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 12 CREATE FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX)) 13 RETURNS @hierarchy TABLE 14 ( 15 element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ 16 sequenceNo [int] NULL, /* the place in the sequence for the element */ 17 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 */ 18 Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ 19 NAME NVARCHAR(2000),/* the name of the object */ 20 StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */ 21 ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/ 22 ) 23 AS 24 BEGIN 25 DECLARE 26 @FirstObject INT, --the index of the first open bracket found in the JSON string 27 @OpenDelimiter INT,--the index of the next open bracket found in the JSON string 28 @NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string 29 @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string 30 @Type NVARCHAR(10),--whether it denotes an object or an array 31 @NextCloseDelimiterChar CHAR(1),--either a '}' or a ']' 32 @Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression 33 @Start INT, --index of the start of the token that you are parsing 34 @end INT,--index of the end of the token that you are parsing 35 @param INT,--the parameter at the end of the next Object/Array token 36 @EndOfName INT,--the index of the start of the parameter at end of Object/Array token 37 @token NVARCHAR(200),--either a string or object 38 @value NVARCHAR(MAX), -- the value as a string 39 @SequenceNo int, -- the sequence number within a list 40 @name NVARCHAR(200), --the name as a string 41 @parent_ID INT,--the next parent ID to allocate 42 @lenJSON INT,--the current length of the JSON String 43 @characters NCHAR(36),--used to convert hex to decimal 44 @result BIGINT,--the value of the hex symbol being parsed 45 @index SMALLINT,--used for parsing the hex value 46 @Escape INT --the index of the next escape character 47 48 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 */ 49 ( 50 String_ID INT IDENTITY(1, 1), 51 StringValue NVARCHAR(MAX) 52 ) 53 SELECT--initialise the characters to convert hex to ascii 54 @characters='0123456789abcdefghijklmnopqrstuvwxyz', 55 @SequenceNo=0, --set the sequence no. to something sensible. 56 /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */ 57 @parent_ID=0; 58 WHILE 1=1 --forever until there is nothing more to do 59 BEGIN 60 SELECT 61 @start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string 62 IF @start=0 BREAK --no more so drop through the WHILE loop 63 IF SUBSTRING(@json, @start+1, 1)='"' 64 BEGIN --Delimited Name 65 SET @start=@Start+1; 66 SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin); 67 END 68 IF @end=0 --no end delimiter to last string 69 BREAK --no more 70 SELECT @token=SUBSTRING(@json, @start+1, @end-1) 71 --now put in the escaped control characters 72 SELECT @token=REPLACE(@token, FROMString, TOString) 73 FROM 74 (SELECT 75 '\"' AS FromString, '"' AS ToString 76 UNION ALL SELECT '\\', '\' 77 UNION ALL SELECT '\/', '/' 78 UNION ALL SELECT '\b', CHAR(08) 79 UNION ALL SELECT '\f', CHAR(12) 80 UNION ALL SELECT '\n', CHAR(10) 81 UNION ALL SELECT '\r', CHAR(13) 82 UNION ALL SELECT '\t', CHAR(09) 83 ) substitutions 84 SELECT @result=0, @escape=1 85 --Begin to take out any hex escape codes 86 WHILE @escape>0 87 BEGIN 88 SELECT @index=0, 89 --find the next hex escape sequence 90 @escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin) 91 IF @escape>0 --if there is one 92 BEGIN 93 WHILE @index<4 --there are always four digits to a \x sequence 94 BEGIN 95 SELECT --determine its value 96 @result=@result+POWER(16, @index) 97 *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1), 98 @characters)-1), @index=@index+1 ; 99 100 END 101 -- and replace the hex sequence by its unicode value 102 SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result)) 103 END 104 END 105 --now store the string away 106 INSERT INTO @Strings (StringValue) SELECT @token 107 -- and replace the string with a token 108 SELECT @JSON=STUFF(@json, @start, @end+1, 109 '@string'+CONVERT(NVARCHAR(5), @@identity)) 110 END 111 -- all strings are now removed. Now we find the first leaf. 112 WHILE 1=1 --forever until there is nothing more to do 113 BEGIN 114 115 SELECT @parent_ID=@parent_ID+1 116 --find the first object or list by looking for the open bracket 117 SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array 118 IF @FirstObject = 0 BREAK 119 IF (SUBSTRING(@json, @FirstObject, 1)='{') 120 SELECT @NextCloseDelimiterChar='}', @type='object' 121 ELSE 122 SELECT @NextCloseDelimiterChar=']', @type='array' 123 SELECT @OpenDelimiter=@firstObject 124 WHILE 1=1 --find the innermost object or list... 125 BEGIN 126 SELECT 127 @lenJSON=LEN(@JSON+'|')-1 128 --find the matching close-delimiter proceeding after the open-delimiter 129 SELECT 130 @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json, 131 @OpenDelimiter+1) 132 --is there an intervening open-delimiter of either type 133 SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%', 134 RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object 135 IF @NextOpenDelimiter=0 136 BREAK 137 SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter 138 IF @NextCloseDelimiter<@NextOpenDelimiter 139 BREAK 140 IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{' 141 SELECT @NextCloseDelimiterChar='}', @type='object' 142 ELSE 143 SELECT @NextCloseDelimiterChar=']', @type='array' 144 SELECT @OpenDelimiter=@NextOpenDelimiter 145 END 146 ---and parse out the list or name/value pairs 147 SELECT 148 @contents=SUBSTRING(@json, @OpenDelimiter+1, 149 @NextCloseDelimiter-@OpenDelimiter-1) 150 SELECT 151 @JSON=STUFF(@json, @OpenDelimiter, 152 @NextCloseDelimiter-@OpenDelimiter+1, 153 '@'+@type+CONVERT(NVARCHAR(5), @parent_ID)) 154 WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0 155 BEGIN 156 IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null 157 BEGIN 158 SELECT 159 @SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name. 160 SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA 161 SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1), 162 @endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin), 163 @param=RIGHT(@token, LEN(@token)-@endofname+1) 164 SELECT 165 @token=LEFT(@token, @endofname-1), 166 @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1) 167 SELECT @name=stringvalue FROM @strings 168 WHERE string_id=@param --fetch the name 169 END 170 ELSE 171 SELECT @Name=null,@SequenceNo=@SequenceNo+1 172 SELECT 173 @end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null 174 IF @end=0 175 SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ' collate SQL_Latin1_General_CP850_Bin) 176 +1 177 SELECT 178 @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin) 179 --select @start,@end, LEN(@contents+'|'), @contents 180 SELECT 181 @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)), 182 @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end) 183 IF SUBSTRING(@value, 1, 7)='@object' 184 INSERT INTO @hierarchy 185 (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) 186 SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5), 187 SUBSTRING(@value, 8, 5), 'object' 188 ELSE 189 IF SUBSTRING(@value, 1, 6)='@array' 190 INSERT INTO @hierarchy 191 (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) 192 SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5), 193 SUBSTRING(@value, 7, 5), 'array' 194 ELSE 195 IF SUBSTRING(@value, 1, 7)='@string' 196 INSERT INTO @hierarchy 197 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 198 SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string' 199 FROM @strings 200 WHERE string_id=SUBSTRING(@value, 8, 5) 201 ELSE 202 IF @value IN ('true', 'false') 203 INSERT INTO @hierarchy 204 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 205 SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean' 206 ELSE 207 IF @value='null' 208 INSERT INTO @hierarchy 209 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 210 SELECT @name, @SequenceNo, @parent_ID, @value, 'null' 211 ELSE 212 IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0 213 INSERT INTO @hierarchy 214 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 215 SELECT @name, @SequenceNo, @parent_ID, @value, 'real' 216 ELSE 217 INSERT INTO @hierarchy 218 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 219 SELECT @name, @SequenceNo, @parent_ID, @value, 'int' 220 if @Contents=' ' Select @SequenceNo=0 221 END 222 END 223 INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) 224 SELECT '-',1, NULL, '', @parent_id-1, @type 225 -- 226 RETURN 227 END 228 229 230 231 232 233 GO
2.存储过程调用
declare @UID nvarchar(32)
select @UID=StringValue from parseJSON(@JSONSTRING) where name='UID'
