1 CREATE FUNCTION [dbo].[parseJSON] ( @JSON NVARCHAR(MAX) ) 2 RETURNS @hierarchy TABLE 3 ( 4 element_id INT IDENTITY(1, 1) 5 NOT NULL , /* internal surrogate primary key gives the order of parsing and the list order */ 6 sequenceNo [INT] NULL , /* the place in the sequence for the element */ 7 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 */ 8 OBJECT_ID INT ,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ 9 NAME NVARCHAR(2000) ,/* the name of the object */ 10 StringValue NVARCHAR(MAX) NOT NULL ,/*the string representation of the value of the element. */ 11 ValueType VARCHAR(10) NOT NULL /* the declared type of the value represented as a string in StringValue*/ 12 ) 13 AS 14 BEGIN 15 16 DECLARE @FirstObject INT , --the index of the first open bracket found in the JSON string 17 @OpenDelimiter INT ,--the index of the next open bracket found in the JSON string 18 @NextOpenDelimiter INT ,--the index of subsequent open bracket found in the JSON string 19 @NextCloseDelimiter INT ,--the index of subsequent close bracket found in the JSON string 20 @Type NVARCHAR(10) ,--whether it denotes an object or an array 21 @NextCloseDelimiterChar CHAR(1) ,--either a '}' or a ']' 22 @Contents NVARCHAR(MAX) , --the unparsed contents of the bracketed expression 23 @Start INT , --index of the start of the token that you are parsing 24 @end INT ,--index of the end of the token that you are parsing 25 @param INT ,--the parameter at the end of the next Object/Array token 26 @EndOfName INT ,--the index of the start of the parameter at end of Object/Array token 27 @token NVARCHAR(200) ,--either a string or object 28 @value NVARCHAR(MAX) , -- the value as a string 29 @SequenceNo INT , -- the sequence number within a list 30 @name NVARCHAR(200) , --the name as a string 31 @parent_ID INT ,--the next parent ID to allocate 32 @lenJSON INT ,--the current length of the JSON String 33 @characters NCHAR(36) ,--used to convert hex to decimal 34 @result BIGINT ,--the value of the hex symbol being parsed 35 @index SMALLINT ,--used for parsing the hex value 36 @Escape INT; --the index of the next escape character 37 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 38 tokens representing the string */ 39 ( 40 String_ID INT IDENTITY(1, 1) , 41 StringValue NVARCHAR(MAX) 42 ); 43 SELECT--initialise the characters to convert hex to ascii 44 @characters = '0123456789abcdefghijklmnopqrstuvwxyz' , 45 @SequenceNo = 0 , --set the sequence no. to something sensible. 46 /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */ 47 @parent_ID = 0; 48 WHILE 1 = 1 --forever until there is nothing more to do 49 BEGIN 50 SELECT @Start = PATINDEX('%[^a-zA-Z]["]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN);--next delimited string 51 IF @Start = 0 52 BREAK; --no more so drop through the WHILE loop 53 IF SUBSTRING(@JSON, @Start + 1, 1) = '"' 54 BEGIN --Delimited Name 55 SET @Start = @Start + 1; 56 SET @end = PATINDEX('%[^]["]%', RIGHT(@JSON, LEN(@JSON + '|') - @Start)); 57 END; 58 IF @end = 0 --no end delimiter to last string 59 BREAK; --no more 60 SELECT @token = SUBSTRING(@JSON, @Start + 1, @end - 1); 61 --now put in the escaped control characters 62 SELECT @token = REPLACE(@token, FromString, ToString) 63 FROM ( SELECT '"' AS FromString , 64 '"' AS ToString 65 UNION ALL 66 SELECT '\' , 67 '' 68 UNION ALL 69 SELECT '/' , 70 '/' 71 UNION ALL 72 SELECT '' , 73 CHAR(08) 74 UNION ALL 75 SELECT 'f' , 76 CHAR(12) 77 UNION ALL 78 SELECT ' ' , 79 CHAR(10) 80 UNION ALL 81 SELECT ' ' , 82 CHAR(13) 83 UNION ALL 84 SELECT ' ' , 85 CHAR(09) 86 ) substitutions; 87 88 SELECT @result = 0 , 89 @Escape = 1; 90 91 --Begin to take out any hex escape codes 92 93 WHILE @Escape > 0 94 BEGIN 95 96 SELECT @index = 0 , 97 98 --find the next hex escape sequence 99 @Escape = PATINDEX('%x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token); 100 101 IF @Escape > 0 --if there is one 102 BEGIN 103 104 WHILE @index < 4 --there are always four digits to a x sequence 105 BEGIN 106 107 SELECT --determine its value 108 @result = @result + POWER(16, @index) * ( CHARINDEX(SUBSTRING(@token, @Escape + 2 + 3 - @index, 1), @characters) 109 - 1 ) , 110 @index = @index + 1; 111 112 113 114 END; 115 116 -- and replace the hex sequence by its unicode value 117 118 SELECT @token = STUFF(@token, @Escape, 6, NCHAR(@result)); 119 120 END; 121 122 END; 123 124 --now store the string away 125 126 INSERT INTO @Strings 127 ( StringValue ) 128 SELECT @token; 129 130 -- and replace the string with a token 131 132 SELECT @JSON = STUFF(@JSON, @Start, @end + 1, '@string' + CONVERT(NVARCHAR(5), @@identity)); 133 134 END; 135 136 -- all strings are now removed. Now we find the first leaf. 137 138 WHILE 1 = 1 --forever until there is nothing more to do 139 BEGIN 140 141 142 143 SELECT @parent_ID = @parent_ID + 1; 144 145 --find the first object or list by looking for the open bracket 146 147 SELECT @FirstObject = PATINDEX('%[{[[]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN);--object or array 148 149 IF @FirstObject = 0 150 BREAK; 151 152 IF ( SUBSTRING(@JSON, @FirstObject, 1) = '{' ) 153 SELECT @NextCloseDelimiterChar = '}' , 154 @Type = 'object'; 155 156 ELSE 157 SELECT @NextCloseDelimiterChar = ']' , 158 @Type = 'array'; 159 160 SELECT @OpenDelimiter = @FirstObject; 161 162 163 164 WHILE 1 = 1 --find the innermost object or list... 165 BEGIN 166 167 SELECT @lenJSON = LEN(@JSON + '|') - 1; 168 169 --find the matching close-delimiter proceeding after the open-delimiter 170 171 SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar, @JSON, @OpenDelimiter + 1); 172 173 --is there an intervening open-delimiter of either type 174 175 SELECT @NextOpenDelimiter = PATINDEX('%[{[[]%', RIGHT(@JSON, @lenJSON - @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_BIN);--object 176 177 IF @NextOpenDelimiter = 0 178 BREAK; 179 SELECT @NextOpenDelimiter = @NextOpenDelimiter + @OpenDelimiter; 180 IF @NextCloseDelimiter < @NextOpenDelimiter 181 BREAK; 182 IF SUBSTRING(@JSON, @NextOpenDelimiter, 1) = '{' 183 SELECT @NextCloseDelimiterChar = '}' , 184 @Type = 'object'; 185 ELSE 186 SELECT @NextCloseDelimiterChar = ']' , 187 @Type = 'array'; 188 SELECT @OpenDelimiter = @NextOpenDelimiter; 189 END; 190 ---and parse out the list or name/value pairs 191 SELECT @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1, @NextCloseDelimiter - @OpenDelimiter - 1); 192 SELECT @JSON = STUFF(@JSON, @OpenDelimiter, @NextCloseDelimiter - @OpenDelimiter + 1, '@' + @Type + CONVERT(NVARCHAR(5), @parent_ID)); 193 WHILE ( PATINDEX('%[A-Za-z0-9@+.e]%', @Contents COLLATE SQL_Latin1_General_CP850_BIN) ) <> 0 194 BEGIN 195 IF @Type = 'Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null 196 BEGIN 197 SELECT @SequenceNo = 0 , 198 @end = CHARINDEX(':', ' ' + @Contents);--if there is anything, it will be a string-based name. 199 SELECT @Start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @Contents);--AAAAAAAA 200 SELECT @token = SUBSTRING(' ' + @Contents, @Start + 1, @end - @Start - 1) , 201 @EndOfName = PATINDEX('%[0-9]%', @token COLLATE SQL_Latin1_General_CP850_BIN) , 202 @param = RIGHT(@token, LEN(@token) - @EndOfName + 1); 203 204 SELECT @token = LEFT(@token, @EndOfName - 1) , 205 @Contents = RIGHT(' ' + @Contents, LEN(' ' + @Contents + '|') - @end - 1); 206 207 SELECT @name = StringValue 208 FROM @Strings 209 WHERE String_ID = @param; --fetch the name 210 211 END; 212 213 ELSE 214 SELECT @name = NULL , 215 @SequenceNo = @SequenceNo + 1; 216 217 SELECT @end = CHARINDEX(',', @Contents);-- a string-token, object-token, list-token, number,boolean, or null 218 219 IF @end = 0 220 SELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents + ' ') + 1; 221 222 SELECT @Start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @Contents); 223 224 --select @start,@end, LEN(@contents+'|'), @contents 225 226 SELECT @value = RTRIM(SUBSTRING(@Contents, @Start, @end - @Start)) , 227 @Contents = RIGHT(@Contents + ' ', LEN(@Contents + '|') - @end); 228 229 IF SUBSTRING(@value, 1, 7) = '@object' 230 INSERT INTO @hierarchy 231 ( NAME , 232 sequenceNo , 233 parent_ID , 234 StringValue , 235 OBJECT_ID , 236 ValueType 237 ) 238 SELECT @name , 239 @SequenceNo , 240 @parent_ID , 241 SUBSTRING(@value, 8, 5) , 242 SUBSTRING(@value, 8, 5) , 243 'object'; 244 245 ELSE 246 IF SUBSTRING(@value, 1, 6) = '@array' 247 INSERT INTO @hierarchy 248 ( NAME , 249 sequenceNo , 250 parent_ID , 251 StringValue , 252 OBJECT_ID , 253 ValueType 254 ) 255 SELECT @name , 256 @SequenceNo , 257 @parent_ID , 258 SUBSTRING(@value, 7, 5) , 259 SUBSTRING(@value, 7, 5) , 260 'array'; 261 262 ELSE 263 IF SUBSTRING(@value, 1, 7) = '@string' 264 INSERT INTO @hierarchy 265 ( NAME , 266 sequenceNo , 267 parent_ID , 268 StringValue , 269 ValueType 270 ) 271 SELECT @name , 272 @SequenceNo , 273 @parent_ID , 274 StringValue , 275 'string' 276 FROM @Strings 277 WHERE String_ID = SUBSTRING(@value, 8, 5); 278 279 ELSE 280 IF @value IN ( 'true', 'false' ) 281 INSERT INTO @hierarchy 282 ( NAME , 283 sequenceNo , 284 parent_ID , 285 StringValue , 286 ValueType 287 ) 288 SELECT @name , 289 @SequenceNo , 290 @parent_ID , 291 @value , 292 'boolean'; 293 294 ELSE 295 IF @value = 'null' 296 INSERT INTO @hierarchy 297 ( NAME , 298 sequenceNo , 299 parent_ID , 300 StringValue , 301 ValueType 302 ) 303 SELECT @name , 304 @SequenceNo , 305 @parent_ID , 306 @value , 307 'null'; 308 309 ELSE 310 IF PATINDEX('%[^0-9]%', @value COLLATE SQL_Latin1_General_CP850_BIN) > 0 311 INSERT INTO @hierarchy 312 ( NAME , 313 sequenceNo , 314 parent_ID , 315 StringValue , 316 ValueType 317 ) 318 SELECT @name , 319 @SequenceNo , 320 @parent_ID , 321 @value , 322 'real'; 323 ELSE 324 INSERT INTO @hierarchy 325 ( NAME , 326 sequenceNo , 327 parent_ID , 328 StringValue , 329 ValueType 330 ) 331 SELECT @name , 332 @SequenceNo , 333 @parent_ID , 334 @value , 335 'int'; 336 IF @Contents = ' ' 337 SELECT @SequenceNo = 0; 338 END; 339 END; 340 INSERT INTO @hierarchy 341 ( NAME , 342 sequenceNo , 343 parent_ID , 344 StringValue , 345 OBJECT_ID , 346 ValueType 347 ) 348 SELECT '-' , 349 1 , 350 NULL , 351 '' , 352 @parent_ID - 1 , 353 @Type; 354 RETURN; 355 END;