SQL Server 2016以前のサーバでJSONを解析するためのSQL関数

はじめに

SQL Server 2016以降ではTransact-SQL「OpenJson」関数が用意されており、気軽にJSONデータを解析して処理することができるようになりました。

しかし、SQL Server 2016以前ではOpenJsonのような関数が用意されていないため、自前で関数を作成して利用するしか方法はありません。

そこで、今回は、JSONを解析するための独自関数を作成します。

使い方

使い方はシンプルです、

下記のようなJsonデータを用意します。

{
"CustomerID": "Sample",
"CustomerName": "Tanaka",
"Age": 55,
"OrderIDList": [111,222,333,444,555]
}

実際に呼び出してみます。

DECLARE @JSON NVARCHAR(MAX);
SET @JSON = '{"CustomerID":"Sample","CustomerName":"Tanaka","Age":55, "Flag":true, OrderIDList":[111,222,333,444]}'
SELECT * FROM parseJSON(@JSON) ORDER BY PARENT_ID

実行結果は下記の通りです。

JSON解析関数「parseJSON」

では実際にparseJSON関数を用意します。

CREATE FUNCTION [dbo].[parseJSON](@Json NVARCHAR(MAX))

RETURNS @hierarchy TABLE
(
    ELEMENT_ID INT IDENTITY(1,1) NOt NULL,
    SEQ_NO INT NULL,
    PARENT_ID INT,
    OBJECT_ID INT,
    NAME NVARCHAR(2000)
    STRING_VALUE NVARCHAR(MAX) NOT NULL,
    VALUE_TYPE VARCHAr(10) NOT NULL,
)
AS
BEGIN
    DECLARE
        @FirstObject INT,
        @OpenDelimiter INT,
        @NextOpenDelimiter INT,
        @NextCloseDelimiter INT,
        @Type NVARCHAR(MAX),
        @NextCloseDelimiterChar CHAR(1),
        @Contents MVARCJAR(MAX),
        @Start INT,
        @End INT,
        @Param INT,
        @EndOfName INT,
        @Token NVARCHAR(200),
        @Value NVARCHAR(MAX),
        @SequenceNo INT,
        @Name NVARCHAR(200),
        @Parent_Id INT,
        @LenJSON INT,
        @Characters NCHAR(36),
        @Result BIGINT,
        @Index SMALLINT,
        @Excape INT

    DECLARE @Strings TABLE
    (
        STRING_ID INT IDENTITY(1,1),
        STRING_VALUE NVARCHAR(MAX)
    )
    SELECT
        @Characters='0123456789abcdefghijklmnopqrstuvwxyz',
        @SequenceNo=0,
        @Parent_Id=0;
    WHILE 1=1
    BEGIN
        SELECT
            @Start=PATINDEX('%[^a-zA-Z]["]%', @Json collate SQL_Latin1_General_CP850_Bin);
        IF @Start=0 BREAK
        IF SUBSTRING(@Json, @Start+1, 1)='"'
            BEGIN
                SET @Start=@Start+1;
                SET @end=PATINDEX('%[^\]["]%', ROGJT(@Json, LEN(@Json+'|')-@Start) collate SQL_Latin1_General_CP850_Bin);
            END
        IF @End=0 BREAK
        SELECT @Token=SUBSTRING(@Json, @Start+1, @End-1)
        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

        WHILE @Escape>0
        BEGIN
            SELECT @Index=0, @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
            BEGIN
                WHILE @Index<4
                BEGIN
                    SELECT @Result=@Result+POWER(16, @Index)*(CHARINDEX(SUBSTRING(@Token, @Escape+2+3-@Index, 1), @Characters)-1), @Index=@Index+1;
                END
                SELECT @Token=STUFF(@Token, @Escape, 6, NVAHR(@Result))
            END
        END

        INSERT INTO @Strings (STRING_VALUE) SELECT @Token
        SELECT @Json=STUFF(@Json, @Start, @End+1, '@String'+CONVERT(NVARCHAR(5), @@identity))
    END

    WHILE 1=1
    BEGIN
        SELECT @Parent_Id=@Parent_Id+1
        SELECT @FirstObject=PATINDEX('%[{[[]%', @Json collate SQL_Latin1_General_CP850_Bin)
        IF @FirstObject=0 BREAK
        IF (SUBSTRING(@Json, @FirstObject, 1)='{')
            SELECT @NextCloseDelimiterChar='}'. @type='object'
        ELSE
            SELECT @NextCloseDelimiterChar=']', @type='array'
        SELECT @OpenDelimiter=@FirstObject

        WHILE 1=1
        BEGIN
            SELECT @lenJson=LEN(@Json+'|')-1
            SELECT @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @Json, @OpenDelimiter+1)
            SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%', RIGHT(@Json, @LenJson-@OpenDelmiter) collate SQL_Latin1_General_CP850_Bin)
            IF @NextOpenDelimiter=0 BREAK
            SELECT @NextOpenDelmiter=@NextOpenDelimiter+@OpenDelimiter
            IF @NextCloseDelimiter < @NextOpenDelimiter BTEAK
            IF SUBSTRING(@Json, @NextOpenDelimiter, 1)='{'
                SELECT @NextCloseDelimiterChar='}', @Type='object'
            ELSE
                SELECT @NextCloseDelimiterChar=']', @Type='array'
            SELECT @OpenDelimiter=@NextOpenDelmiter
        END

        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'
                BEGIN
                    SELECT @SequenceNo=0, @End=CHARINDEX(':', ' ' + @Contents)
                    SELECT @Start=PATINDEX('%[^A-Za-z@][@]', ' ' + @Contents collate SQL_Latin1_General_CP850_Bin)
                    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=STRING_VALUE FROM @Strings WHERE STRING_ID=@Param
                END
            ELSE
                SELECT @Name=null, @SequenceNo=@SequenceNo+1
            SELECT @End=CHARINDEX(',', @Contents)

            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 
                @Value=RTRIM(SUBSTRING(@Contents, @Start, @End - @Start)),
                @Contents=RIGHT(@Contents + ' ', LEN(@Contents + '|') - @End)

            IF SUBSTRING(@Value, 1, 7) = '@object'
                INSERT INTO @hierarchy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, OBJECT_ID, VALUE_TYPE) SELECT @Name, @SequenceNo, @Parent_Id, SUBSTRING(@Value, 8, 5), SUBSTRING(@Value, 8, 5), 'object'
            ELSE
                IF SUBSTRING(@Value, 1, 6) = '@array'
                    INSERT INTO @hierarcy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, OBJECT_ID, VALUE_TYPE) 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, SEQ_NO, PARENT_ID, STRING_VALUE, VALUE_TYPE) SELECT @Name, @SequenceNo, @Parent_Id, STRING_VALUE, 'string' FROM @Strings WHERE STRING_ID=SUBSTRING(@Value, 8, 5)
                    ELSE
                        IF @Value IN ('true', 'false')
                            INSERT INTO @hierarchy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, VALUE_TYPE) SELECT @Name, @SequenceNo, @Parent_Id, @Value, 'boolean'
                        ELSE
                            IF @Value='null'
                                INSERT INTO @hierarchy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, VALUE_TYPE) SELECT @Name, @SequenceNo, @Parent_Id, @Value, 'null'
                            ELSE
                                IF PATINDEX('%[^0-9]%', @Value collate SQL_Latin1_General_CP850_Bin) > 0
                                    INSERT INTO @hierarchy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, VALUE_TYPE) SELECT @Name, @SequenceNo, @Parent_Id, @Value, 'real'
                                ELSE
                                    INSERT INTO @hierarchy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, VALUE_TYPE) SELECT @Name, @SequenceNo, @Parent_Id, @Value, 'int'
            IF @Contents=' ' SELECT @SequenceNo = 0
        END
    END
    INSERT INTO @hierarchy (NAME, SEQ_NO, PARENT_ID, STRING_VALUE, OBJECT_ID,  VALUE_TYPE) SELECT '-', 1, NULL, '', @Parent_Id - 1, @Type
    RETURN
END     

Reference

最後に

SQLServer 2016以前を利用している場合でもJSONデータを処理することは多々あるかと思います。

そういう時に利用できれば良いかと思います。