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

 

 

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

 

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

 

JSON 基礎(chǔ)知識

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

 

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

 

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

 

 

 

JSON 基本結(jié)構(gòu)

 

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

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

 

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

 

FOR JSON AUTO - 輸出將根據(jù) SELECT 語句結(jié)構(gòu)進(jìn)行格式化

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

 

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

 

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

 

 

我們收到以下錯(cuò)誤消息：

 

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

 

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

 

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

 

 

 

原始結(jié)果集中的每一行都創(chuàng)建為平面屬性結(jié)構(gòu)。如果將其與標(biāo)準(zhǔn) XML 進(jìn)行比較，您會(huì)看到更少的文本。這是因?yàn)楸砻麤]有出現(xiàn)在 JSON 輸出中。

 

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

 

 

 

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

 

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

 

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

 

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

 

 

 

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

 

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

 

 

 

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

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

 

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

 

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

 

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

 

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

 

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

 

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

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

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

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

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

結(jié)論

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

 

Josip Saban

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