将JSON转换为SQL Server 2016中的表 [英] Converting JSON to table in SQL Server 2016

问题描述

我正在处理一个Web项目，其中客户端应用程序通过JSON与DB通信.

I'm working on a Web project where the client application communicates with the DB via JSONs.

最初的实现是在SQL Server 2012上进行的(不支持JSON，因此我们实现了一个用于存储解析的存储函数)，现在我们将移至2016年(支持JSON).

The initial implementation took place with SQL Server 2012 (NO JSON support and hence we implemented a Stored Function that handled the parsing) and now we are moving to 2016 (YES JSON support).

到目前为止，我们正在将处理时间减少很多(在某些情况下，速度要快200倍！).

So far, we are reducing processing time by a significant factor (in some cases, over 200 times faster!).

有些交互包含需要转换为表的数组.为此，OPENJSON函数执行 ALMOST 我们需要的操作.

There are some interactions that contain arrays that need to be converted into tables. To achieve that, the OPENJSON function does ALMOST what we need.

在其中一些(基于数组)情况下，数组中的记录具有一个或多个也是对象的字段(在这种情况下，也是数组)，例如:

In some of these (array-based) cases, records within the arrays have one or more fields that are also OBJECTS (in this particular case, also arrays), for instance:

    [{
        "Formal_Round_Method": "Floor",
        "Public_Round_Method": "Closest",
        "Formal_Precision": "3",
        "Public_Precision": "3",
        "Formal_Significant_Digits": "3",
        "Public_Significant_Digits": "3",
        "General_Comment": [{
            "Timestamp": "2018-07-16 09:19",
            "From": "1",
            "Type": "Routine_Report",
            "Body": "[To + Media + What]: Comment 1",
            "$$hashKey": "object:1848"
        }, {
            "Timestamp": "2018-07-16 09:19",
            "From": "1",
            "Type": "User_Comment",
            "Body": "[]: Comment 2",
            "$$hashKey": "object:1857"
        }, {
            "Timestamp": "2018-07-16 09:19",
            "From": "1",
            "Type": "Routine_Report",
            "Body": "[To + Media + What]: Comment 3",
            "$$hashKey": "object:1862"
        }]
    }, {
        "Formal_Round_Method": "Floor",
        "Public_Round_Method": "Closest",
        "Formal_Precision": "3",
        "Public_Precision": "3",
        "Formal_Significant_Digits": "3",
        "Public_Significant_Digits": "3",
        "General_Comment": []

    }]

在这里，General_Comment也是一个数组.

Here, General_Comment is also an array.

运行命令时:

SELECT *
  FROM OPENJSON(@_l_Table_Data)
  WITH (    Formal_Round_Method                 NVARCHAR(16)    '$.Formal_Round_Method'               ,
            Public_Round_Method                 NVARCHAR(16)    '$.Public_Round_Method'               ,
            Formal_Precision                    INT             '$.Formal_Precision'                  ,
            Public_Precision                    INT             '$.Public_Precision'                  ,
            Formal_Significant_Digits           INT             '$.Formal_Significant_Digits'         ,
            Public_Significant_Digits           INT             '$.Public_Significant_Digits'         ,
            General_Comment                     NVARCHAR(4000)  '$.General_Comment'                   
        ) ;

[@_l_Table_Data是一个保存JSON字符串的变量]

[@_l_Table_Data is a variable holding the JSON string]

即使其中有is数据(至少在数组的第一个元素中)，我们也会获得列General_Comment = NULL.

we are getting the column General_Comment = NULL even though the is data in there (at least in the first element of the array).

我想我应该对可能包含 OBJECTS (而不是 SIMPLE VALUES (简单值))的那些列使用不同的语法，但是我不知道该语法应该是什么.

I guess that I should be using a different syntax for those columns that may contain OBJECTS and not SIMPLE VALUES, but I have no idea what that syntax should be.

推荐答案

我找到了一个实际上可以解决该问题的Microsoft页面.

I found a Microsoft page that actually solves the problem.

这是查询的外观:

SELECT *
  FROM OPENJSON(@_l_Table_Data)
  WITH (    Formal_Round_Method        NVARCHAR(16)    '$.Formal_Round_Method'               ,
            Public_Round_Method        NVARCHAR(16)    '$.Public_Round_Method'               ,
            Formal_Precision           INT             '$.Formal_Precision'                  ,
            Public_Precision           INT             '$.Public_Precision'                  ,
            Formal_Significant_Digits  INT             '$.Formal_Significant_Digits'         ,
            Public_Significant_Digits  INT             '$.Public_Significant_Digits'         ,
            General_Comment            NVARCHAR(MAX)   '$.General_Comment'   AS JSON                
    ) ;

因此，您需要在列定义的末尾添加AS JSON，并且(上帝知道为什么)类型必须必须为NVARCHAR(MAX).

So, you need to add AS JSON at the end of the column definition and (God knows why) the type MUST be NVARCHAR(MAX).

确实非常简单！

这篇关于将JSON转换为SQL Server 2016中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！