[sql] Parse json in tsql

Funzione sql server per effettuare il parse di un campo contenente una stringa in formato json

IF OBJECT_ID (N'dbo.parseJSON') IS NOT NULL

   DROP FUNCTION dbo.parseJSON

GO

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 */

   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 */

   Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */

   NAME NVARCHAR(2000),/* the name of the object */

   StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */

   ValueType VARCHAR(10) 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(10),--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 you are parsing

    @end INT,--index of the end of the token that you are 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(200),--either a string or object

    @value NVARCHAR(MAX), -- the value as a string

    @name NVARCHAR(200), --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(36),--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 /* 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 */

    (

     String_ID INT IDENTITY(1, 1),

     StringValue NVARCHAR(MAX)

    )

  SELECT--initialise the characters to convert hex to ascii

    @characters='0123456789abcdefghijklmnopqrstuvwxyz',

  /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */

    @parent_ID=0;

  WHILE 1=1 --forever until there is nothing more to do

    BEGIN

      SELECT

        @start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string

      IF @start=0 BREAK --no more so drop through the WHILE loop

      IF SUBSTRING(@json, @start+1, 1)='"'

        BEGIN --Delimited Name

          SET @start=@Start+1;

          SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);

        END

      IF @end=0 --no end delimiter to 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 are always four digits to a \x sequence  

                BEGIN

                  SELECT --determine its value

                    @result=@result+POWER(16, @index)

                    *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),

                                @characters)-1), @index=@index+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 are now removed. Now we find the first leaf. 

  WHILE 1=1  --forever until there is nothing more to do

  BEGIN

 

  SELECT @parent_ID=@parent_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 @OpenDelimiter=@firstObject

 

  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, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object

      IF @NextOpenDelimiter=0

        BREAK

      SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter

      IF @NextCloseDelimiter<@NextOpenDelimiter

        BREAK

      IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'

        SELECT @NextCloseDelimiterChar='}', @type='object'

      ELSE

        SELECT @NextCloseDelimiterChar=']', @type='array'

      SELECT @OpenDelimiter=@NextOpenDelimiter

    END

  ---and parse out the list or name/value pairs

  SELECT

    @contents=SUBSTRING(@json, @OpenDelimiter+1,

                        @NextCloseDelimiter-@OpenDelimiter-1)

  SELECT

    @JSON=STUFF(@json, @OpenDelimiter,

                @NextCloseDelimiter-@OpenDelimiter+1,

                '@'+@type+CONVERT(NVARCHAR(5), @parent_ID))

  WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0

    BEGIN

      IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null

        BEGIN

          SELECT

            @end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name.

          SELECT  @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA

          SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),

            @endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),

            @param=RIGHT(@token, LEN(@token)-@endofname+1)

          SELECT

            @token=LEFT(@token, @endofname-1),

            @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)

          SELECT  @name=stringvalue FROM @strings

            WHERE string_id=@param --fetch the name

        END

      ELSE

        SELECT @Name=null 

      SELECT

        @end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null

      IF @end=0

        SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ' collate SQL_Latin1_General_CP850_Bin)

          +1

      SELECT

        @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)

      --select @start,@end, LEN(@contents+'|'), @contents 

      SELECT

        @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),

        @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)

      IF SUBSTRING(@value, 1, 7)='@object'

        INSERT INTO @hierarchy

          (NAME, parent_ID, StringValue, Object_ID, ValueType)

          SELECT @name, @parent_ID, SUBSTRING(@value, 8, 5),

            SUBSTRING(@value, 8, 5), 'object'

      ELSE

        IF SUBSTRING(@value, 1, 6)='@array'

          INSERT INTO @hierarchy

            (NAME, parent_ID, StringValue, Object_ID, ValueType)

            SELECT @name, @parent_ID, SUBSTRING(@value, 7, 5),

              SUBSTRING(@value, 7, 5), 'array'

        ELSE

          IF SUBSTRING(@value, 1, 7)='@string'

            INSERT INTO @hierarchy

              (NAME, parent_ID, StringValue, ValueType)

              SELECT @name, @parent_ID, stringvalue, 'string'

              FROM @strings

              WHERE string_id=SUBSTRING(@value, 8, 5)

          ELSE

            IF @value IN ('true', 'false')

              INSERT INTO @hierarchy

                (NAME, parent_ID, StringValue, ValueType)

                SELECT @name, @parent_ID, @value, 'boolean'

            ELSE

              IF @value='null'

                INSERT INTO @hierarchy

                  (NAME, parent_ID, StringValue, ValueType)

                  SELECT @name, @parent_ID, @value, 'null'

              ELSE

                IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0

                  INSERT INTO @hierarchy

                    (NAME, parent_ID, StringValue, ValueType)

                    SELECT @name, @parent_ID, @value, 'real'

                ELSE

                  INSERT INTO @hierarchy

                    (NAME, parent_ID, StringValue, ValueType)

                    SELECT @name, @parent_ID, @value, 'int'

 

    END

  END

INSERT INTO @hierarchy (NAME, parent_ID, StringValue, Object_ID, ValueType)

  SELECT '-', NULL, '', @parent_id-1, @type

--

   RETURN

END

GO

posted @ venerdì 20 aprile 2012 22:06

Print

Comments on this entry:

# re: [sql] Parse json in tsql

Left by iren at 26/06/2012 12:27
Gravatar
Gratitude To be grateful アバクロ means you are thankful for and appreciative of what you have and where you are on your path right now. Gratitude fills your heart with the joyful feeling and allows you to fully appreciate everything that arises on your path. アバクロ 通販 As you strive to keep your focus on the present moment, you can experience the full wonder of "here." There are many ways to cultivate gratitude. Here are just a few suggestions you may wish to try: Imagine what your life would be like if you lost all that you had. This will most surely remind you of how much you do アバクロ 激安 appreciate it.
Make a list each day of all that you are grateful for, アバクロ 新作 so that you can stay conscious daily of your blessings. Do this especially when you are feeling as though you have nothing to feel grateful for. Or spend a few minutes before you go to sleep giving thanks for all that you have アバクロ 日本.

# re: [sql] Parse json in tsql

Left by yamail at 17/11/2012 11:44
Gravatar

# re: [sql] Parse json in tsql

Left by yamail at 17/11/2012 11:49
Gravatar

# re: [sql] Parse json in tsql

Left by private investigator at 28/11/2017 09:22
Gravatar
We are a London based company. Our services include, surveillance, car tracking, background reports and investigations.

# re: [sql] Parse json in tsql

Left by skip hire dudley at 18/01/2018 15:12
Gravatar
ECL Skips provide skip hire in Dudley and the surrounding area such as Wolverhampton, Walsall, Sandwell and Birmingham. We provide builders skips to roll on roll off and closed asbestos skips. We operate in the West Midlands from our Dudley Recycle Centre.

# re: [sql] Parse json in tsql

Left by Slip Trip Or Fall at 27/02/2018 08:07
Gravatar
Claim maximum compensation if you have been injured in a slip trip or fall which was not your fault. Use our professional and confidential service.

# re: [sql] Parse json in tsql

Left by Comedy Hypnotist at 04/04/2018 15:09
Gravatar
The best comendy hypnotist you can find for your next event.

# re: [sql] Parse json in tsql

Left by saro at 27/04/2018 10:27
Gravatar
My partner and i faithfulness your elaboration associated with exactly how dialogue millstone discourse processs.

# re: [sql] Parse json in tsql

Left by super bright led bulbs at 12/06/2018 12:46
Gravatar
superbrightleds amber yellow led turn signal light red brake lights white led back up light 3157 led back up light 921 led bulbs

# re: [sql] Parse json in tsql

Left by comfortable conservatories at 28/06/2018 10:31
Gravatar
A home conservatory is a great way to add some light and extra space to your home. Since a conservatory does not require any planning permission or building regulations, it is relatively easy to purchase and install. But, once you have made up your mind to get a conservatory for your home it is up to up to make sure that the conservatory is able to provide the benefits that you got it installed for in the first place. More importantly, it is important that your comfortable conservatory is used all year round.

# re: [sql] Parse json in tsql

Left by willesden taxi at 10/07/2018 10:55
Gravatar
If you're in the Willesden area, you need transport that is as flexible as your plans can be. So it's time to use Cheetah Cars, London's favourite taxi service. There are so many reasons to choose Cheetah Cars over any standard taxi in Willesden. You'll love our obvious commitment to high quality customer service. We're already receiving impressive reviews from our customers in the Willesden area.

# re: [sql] Parse json in tsql

Left by icon for website at 24/07/2018 15:23
Gravatar
Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles.
Comments have been closed on this topic.
«agosto»
domlunmarmergiovensab
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910