Share via

將 JSON 文件儲存在 SQL Server 或 SQL Database

  • 發行項

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL DatabaseAzure SQL 受控執行個體

SQL 資料庫引擎提供原生 JSON 函數，可讓您使用標準 SQL 語言剖析 JSON 文件。 您可以在 SQL Server 或 SQL Database 中儲存 JSON 文件及查詢 JSON 資料，如同在 NoSQL 資料庫中一樣。 本文描述儲存 JSON 文件的選項。

JSON 儲存體格式

第一個儲存體設計決策是如何將 JSON 文件儲存在資料表中。 有兩個可用的選項：

  • LOB 儲存體 - JSON 文件可以依現況儲存於資料行，其資料類型為 json 或 nvarchar。 這是快速載入資料和內嵌的最佳方式，因為載入速度與字串資料行的載入速度相符。 如果未針對 JSON 值編製索引，這種方法可能會對查詢/分析時間造成額外的效能影響，因為原始 JSON 文件必須在查詢執行時進行剖析。

  • 關聯式儲存體 - JSON 文件可以在使用 OPENJSON、JSON_VALUE 或 JSON_QUERY 函式插入資料表時進行剖析。 輸入 JSON 文件的片段，可以儲存在包含 JSON 子元素且資料類型為 json 或 nvarchar 的資料行。 這種方法會增加載入時間，因為載入期間要進行 JSON 剖析；不過，查詢效能與關聯式資料的傳統查詢效能相符。

  • 目前在 SQL Server 中，JSON 並非內建資料類型。

  • 目前，JSON 資料類型適用於 Azure SQL 資料庫。

傳統的資料表

將 JSON 文件儲存在 SQL Server 或 Azure SQL 資料庫的最簡單方式是建立雙資料行資料表，其中包含文件的識別碼和文件的內容。 例如：

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

或者，如果支援：

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

這個結構相等於您可以在傳統文件資料庫中找到的集合。 主索引鍵 _id 是一個自動遞增的值，它為每份文件提供唯一的識別碼，而且可供快速查閱。 此結構對於您要依識別碼擷取文件或依識別碼更新儲存文件的傳統 NoSQL 案例而言是不錯的選擇。

  • 使用可用於儲存 JSON 文件的原生 json 資料類型。
  • nvarchar(max) 資料類型可讓您儲存大小高達 2 GB 的 JSON 文件。 不過如果您確定 JSON 文件不大於 8 KB，則基於效能考量，我們建議您使用 nvarchar(4000)，不要使用 nvarchar(max)。

在上述範例中建立的範例資料表假設，有效的 JSON 文件會儲存在 log 資料行。 如果您想要確定有效的 JSON 會儲存在 log 資料行，您可以新增資料行的 CHECK 條件約束。 例如：

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

每次有人插入或更新資料表中的文件時，這個條件約束會驗證 JSON 文件已正確格式化。 如果沒有條件約束，資料表最適合插入，因為任何 JSON 文件會直接新增到資料行，而不進行任何處理。

當您將 JSON 文件儲存到資料表中時，您可以使用標準的 Transact-SQL 語言來查詢文件。 例如：

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

有一項強大的優點，就是您可以使用「任何」T-SQL 函式和查詢子句來查詢 JSON 文件。 SQL Server 和 SQL Database 不會導入查詢中您可用來分析 JSON 文件的任何條件約束。 您可以使用 JSON_VALUE 函式從 JSON 文件擷取值，並像任何其他值將其用於查詢。

可使用豐富 T-SQL 查詢語法這項功能是 SQL Server 和 SQL Database 與傳統 NoSQL 資料庫之間的主要差異；在 Transact-SQL 中，您可能有處理 JSON 資料所需的任何函式。

索引

如果您發現您的查詢經常以某個屬性來搜尋文件 (例如，JSON 文件中的 severity 屬性)，您可以在屬性上新增資料列存放區非叢集索引索引以加速查詢。

您可以建立計算資料行，從指定的路徑 (也就是在路徑 $.severity) 上的 JSON 資料行公開 JSON 值，並在這個計算資料行上建立標準索引。 例如：

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

此範例中使用的計算資料行，是不會將額外空間新增至資料表的非持續性或虛擬資料行。 它由索引 ix_severity 用來改善查詢的效能，如下列範例所示：

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

此索引的一項重要特性是感知定序。 如果原始的 nvarchar 資料行有 COLLATION 屬性 (例如，區分大小寫或日文)，則索引組織會根據語言規則或與 NVARCHAR 資料行建立關聯的區分大小寫規則。 如果您正在為需要使用自訂語言規則來處理 JSON 文件的全球市場開發應用程式，這個感知定序可能是一項重要功能。

大型資料表和資料行存放區格式

如果您預期在集合中有大量的 JSON 文件，我們建議在集合上新增叢集資料行存放區索引，如下列範例所示：

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] json,
    INDEX cci CLUSTERED COLUMNSTORE
);

叢集資料行存放區索引能提供高資料壓縮 (最多 25 倍)，大幅減少您的儲存空間需求、降低儲存體的成本，以及提升您的工作負載 I/O 效能。 此外，叢集資料行存放區索引針對您 JSON 文件上的資料表掃描和分析而最佳化，因此這種類型的索引可能是記錄分析的最佳選項。

上述範例中使用序列物件將值指派給 _id 資料行。 序列和身分識別都是識別碼資料行的有效選項。

經常變更的文件和經記憶體最佳化的資料表

如果您預期在集合中會有大量的更新、插入和刪除作業，您可以在記憶體最佳化資料表中儲存 JSON 文件。 記憶體最佳化的 JSON 集合一律會將資料保存在記憶體中，因此沒有儲存體 I/O 額外負荷。 此外，記憶體最佳化 JSON 集合完全無鎖定，也就是對文件採取的動作不會封鎖任何其他作業。

要將傳統集合轉換成記憶體最佳化的集合，您唯一要做的是在資料表定義之後指定 WITH (MEMORY_OPTIMIZED=ON) 選項，如下列範例所示。 然後，您便有記憶體最佳化版本的 JSON 集合。

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json
) WITH (MEMORY_OPTIMIZED=ON)

記憶體最佳化的資料表是最適合經常變更之文件的選項。 當您考慮記憶體最佳化資料表時，也請考慮效能。 可能的話，請針對記憶體最佳化集合中的 JSON 文件使用 nvarchar(4000)，不使用 nvarchar(max)，因為它可能會大幅提升效能。 記憶體最佳化的資料表不支援 json 資料類型。

如同傳統的資料表，您可以對使用計算資料行，公開在記憶體最佳化資料表中的欄位，新增索引。 例如：

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json,

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

為了充分發揮效能，將 JSON 值轉換成可用來保存屬性值的最小可能類型。 在上述範例中，使用了 tinyint。

您也可以將更新 JSON 文件的 SQL 查詢，放在預存程序中以善加利用原生編譯。 例如：

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

此原生編譯的程序會採用查詢，並建立執行查詢的 .DLL 程式碼。 原生編譯的程序是能更快速查詢和更新資料的方法。

結論

SQL Server 和 SQL Database 中的原生 JSON 函式，讓您能像在 NoSQL 資料庫中一樣地處理 JSON 文件。 每個資料庫不論是關聯式還是 NoSQL，都有一些 JSON 資料處理方面的優缺點。 將 JSON 文件儲存在 SQL Server 或 SQL Database 的主要優點是 SQL 語言的完整支援。 您可以使用豐富的 Transact-SQL 語言來處理資料，以及設定各種不同的儲存體選項 (從高壓縮和快速分析用的資料行存放區索引，到無鎖定處理用的經記憶體最佳化的資料表)。 同時，您會受益於成熟的安全性和國際化功能，並且可以輕鬆地重複用於 NoSQL 案例中。 本文中所描述的理由是考慮將 JSON 文件儲存在 SQL Server 或 SQL Database 中的絕佳原因。

深入了解 SQL Server 和 Azure SQL Database 中的 JSON

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介，請參閱下列影片：

相關內容