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_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 数据。