JSON (JavaScript Object Notation) 是一種輕量級的數據交換格式，易于人閱讀和編寫，同時也易于機器解析和生成。自 SQL Server 2016 起，SQL Server 提供了對 JSON 數據的內置支持，允許用戶解析、查詢、存儲和輸出 JSON 數據。以下是如何在 SQL Server 中處理 JSON 數據的詳細指南。
實例數據表
假設我們有一個名為 Customers 的表，其中包含 JSON 格式的地址數據：
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 數據
您可以使用 JSON_VALUE 函數來提取 JSON 字符串中的特定值：
SELECT CustomerID, Name, JSON_VALUE(AddressJSON, '$.City') AS CityFROM Customers;
這個查詢將返回每個顧客的城市信息。
修改 JSON 數據
可以通過將整個 JSON 字符串替換為更新后的版本來修改 JSON 數據。目前 SQL Server 不支持直接修改 JSON 字符串中的單個屬性。
UPDATE CustomersSET AddressJSON = JSON_MODIFY(AddressJSON, '$.ZipCode', '10002')WHERE CustomerID = 1;
這個腳本將為 CustomerID 為 1 的顧客更新郵政編碼。
將 JSON 轉換為關系數據
使用 OPENJSON 函數將 JSON 數據轉換為行集合：
SELECT CustomerID, Name, JSONData.*FROM CustomersCROSS APPLY OPENJSON(AddressJSON) WITH ( Street NVARCHAR(50) '$.Street', City NVARCHAR(50) '$.City', ZipCode NVARCHAR(10) '$.ZipCode' ) AS JSONData;
這個查詢將返回一個包含街道、城市和郵政編碼的扁平化數據表。
將關系數據轉換為 JSON
使用 FOR JSON 子句將關系數據轉換為 JSON 格式：
SELECT CustomerID, Name, AddressJSONFROM CustomersFOR JSON PATH, ROOT('Customers');
這將生成一個包含所有顧客信息的 JSON 文檔。
使用 JSON 查詢
JSON_QUERY 函數用于提取 JSON 對象或數組，而非單個標量值：
SELECT CustomerID, Name, JSON_QUERY(AddressJSON, '$') AS AddressFROM CustomersWHERE JSON_VALUE(AddressJSON, '$.City') = 'New York';
這將返回所有在紐約市的顧客及其地址。
驗證 JSON 數據
使用 ISJSON 函數驗證字符串是否包含有效的 JSON 數據：
SELECT CustomerID, Name, AddressJSON, ISJSON(AddressJSON) AS IsValidJSONFROM Customers;
這將返回每個顧客的地址數據及其是否為有效 JSON 的指示。
總結
SQL Server 中的 JSON 功能提供了與 JSON 數據進行互動的便捷方法。從簡單的提取值到復雜的 JSON 數據轉換，SQL Server 都能夠處理各種 JSON 相關的任務。通過上述示例，開發人員可以更好地理解如何在 SQL Server 中利用 JSON 功能，并使用相關的方法和函數來執行各種操作。
請注意，與 XML 功能類似，JSON 數據處理在 SQL Server 中可能會有性能影響，特別是在處理大量數據時。因此，開發人員在設計和實現時應考慮性能最佳實踐，如避免復雜的 JSON 查詢以及在可能的情況下使用關系數據代替 JSON 數據。
該文章在 2024/2/7 23:10:16 編輯過
400 186 1886
