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 '\b' ,
73 CHAR(08)
74 UNION ALL
75 SELECT '\f' ,
76 CHAR(12)
77 UNION ALL
78 SELECT '\n' ,
79 CHAR(10)
80 UNION ALL
81 SELECT '\r' ,
82 CHAR(13)
83 UNION ALL
84 SELECT '\t' ,
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;
