SQLSERVER 2014 解析json

SQL Server 2014提供了解析JSON的功能。可以使用自定义函数来解析JSON并将其转换为表格形式。下面是一个示例函数来解析JSON：

CREATE FUNCTION [dbo].[parseJSON](@JSON NVARCHAR(MAX))
RETURNS @hierarchy TABLE
(
    element_id INT IDENTITY(1, 1) NOT NULL,
    parent_id INT,
    sequence_no INT,
    [key] NVARCHAR(4000),
    [value] NVARCHAR(MAX),
    level_no INT
)
AS
BEGIN
    DECLARE @level INT = 1
    DECLARE @parent_id INT = 0
    DECLARE @sequence_no INT = 1
    DECLARE @key NVARCHAR(4000), @value NVARCHAR(MAX)

    WHILE LEN(@JSON) > 0
    BEGIN
        SELECT @key = '', @value = ''

        IF LEFT(@JSON, 1) = '{'
        BEGIN
            -- Object
            SET @key = dbo.parseJSON_GetKey(@JSON)
            SET @JSON = SUBSTRING(@JSON, LEN(@key) + 2, LEN(@JSON) - LEN(@key) - 1)

            INSERT INTO @hierarchy (parent_id, sequence_no, [key], level_no)
            VALUES (@parent_id, @sequence_no, @key, @level)

            SET @parent_id = SCOPE_IDENTITY()
            SET @sequence_no = 1
            SET @level = @level + 1
        END
        ELSE IF LEFT(@JSON, 1) = '['
        BEGIN
            -- Array
            SET @JSON = SUBSTRING(@JSON, 2, LEN(@JSON) - 2)

            INSERT INTO @hierarchy (parent_id, sequence_no, level_no)
            VALUES (@parent_id, @sequence_no, @level)

            SET @parent_id = SCOPE_IDENTITY()
            SET @sequence_no = 1
            SET @level = @level + 1
        END
        ELSE IF LEFT(@JSON, 1) = '"'
        BEGIN
            -- String
            SET @value = dbo.parseJSON_GetString(@JSON)
            SET @JSON = SUBSTRING(@JSON, LEN(@value) + 3, LEN(@JSON) - LEN(@value) - 2)

            INSERT INTO @hierarchy (parent_id, sequence_no, [key], [value], level_no)
            VALUES (@parent_id, @sequence_no, @key, @value, @level)

            SET @sequence_no = @sequence_no + 1
        END
        ELSE IF ISNUMERIC(LEFT(@JSON, 1)) = 1 OR LEFT(@JSON, 1) = '-'
        BEGIN
            -- Number
            SET @value = dbo.parseJSON_GetNumber(@JSON)
            SET @JSON = SUBSTRING(@JSON, LEN(@value) + 1, LEN(@JSON) - LEN(@value))

            INSERT INTO @hierarchy (parent_id, sequence_no, [key], [value], level_no)
            VALUES (@parent_id, @sequence_no, @key, @value, @level)
            SET @sequence_no = @sequence_no + 1
        END
        ELSE IF LEFT(@JSON, 4) = 'true'
        BEGIN
            -- Boolean true
            SET @value = 'true'
            SET @JSON = SUBSTRING(@JSON, 5, LEN(@JSON) - 4)

            INSERT INTO @hierarchy (parent_id, sequence_no, [key], [value], level_no)
            VALUES (@parent_id, @sequence_no, @key, @value, @level)

            SET @sequence_no = @sequence_no + 1
        END
        ELSE IF LEFT(@JSON, 5) = 'false'
        BEGIN
            -- Boolean false
            SET @value = 'false'
            SET @JSON = SUBSTRING(@JSON, 6, LEN(@JSON) - 5)

            INSERT INTO @hierarchy (parent_id, sequence_no, [key], [value], level_no)
            VALUES (@parent_id, @sequence_no, @key, @value, @level)

            SET @sequence_no = @sequence_no + 1
        END
        ELSE IF LEFT(@JSON, 4) = 'null'
        BEGIN
            -- Null
            SET @value = 'null'
            SET @JSON = SUBSTRING(@JSON, 5, LEN(@JSON) - 4)

            INSERT INTO @hierarchy (parent_id, sequence_no, [key], [value], level_no)
            VALUES (@parent_id, @sequence_no, @key, @value, @level)

            SET @sequence_no = @sequence_no + 1
        END
        ELSE
        BEGIN
            SET @JSON = SUBSTRING(@JSON, 2, LEN(@JSON) - 1)
        END
    END

    RETURN
END

此函数将JSON解析为层次结构表格，其中包含元素ID、父ID、序列号、键、值和级别号。您可以根据需要修改函数以满足您的要求。