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

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

假設(shè)我們有一個(gè)名為 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ù)來(lái)提取 JSON 字符串中的特定值：

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

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

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

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

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

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

將 JSON 轉(zhuǎn)換為關(guān)系數(shù)據(jù)

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

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

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

將關(guān)系數(shù)據(jù)轉(zhuǎn)換為 JSON

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

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

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

使用 JSON 查詢

JSON_QUERY 函數(shù)用于提取 JSON 對(duì)象或數(shù)組，而非單個(gè)標(biāo)量值：

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

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

驗(yàn)證 JSON 數(shù)據(jù)

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

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

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

總結(jié)

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

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