JSON (JavaScript Object Notation) 是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。自 SQL Server 2016 起，SQL Server 提供了对 JSON 数据的内置支持，允许用户解析、查询、存储和输出 JSON 数据。以下是如何在 SQL Server 中处理 JSON 数据的详细指南。

实例数据表

假设我们有一个名为 Customers 的表，其中包含 JSON 格式的地址数据：

SQL
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 字符串中的特定值：

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

这个查询将返回每个顾客的城市信息。

修改 JSON 数据

可以通过将整个 JSON 字符串替换为更新后的版本来修改 JSON 数据。目前 SQL Server 不支持直接修改 JSON 字符串中的单个属性。

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

这个脚本将为 CustomerID 为 1 的顾客更新邮政编码。

将 JSON 转换为关系数据

使用 OPENJSON 函数将 JSON 数据转换为行集合：

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

这个查询将返回一个包含街道、城市和邮政编码的扁平化数据表。

将关系数据转换为 JSON

使用 FOR JSON 子句将关系数据转换为 JSON 格式：

SQL
SELECT
    CustomerID,
    Name,
    AddressJSON
FROM Customers
FOR JSON PATH, ROOT('Customers');

这将生成一个包含所有顾客信息的 JSON 文档。

使用 JSON 查询

JSON_QUERY 函数用于提取 JSON 对象或数组，而非单个标量值：

SQL
SELECT
    CustomerID,
    Name,
    JSON_QUERY(AddressJSON, '$') AS Address
FROM Customers
WHERE JSON_VALUE(AddressJSON, '$.City') = 'New York';

这将返回所有在纽约市的顾客及其地址。

验证 JSON 数据

使用 ISJSON 函数验证字符串是否包含有效的 JSON 数据：

SQL
SELECT
    CustomerID,
    Name,
    AddressJSON,
    ISJSON(AddressJSON) AS IsValidJSON
FROM Customers;

这将返回每个顾客的地址数据及其是否为有效 JSON 的指示。

总结

SQL Server 中的 JSON 功能提供了与 JSON 数据进行互动的便捷方法。从简单的提取值到复杂的 JSON 数据转换，SQL Server 都能够处理各种 JSON 相关的任务。通过上述示例，开发人员可以更好地理解如何在 SQL Server 中利用 JSON 功能，并使用相关的方法和函数来执行各种操作。

请注意，与 XML 功能类似，JSON 数据处理在 SQL Server 中可能会有性能影响，特别是在处理大量数据时。因此，开发人员在设计和实现时应考虑性能最佳实践，如避免复杂的 JSON 查询以及在可能的情况下使用关系数据代替 JSON 数据。

目录