在過去幾年中，盡管 XML 仍然被廣泛使用，但 JSON 已將自己定位為服務之間的標準數(shù)據(jù)交換格式。在 SQL Server 2016 中，微軟直接在數(shù)據(jù)庫引擎中實現(xiàn)了 JSON 支持，數(shù)據(jù)操作的能力在每個后續(xù)版本中都在提升。

 

 

這個詞是 JavaScript Object Notation 的縮寫，它以人類可讀的鍵值對的形式表示一種開放標準格式；它是獨立于語言的。它通常用于應用程序配置、RESTful Web 服務以及 NoSQL 數(shù)據(jù)庫（如 CouchDB 和 MongoDB）。

 

流行的開發(fā)語言，包括 JavaScript，原生支持無需序列化的 JSON 的生成和使用，這在保持自我描述的同時提供了靈活性，而無需 XML 中的架構要求。

 

JSON 基礎知識

JSON 文本內容是包含符合 JSON 值語法的代碼點的令牌序列。值可以是原始值（字符串、數(shù)字、布爾值或空值）或復雜值（對象或數(shù)組）。

 

JSON對象被定義為“零個或多個”名為對象成員的鍵-值對的集合，這些鍵-值對被寫入成對的大括號中。鍵和值用一個冒號分隔，對象用逗號分隔。鍵是字符串，值可以是任何原始或復雜數(shù)據(jù)類型。JSON 數(shù)組是由逗號分隔并用方括號括起來的零個或多個值的有序列表。

 

由于 JSON 被設計為盡可能輕量級，因此它僅支持四種原始數(shù)據(jù)類型——數(shù)字（雙精度浮點數(shù)）、字符串（用雙引號括起來的 Unicode 文本）、真/假（必須以小寫形式寫入的布爾值） ) 和空值。沒有專門的“日期”類型——它們被表示為字符串。在 JSON 中，字符串是用引號包裹的序列，除轉義字符外，所有字符都必須放在其中。

 

 

 

JSON 基本結構

 

以 JSON 格式獲取 SQL Server 數(shù)據(jù)

當我們開始在 SQL Server 中使用 JSON 時，我們通常首先必須以這種格式檢索表格數(shù)據(jù)。微軟首先在 SQL Server 2017 中實現(xiàn)了一個FOR JSON 子句——這個子句可以在本地與 SELECT 語句一起使用，類似于我們用于檢索 XML 格式數(shù)據(jù)的 FOR XML。

 

FOR JSON允許有兩種方法可供選擇：

 

FOR JSON AUTO - 輸出將根據(jù) SELECT 語句結構進行格式化

FOR JSON PATH - 輸出將根據(jù)用戶定義的結構進行格式化，允許您使用嵌套對象和屬性

 

無論您選擇哪種模型，SQL Server 都將在 SELECT 語句中提取關系數(shù)據(jù)。它將自動將數(shù)據(jù)庫數(shù)據(jù)類型轉換為 JSON 類型并實現(xiàn)字符轉義規(guī)則。最后，它將根據(jù)顯式或隱式定義的格式規(guī)則來格式化輸出。

 

使用FOR JSON AUTO，輸出格式由 SELECT 語句的設計控制。因此，使用這種模式需要一個數(shù)據(jù)庫表或視圖。

 

 

我們收到以下錯誤消息：

 

消息 13600，級別 16，狀態(tài) 1，第 4 行

 

FOR JSON AUTO 至少需要一張表來生成 JSON 對象。使用 FOR JSON PATH 或添加帶有表名的 FROM 子句。

 

現(xiàn)在我們展示 SQL Server 如何自動生成 JSON 數(shù)據(jù)。首先，它在 Management Studio 中作為輸出，然后在文本編輯器中格式化：

 

 

 

原始結果集中的每一行都創(chuàng)建為平面屬性結構。如果將其與標準 XML 進行比較，您會看到更少的文本。這是因為表名沒有出現(xiàn)在 JSON 輸出中。

 

當您開始在 XML 中使用 ELEMENTS 選項而不是默認的 RAW 值時，大小的差異變得很重要。為了證明這一點，我們使用 SELECT 語句來比較 XML 和 JSON 輸出的數(shù)據(jù)長度（以字節(jié)為單位）：

 

 

 

從查詢結果中可以看出，XML 元素大小比 JSON 大小大 65% 左右。另一方面，當表示為 XML 屬性時，JSON 和 XML 大致相同。

 

使用FOR JSON AUTO的輸出是具有單級屬性的平面結構。如果這不足以滿足您的需求，您需要使用FOR JSON PATH擴展。

 

FOR JSON PATH允許您通過創(chuàng)建包裝器對象和使用復雜屬性來完全控制 JSON 輸出。最終結果顯示為 JSON 對象數(shù)組。此擴展將使用別名/列名來定義輸出中的鍵名。如果別名包含點，它將創(chuàng)建一個嵌套對象。

 

擴展前面的示例，我們希望將FirstName和LastName列顯示為新PersonName列的嵌套屬性。我們通過添加別名來執(zhí)行我們嵌套的列并使用點語法來獲得正確的輸出：

 

 

 

如果我們不更改默認設置，則結果中將不會包含 NULL 值。如果您的語句在一個查詢中連接多個表，則輸出將是一個平面列表，其中FOR JSON PATH根據(jù)定義的列別名嵌套每一列。

 

FOR JSON PATH擴展不需要數(shù)據(jù)庫表，我們可以在下面的例子中：

 

 

 

使用 JSON 進行表格數(shù)據(jù)轉換

要將 JSON 與關系數(shù)據(jù)一起使用或使用表，您需要使用 JSON 映射此數(shù)據(jù)并將其導入到數(shù)據(jù)庫表中。

 

如果您使用的是 SQL Server 2016 或更高版本，則可以使用OPENJSON函數(shù)。這是添加到數(shù)據(jù)庫引擎的新行集表值函數(shù)。它返回一個可用作視圖或表的對象。

 

它將 JSON 對象/屬性對轉換為行/列組合，接受兩個輸入參數(shù)：Expression（基于 UNICODE 的 JSON 文本）和Path（JSON 路徑表達式，可選參數(shù)，用于指定輸入表達式的片段）。

 

如果您的數(shù)據(jù)庫的兼容級別不是 130 或更高，則在嘗試使用 OPENJSON 函數(shù)時會出現(xiàn)以下異常：

 

Msg 208, Level 16, State 1, Line 78
Invalid object name ‘OPENJSON’.

 

如果您沒有為返回的結果指定架構，它將創(chuàng)建一個包含三列的表：

 

鍵（屬性名稱或元素索引，列類型為 NOT NULL VARCHAR(4000)）；

值（屬性值或元素索引，列類型為 NOT NULL NVARCHAR(MAX)）；

類型（值的 JSON 數(shù)據(jù)類型，列類型為 TINYINT）。

OPENJSON將返回一個表，其中第一級屬性將是行，每一行將是一個 JSON 屬性或數(shù)組元素。為了演示這一點，我們將使用示例 JSON 數(shù)據(jù)并將其作為字符串提供以查看輸出：

 

 

 

如果輸入的數(shù)據(jù)格式不正確，將顯示以下錯誤：

 

 

 

在這個例子中，我們只返回了一級屬性。如果我們希望返回 JSON 文檔（對象和數(shù)組）的復雜值，我們需要指定一個路徑參數(shù)。請參閱下一個返回PersonName元素的示例：

 

 

 

同樣，如果 JSON 格式不正確，SQL Server 引擎將拋出異常。讓我們在同一個 JSON 文本中犯一個故意的錯誤——我們將省略FirstName旁邊的一個引號：

 

 

 

一個常見的問題是以逗號分隔值格式加載數(shù)據(jù)，我們可以使用OPENJSON來幫助我們解決這個問題：

 

 

 

有一種更簡單的方法來處理 JSON 代碼——現(xiàn)代技術為我們帶來了許多工具。例如，dbForge SQL Complete允許您將此插件添加到 SSMS，然后直接在專用的數(shù)據(jù)查看器窗口中查看 JSON 代碼。

 

結論

因此，我們已經介紹了 JSON 的基礎知識及其在 SQL Server 中的用法，并通過示例演示了這些概念。還有更多關于 JSON 處理的高級主題，我們將在下一篇文章中深入探討它們。

 

Josip Saban

一位經驗豐富的數(shù)據(jù)庫開發(fā)人員和軟件經理，在該領域擁有超過 15 年的經驗，無論是在公司環(huán)境中還是在初創(chuàng)環(huán)境中。Josip 具有很強的解決問題的能力，并且在成功實施戰(zhàn)略解決方案以滿足不斷變化的業(yè)務需求方面有著良好的記錄，并且能夠與利益相關者建立關鍵關系并進行良好溝通，其中大部分在金融行業(yè)。專注于 Microsoft 工具堆棧，特別是 SQL Server，尤其是數(shù)據(jù)倉庫建模和開發(fā)。從 2000 版到最新版本。他對數(shù)據(jù)庫架構師和工程管理職位感興趣，對數(shù)據(jù)驅動的行業(yè)有很強的親和力。