SQL Server2016中的Camel Case jsonobject数组

Question

我正在从sqlserver 2016返回JSON使用" for json auto"我的OP就像This

"[{"Id":3,"SId":"5801","Name":"Pizza","Type":"Error"}]"

我使用以下代码

进行了序列化数据

 public Someclass GetData(int Id)
    {
        using (var dataContext = new DataContext(_connectionString))
        {
               var serializer = new JsonSerializerSettings()
               {
                ContractResolver = new CamelCasePropertyNamesContractResolver()
               };
            var dataArray = dataContext.ExecuteQuery<string>(query.ToString()).SingleOrDefault();
            dynamic obj= JsonConvert.DeserializeObject<dynamic>(dataArray , serializer);
            return obj
        }
    }

在OBJ对象中，我没有得到骆驼的数据。

 "{[{"Id":3,SId":"5801","Name":"Pizza","Type":"Error"}]}" 

由于我的数据架构持有JSON结果是动态的，因此我无法使用JSON.NET将其转换为骆驼。是否有任何SQL从SQL Server 2016

Answer 1

他们希望从" select ... for json auto"中输出此JSON：

[{" id"：3，" sid"：" 5801"，"名称"：" pizza"，" type"：" error"}]

如果您直接在JavaScript中使用SQL输出，则可能会关心它不是骆驼情况。但是，在上述代码中，SQL在.NET程序中使用，并且.NET避免序列化不敏感。无需使用动态类型或与序列化器设置混乱。只需直接到SomeClass：

var someClass = JSONConvert.DeserializeObject<SomeClass>(dataArray)

请注意，您应该在C＃代码中使用C＃约定 - 因此SomeClass应该使用Pascal Case名称。如果您要从.NET WebAPI返回SomeClass，则是应该转换为骆驼盒的。在.NET核心中，这是默认情况下完成的。对于.NET框架，您可以在App Init上更改JSON Serializer设置，以执行相同的操作。

但是，如果您真的想要：

[{"id":3,"sId":"5801","name":"pizza","type":"Error"}]

如果可以的话，请修改查询本身并在您想要的任何情况下明确列出列：

select id, sId, name, type from ... for json auto

Answer 2

我写了一个存储过程，以将任意JSON转换为骆驼。它涵盖了我能想到的大多数数据类型，但是可能会有一些JSON会破坏它。我很想拥有一个功能，但是您不能在功能中进行动态SQL。

CREATE PROCEDURE [dbo].[CamelCaseJson] (
    @jsonIn nvarchar(max),
    @jsonOut nvarchar(max) output
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @isArray    BIT = 0
    DECLARE @innerCols  NVARCHAR(MAX)
    DECLARE @outerCols  NVARCHAR(MAX)
    DECLARE @sql        NVARCHAR(MAX)
    IF LEFT(@jsonIn, 1) = '['
    BEGIN
        SET @isArray = 1
    END
    CREATE TABLE #tmpData (
        [rownum]            INT IDENTITY(1, 1),
        [key]               NVARCHAR(MAX),
        [value]             NVARCHAR(MAX),
        [type]              INT,
        [processedKey]      NVARCHAR(MAX),
        [processedValue]    NVARCHAR(MAX)
    )
    DECLARE @jsons TABLE (
        [rownum]    INT,
        [json]      NVARCHAR(MAX)
    )
    INSERT #tmpData (
        [key],
        [value],
        [type],
        [processedKey],
        [processedValue]
    )
    SELECT
        [key],
        [value],
        [type],
        LOWER(SUBSTRING([key], 1, 1)) + SUBSTRING([key], 2, 999999999),
        [value]
    FROM OPENJSON(@jsonIn)  

    INSERT @jsons (
        [rownum],
        [json]
    )
    SELECT
        [rownum],
        [value]
    FROM #tmpData
    WHERE [type] in (4, 5)

    DECLARE @id         INT
    DECLARE @subJsonIn  NVARCHAR(MAX)
    DECLARE @subJsonOut NVARCHAR(MAX)
    
    WHILE EXISTS (SELECT 1 FROM @jsons)
    BEGIN
        SET @subJsonOut = NULL
        SELECT 
            TOP 1 
            @id = [rownum],
            @subJsonIn = [json]
        FROM @jsons
        EXEC [dbo].[CamelCaseJson] @subJsonIn, @subJsonOut OUTPUT
        UPDATE #tmpData
        SET [processedValue] = @subJsonOut
        WHERE [rownum] = @id
        DELETE @jsons WHERE [rownum] = @id
    end

    IF @isArray = 0
    BEGIN
        SELECT @innerCols = COALESCE(@innerCols + ', ', '') + QUOTENAME([processedKey]) FROM #tmpData
        SELECT @outerCols = 
            COALESCE(@outerCols + ', ', '') 
                +   CASE    
                        WHEN [type] = 2 AND [value] LIKE '%e%' THEN 'CAST(' + QUOTENAME([processedKey]) + ' AS FLOAT) AS ' + QUOTENAME([processedKey])
                        WHEN [type] = 2 AND [value] LIKE '%.%' THEN 'CAST(' + QUOTENAME([processedKey]) + ' AS DECIMAL(19, 7)) AS ' + QUOTENAME([processedKey])
                        WHEN [type] = 2 THEN 'CAST(' + QUOTENAME([processedKey]) + ' AS INT) AS ' + QUOTENAME([processedKey])
                        WHEN [type] = 3 THEN 'CAST(' + QUOTENAME([processedKey]) + ' AS BIT) AS ' + QUOTENAME([processedKey])
                        WHEN [type] in (4, 5) THEN 'JSON_QUERY(' + QUOTENAME([processedKey]) + ') AS ' + QUOTENAME([processedKey])
                        ELSE QUOTENAME([processedKey]) 
                    END
        FROM #tmpData 

        SET @sql = '
            WITH pv AS (
                SELECT
                    [dummy] = 1,
                    [key] = [processedKey],
                    [value] = [processedValue]
                FROM #tmpData
            )
            SELECT @dynout = (
                SELECT ' + @outerCols + '
                FROM (
                    SELECT [dummy], ' + @innerCols + '
                    FROM pv
                    PIVOT (
                        MAX([value]) FOR [key] IN (' + @innerCols + ')
                    ) pvresult
                ) x
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES
            )
        '
        EXEC sp_executesql @sql, N'@dynout NVARCHAR(MAX) OUT', @jsonOut OUTPUT
    END
    ELSE
    BEGIN
        SELECT @jsonOut = 
            COALESCE(@jsonOut + ',', '') + 
                CASE
                    WHEN [type] = 1 THEN '"' + [processedValue] + '"' 
                    ELSE [processedValue]
                END
        FROM #tmpData 
        ORDER BY [rownum]
        SET @jsonOut = '[' + @jsonOut + ']'
    END
    DROP TABLE #tmpData
END
GO

检查您的示例：

DECLARE @jsonIn     NVARCHAR(MAX)
DECLARE @jsonOut    NVARCHAR(MAX)

SET @jsonIn = (
    SELECT
        [Id] = 3,
        [SId] = '5801',
        [Name] = 'Pizza',
        [Type] = 'Error'
    FOR JSON PATH
)
EXEC [dbo].[CamelCaseJson] @jsonIn, @jsonOut OUTPUT
SELECT @jsonIn [Json]
UNION ALL
SELECT @jsonOut [Json]

给你

in [{{iD：3，sid''："：" 5801＆quot; quot''

out [{{id'; 3，sid; sid'：'：" 5801" 5801＆quot，'name; pizza; quord''type''

我的单位测试JSON（不格式化，对不起）：

{"Pnull":null,"Pstring":"Foo","Pint":1,"Pdec":1.3,"Pbool":true,"Pstrarray":["a","b"],"Pintarray":[1,3,5],"Parray":[{"Prop":"val1"},{"Prop":"val2"}],"Pjson":{"Subproperty":1},"Pjsonstring":"{"Subproperty":2}","Poffset":"2017-06-20T22:18:31.2279221-04:00","DeepObj":{"Level":2,"Sub":{"Level":3,"Sub":{"Level":4,"Sub":{"Level":"Basement"}}}},"Flt":1.3e-9}