JSON (JavaScript Object Notation) 是一種輕量級的數(shù)據(jù)交換格式，易于人閱讀和編寫，同時也易于機器解析和生成。自 SQL Server 2016 起，SQL Server 提供了對 JSON 數(shù)據(jù)的內置支持，允許用戶解析、查詢、存儲和輸出 JSON 數(shù)據(jù)。以下是如何在 SQL Server 中處理 JSON 數(shù)據(jù)的詳細指南。

實例數(shù)據(jù)表

假設我們有一個名為 Customers 的表，其中包含 JSON 格式的地址數(shù)據(jù)：

CREATE TABLE Customers(    CustomerID INT PRIMARY KEY,    Name NVARCHAR(100),    AddressJSON NVARCHAR(MAX));
INSERT INTO Customers (CustomerID, Name, AddressJSON)VALUES(1, 'John Doe', '{"Street":"123 Main St", "City":"New York", "ZipCode":"10001"}'),(2, 'Jane Smith', '{"Street":"456 Center Rd", "City":"Los Angeles", "ZipCode":"90001"}'),(3, 'Mike Johnson', '{"Street":"789 Side Ave", "City":"Chicago", "ZipCode":"60601"}');

查詢 JSON 數(shù)據(jù)

您可以使用 JSON_VALUE 函數(shù)來提取 JSON 字符串中的特定值：

SELECT    CustomerID,    Name,    JSON_VALUE(AddressJSON, '$.City') AS CityFROM Customers;

這個查詢將返回每個顧客的城市信息。

修改 JSON 數(shù)據(jù)

可以通過將整個 JSON 字符串替換為更新后的版本來修改 JSON 數(shù)據(jù)。目前 SQL Server 不支持直接修改 JSON 字符串中的單個屬性。

UPDATE CustomersSET AddressJSON = JSON_MODIFY(AddressJSON, '$.ZipCode', '10002')WHERE CustomerID = 1;

這個腳本將為 CustomerID 為 1 的顧客更新郵政編碼。

將 JSON 轉換為關系數(shù)據(jù)

使用 OPENJSON 函數(shù)將 JSON 數(shù)據(jù)轉換為行集合：

SELECT    CustomerID,    Name,    JSONData.*FROM    CustomersCROSS APPLY    OPENJSON(AddressJSON)    WITH (        Street NVARCHAR(50) '$.Street',        City NVARCHAR(50) '$.City',        ZipCode NVARCHAR(10) '$.ZipCode'    ) AS JSONData;

這個查詢將返回一個包含街道、城市和郵政編碼的扁平化數(shù)據(jù)表。

將關系數(shù)據(jù)轉換為 JSON

使用 FOR JSON 子句將關系數(shù)據(jù)轉換為 JSON 格式：

SELECT    CustomerID,    Name,    AddressJSONFROM CustomersFOR JSON PATH, ROOT('Customers');

這將生成一個包含所有顧客信息的 JSON 文檔。

使用 JSON 查詢

JSON_QUERY 函數(shù)用于提取 JSON 對象或數(shù)組，而非單個標量值：

SELECT    CustomerID,    Name,    JSON_QUERY(AddressJSON, '$') AS AddressFROM CustomersWHERE JSON_VALUE(AddressJSON, '$.City') = 'New York';

這將返回所有在紐約市的顧客及其地址。

驗證 JSON 數(shù)據(jù)

使用 ISJSON 函數(shù)驗證字符串是否包含有效的 JSON 數(shù)據(jù)：

SELECT    CustomerID,    Name,    AddressJSON,    ISJSON(AddressJSON) AS IsValidJSONFROM Customers;

這將返回每個顧客的地址數(shù)據(jù)及其是否為有效 JSON 的指示。

總結

SQL Server 中的 JSON 功能提供了與 JSON 數(shù)據(jù)進行互動的便捷方法。從簡單的提取值到復雜的 JSON 數(shù)據(jù)轉換，SQL Server 都能夠處理各種 JSON 相關的任務。通過上述示例，開發(fā)人員可以更好地理解如何在 SQL Server 中利用 JSON 功能，并使用相關的方法和函數(shù)來執(zhí)行各種操作。

請注意，與 XML 功能類似，JSON 數(shù)據(jù)處理在 SQL Server 中可能會有性能影響，特別是在處理大量數(shù)據(jù)時。因此，開發(fā)人員在設計和實現(xiàn)時應考慮性能最佳實踐，如避免復雜的 JSON 查詢以及在可能的情況下使用關系數(shù)據(jù)代替 JSON 數(shù)據(jù)。