在 SQL Server 2016 中将 JSON 转换为表

我正在开发一个 Web 项目，其中客户端应用程序通过 JSON 与数据库进行通信。

最初的实现是在 SQL Server 2012 中进行的（不支持 JSON，因此我们实现了一个处理解析的存储函数），现在我们正在转向 2016（支持 JSON）。

到目前为止，我们已将处理时间缩短了很多（在某些情况下，速度快了 200 多倍！）。

有一些交互包含需要转换为表的数组。为了实现这一目标，OPENJSON函数确实ALMOST我们需要的。

在其中一些（基于数组的）情况下，数组中的记录具有一个或多个也是对象的字段（在这种特殊情况下，也是数组），例如：

    [{
        "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": []

    }]

Here, General_Comment也是一个数组。

运行命令时：

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 字符串的变量]

我们正在获取专栏General_Comment = NULL即使其中有数据（至少在数组的第一个元素中）。

我想我应该对那些可能包含的列使用不同的语法OBJECTS并不是简单的价值观，但我不知道该语法应该是什么。

我找到了一个微软页面，确实解决了这个问题。

查询应如下所示：

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在列定义的末尾和（天知道为什么）类型MUST be NVARCHAR(MAX).

确实很简单！！！