SQL Server implements JSON-formatted strings converted to table DataTable

In SQL Server use JSON string to save in table

Let's look at an example and see where he's strong.

SELECT * from Parsejson (' {'):  {     "firstName": "John",     "LastName": "Smith", "Age     "     : "Address":     {        "streetaddress": "2nd Street",        "City": "New York",        "state": "NY",        "PostalCode ":" 10021 "     },     " Phonenumbers ":     {        " home ":" 212 555-1234 ",        " Fax ":" 646 555-4567 "     }  }}‘)

Results show:

Parsejson Source:

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 and then it's in this column. The document is the ultimate parent and so can get the structure from recursing from the document */OBJECT_ID int,/* E Ach list or object has an object ID. This ties any elements to a parent. Lists is treated as objects here */name NVARCHAR (+),/* The name of the object */StringValue NVARCHAR (MAX) not NU Ll,/*the string representation of the value of the element.  */ValueType VARCHAR (+) NOT NULL/* The declared type of the value represented as a string in stringvalue*/) Asbegin DECLARE @FirstObject int,--the index of the first open bracket found in the JSON string @OpenDelimiter int,--the Dex of the next open bracket Found in the JSON string @NextOpenDelimiter INT,--The index of subsequent open bracket found in the JSON string @Ne Xtclosedelimiter INT,--The index of subsequent close bracket found in the JSON string @Type NVARCHAR (Ten),--whether it D Enotes 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 is parsing @en D INT,--Index of the end of the token that is parsing @param INT,--The parameter at the end of the next Object/arr Ay 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 W  Ithin a list @name NVARCHAR,--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,--used to convert hex to decimal @result BIGINT,--The value of the hex sy Mbol being parsed @index SMALLINT,--used for parsing the hex value @Escape INT--the index of the next Escape Charac  ter DECLARE @Strings Table/* In this temporary TABLE we keep all Strings, even the names of the elements, since they Is ' escaped ' in a different-to, and may contain, unescaped, brackets denoting objects or lists. These is replaced in the JSON string by tokens representing the string */(string_id INT IDENTITY (1, 1), Stri Ngvalue NVARCHAR (MAX)) select--initialise the characters to convert hex to ASCII @characters = ' 0123456789abcdefghij Klmnopqrstuvwxyz ', @SequenceNo =0,--set the sequence No.  to something sensible. /* Firstly we process all strings. This is do because [{} and] aren ' t escaped in strings, which complicates an iterative parse.  */@parent_ID = 0;  While 1=1--forever until there are nothing more  BEGIN SELECT @start =patindex ('%[^a-za-z]["]% ', @json collate sql_latin1_general_cp850_bin);--next Delimited String if @start =0 break--no + so drop through the while loop IF SUBSTRING (@json, @start +1, 1) = ' ' Is          GIN--delimited Name SET @[email protected]+1; SET @end =patindex ('%[^\][']% ', right (@json, LEN (@json + ' | ') [email protected])        Collate sql_latin1_general_cp850_bin); End IF @end =0--no end delimiter to the 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) FRO M (select ' \ ' ' as fromstring, ' ' as ToString union ALL select ' \ \ ', ' \ ' UNION ALL Select ' \ \ ', '/' union ALL Select ' \b ', char (n) UNION ALL SELECT ' \f ', char (+) union ALL select ' \ nthe ', C HAR (Ten) union All SELECT ' \ R ', CHAR UNION ALL SeleCT ' t ', CHAR (in)) substitutions SELECT @result =0, @escape =1--begin to take out any hex escape codes W Hile @escape >0 BEGIN SELECT @index =0,--find the next hex escape sequence @escape =pat INDEX ('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]% ', @token collate sql_latin1_general_cp850_bin) IF @escape >0--if t                  Here's one BEGIN while @index <4--there was always four digits to a \x sequence                    BEGIN SELECT--determine its value @[email protected]+power (@index) * (CHARINDEX (SUBSTRING (@token, @[email protected), 1), @characters)-1), @[                        email protected]+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 is now removed.   Now we find the first leaf. While 1=1--forever until there are nothing more to do BEGIN SELECT @[email protected]_id+1--find the first obje CT 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 @NextCloseDelimite Rchar= '} ', @type = ' object ' ELSE SELECT @NextCloseDelimiterChar = '] ', @type = ' array ' select @[email protected] WHI    LE 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 =charind EX (@NextCloseDelimiterChar, @json,                                      @OpenDelimiter + 1)--is there an intervening open-delimiter of either type Sele CT @NextOpenDelimiter =patindex ('%[{[[]% ', right (@json, @[email protected]) collate sql_latin1_general_cp85  0_bin)--object if @NextOpenDelimiter =0 break SELECT @[email protected][email protected] If @NextCloseDelimiter < @NextOpenDelimiter break IF SUBSTRING (@json, @NextOpenDelimiter, 1) = ' {' SELECT  @NextCloseDelimiterChar = '} ', @type = ' object ' ELSE Select @NextCloseDelimiterChar = '] ', @type = ' array ' Select @[email protected] END---and parse out the list or name/value pairs SELECT @contents =substring (@json, @Open                Delimiter+1, @[email protected]) SELECT @JSON =stuff (@json, @OpenDelimiter, @[email protected]+1, ' @ ' [Email protected]+convert (NVARCHAR (5), @parent_ID)) while (PATINDEX ('%[ [Email protected]+.e]% ', @contents collate sql_latin1_general_cp850_bin)) <>0 BEGIN IF @Type = ' Object '--it would be a 0-n list co Ntaining a string followed by a string, Number,boolean, or null BEGIN SELECT @SequenceNo =0, @end          =charindex (': ', ' [email protected])--if There is anything, it'll be a string-based name. SELECT @start =patindex ('%[^[email protected]][@]% ', ' [email protected] collate sql_latin1_general_cp850            _bin)--aaaaaaaa SELECT @token =substring ("[email protected], @start +1, @[email protected]), @endofname =patindex ('%[0-9]% ', @token collate sql_latin1_general_cp850_bin), @param =right (@token, LEN (@token) [ email protected]+1) SELECT @token =left (@token, @endofname-1), @Contents =right ("[Emai L protected], LEN (' [email protected]+ ' | ')          [email protected]) SELECT @name =stringvalue from @strings WHERE [email protected]--fetch The name END ELSE Select @Name =null,@[email protected]+1 Select @end =charindex ( ', ', @contents)--a String-token, Object-token, List-token, Number,boolean, or null IF @end =0 SELECT @end =pat INDEX ('%[[email protected]+.e][^[email protected]+.e]% ', @Contents + ' collate sql_latin1_general_cp850_ Bin) +1 SELECT @start =patindex ('%[^[email protected]+.e][[email protected]+.e]% ', ' [Emai L protected] Collate sql_latin1_general_cp850_bin)--select @start, @end, LEN (@contents + ' | '), @contents SEL ECT @Value =rtrim (SUBSTRING (@contents, @start, @[email protected]), @Contents =right (@contents + ", LEN ( @contents + ' | ')      [email protected]) IF SUBSTRING (@value, 1, 7) = ' @object ' INSERT into @hierarchy (NAME, Sequenceno, parent_id, StringValue, OBJ ect_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 @stri              NGS WHERE string_id=substring (@value, 8, 5) ELSE IF @value in (' true ', ' false ') INSERT into @hierarchy (NAME, Sequenceno, parent_id, StringValue, ValueType) SELECT @nam E, @SequenceNo, @parent_ID, @value, ' boolean ' ELSE IF @value = ' null ' INSERT into @hi Erarchy (NAME, Sequenceno, parent_id, StringValue, VaLuetype) SELECT @name, @SequenceNo, @parent_ID, @value, ' null ' ELSE IF patind                    EX ('%[^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 Endinsert into @hi   Erarchy (NAME, Sequenceno, parent_id, StringValue, object_id, ValueType) SELECT '-', 1, NULL, ', @parent_id-1, @type-- Returnendgo

　　

You can also turn a DataTable into a JSON string

SQL Server implements JSON-formatted strings converted to table DataTable