json - 在 SQL Server 2016 中将 JSON 转换为表

Question

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

最初的实现发生在 SQL Server 2012(不支持 JSON，因此我们实现了一个处理解析的存储函数)，现在我们正在移动到 2016(支持 JSON)。

到目前为止，我们显着减少了处理时间(在某些情况下，速度提高了 200 倍以上!)。

有一些交互包含需要转换为表的数组。为了实现这一点，OPENJSON功能确实几乎我们需要的。

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

    [{
        "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也是一个数组。

运行命令时:

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即使是数据在那里(至少在数组的第一个元素中)。

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

Answer 1

我找到了一个 Microsoft 页面，它实际上解决了这个问题。

查询应如下所示:

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) .

确实很简单!!!