SQL JSON into a table

ALTER 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 sequence for the element */
parent_id int,/* If the element has a parent 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,/* Each list or object have 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 null,/*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*/
)
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,--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 is parsing
@end INT,--Index of the end of the token that is 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 ($),--either a string or object
@value NVARCHAR (MAX),--the value as a string
@SequenceNo int,--the sequence number within 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 symbol being parsed
@index SMALLINT,--used for parsing the hex value
@Escape INT--the Index of the next Escape character


DECLARE @Strings Table/* Temporary table We keep all Strings, even the names of the elements, since they is ' ESC Aped ' 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),
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 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) = ' "'
BEGIN--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)
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 collate sql_latin1_general_cp850_bin)
IF @escape >0--if there is one
BEGIN
While @index <4--there is 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
BEGIN

SELECT @[email protected]_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 @[email protected]

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, @[email protected]) collate Sql_latin1_general_cp850_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, @OpenDelimiter +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 containing 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 (' [email 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 =patindex ('%[[email protected]+.e][^[email protected]+.e]% ', @Contents + ' collate Sql_latin1_general_ Cp850_bin)
+1
SELECT
@start =patindex ('%[^[email protected]+.e][[email protected]+.e]% ', ' [email protected] collate Sql_latin1_general_ Cp850_bin)
--select @start, @end, LEN (@contents + ' | '), @contents
SELECT
@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, 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

SQL JSON into a table